Exporting Journal Information to Outlook

If you link to or import mainframe transaction or batch processing data into an Access database table, it may be convenient to export that data to Outlook journal items, for quick reference in the Outlook interface. The table tblMainframeData in the sample database is an example of such data. Figure 4.5 shows a portion of this table, with the fields to be exported to Outlook.

FIGURE 4.5

A table of mainframe data to export to Outlook journal items.

Account -

Debit '

Credit - Transaction

JournalType »

TransactionDate - Dept

1.1

S120.00

$0.00 Payments

Note

7/29/2006 Accounting

2.1

$0.00

($88.93) Payments

Note

7/29/2005 Accounting

6.3

$0.00

¡$31.07) Payments

Note

7/29/2006 Accounting

4.45

2.1

$0.00 $341.83

$0.00 Address & phone number verified $0.00 Payments

Phone Call Note

7/7/2006 Human Resources 7/8/2006 Accounting

6.3

$74.17

$0.00 Payments

Note

7/8/2006 Accounting

7.1

$0.00

($416.00) Payments

Note

7/8/2006 Accounting

1.2

$0.00

$0.00 Collection fees due_

Note

7/8/2006 Accounting

2.7

$0.00

$0.00 Revision of bill

Note

7/8/2006 Accounting

2.1

$0.00

($341.35) Reprocessing Fee

Note

7/8/2006 Accounting

6.3

$0.00

($74.15) Reprocessing Fee

Note

7/8/2006 Accounting

7.1

$416.00

$0.00 Reprocessing Fee

Note

7/8/2006 Accounting

4.2

$0.00

$0.00 Collection fees due

Note

7/8/2006 Accounting

6.3

$0.00

$0.00 Deferment

Note

7/8/2006 Accounting

2.1

$0.00

($74.15) Reprocessing Fee

Note

7/8/2006 Accounting

6.3

$74.15

$0.00 Reprocessing Fee

Note

7/8/2006 Accounting

4.3

$0.00

$0.00 Repay plan

Note

7/8/2006 Accounting

The function that exports the mainframe data to Outlook journal items is listed as follows (for convenience, this function is run from the macro mcrExportTransactions):

Public Function ExportTransactions()

On Error GoTo ErrorHandler

Dim appOutlook As Outlook.Application

Dim jnl As Outlook.Journalltem

Dim dbs As DAO.Database

Dim rst As DAO.Recordset

Dim strBody As String

Dim strPrompt As String

Dim strTitle As String

Set appOutlook = GetObject(, "Outlook.Application") Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblMainframeData") Do While Not rst.EOF

Set jnl = appOutlook.Createltem(olJournalltem)

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 = 42 9 Then

Set appOutlook = CreateObject("Outlook.Application") Resume Next Else

& "; 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, creating a new journal item in the default Journal folder for each record in the table, and setting its properties from data in the tables 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 concatenated into a String variable, which is written to the Body field (the large textbox at the bottom of the Journal item).

NOTE

A journal item created from a record in a table of mainframe transaction data.

0 0

Post a comment