Access.2007.VBA.Bibl..

jnl.Subject = rst![Transaction]

jnl.Type = rst![JournalType]

jnl.Companies = rst![Dept]

jnl.Start = rst![TransactionDate]

Create a text string with data from various table fields, for writing to the journal item’s Body field:

strBody = IIf(rst![Debit] > 0, “Debit of “ _

& Format(rst![Debit], “$###,##0.00”) _

& “ for “, “”) & IIf(rst![Credit] > 0, _

“Credit of “ & Format(rst![Debit], _

“$###,##0.00”) & “ for “, “”) _

& “Account No. “ & rst![Account]

Debug.Print “Body string: “ & strBody

jnl.Body = strBody

jnl.Close (olSave)

rst.MoveNext

Loop

strTitle = “Done”

strPrompt = “All transactions exported to Outlook “ _

& “journal items”

MsgBox strPrompt, vbOKOnly + vbInformation, strTitle

ErrorHandler:

‘Outlook is not running; open Outlook with CreateObject

If Err.Number = 429 Then

Set appOutlook = CreateObject(“Outlook.Application”)

Resume Next

Else

MsgBox “Error No: “ & Err.Number _

& “; Description: “ & Err.Description

Resume ErrorHandlerExit

End If

End Function

When Outlook 2007 is first installed, the Journal component is turned off; activate it in

order to see the journal entries created by the preceding procedure.

This function first sets up a DAO recordset based on tblMainframeData and loops through it, creat-

ing a new journal item in the default Journal folder for each record in the table, and setting its

properties from data in the table’s fields. There is a success message when all the data has been

exported. Figure 4.6 shows a journal item created from a transaction record.

To avoid having to create a custom Journal form, the code writes the Dept data to the Companies

(Company in the interface) field of a standard Journal item. Data from several fields is concate-

nated into a String variable, which is written to the Body field (the large textbox at the bottom of

the Journal item).

NOTENOTE

Part I The Office Components and What They Do Best