Reports are useful for exporting multiple records from the accounting system to other VBA-compliant applications, such as Microsoft Excel, Outlook or Word. A report’s output can include several records, such as customer, item or transaction records. You can use VBA to analyze each record in the report as the report prints, then export specific field values to another application.
Before you can work with another application’s objects, you must set a reference to that application’s object library using the References dialog box in the Visual Basic Editor. |
The following example uses the RM Customer Report to create customer contacts in Microsoft Outlook. The example first declares two module-level variables used to create Outlook contact objects. The report’s Start event then creates and returns a reference to Outlook using VBA’s CreateObject() function.
The RM Customer Report’s additional header contains the majority of customer information, so the procedure uses the BeforeAH event to return customer information. As Report Writer prints each additional header (one per customer record), the procedure checks the Sales Territory field. For each customer in “TERRITORY1,” the procedure creates a contact object in Microsoft Outlook, and sets properties in the contact object to fields in the report:
'Declare module-level variables in the general section Dim DynItem, DynOlApp As Object --------------------------------------------------------------------- Report_Start() 'Create the Outlook object Set DynOlApp = CreateObject("Outlook.Application") End Sub --------------------------------------------------------------------- Private Sub Report_BeforeAH(ByVal Level As Integer, _ SuppressBand As Boolean) If SalesTerritory = "TERRITORY 1" Then 'Add customer information to Outlook Set DynItem = DynOlApp.CreateItem(olContactItem) 'Set contact object properties to window field values DynItem.FullName = ContactPerson DynItem.CompanyName = CustomerName DynItem.BusinessAddressStreet = Address1 DynItem.BusinessAddressCity = City DynItem.BusinessAddressState = State DynItem.BusinessAddressPostalCode = Zip 'Format report fields DynItem.BusinessTelephoneNumber = Format(Phone1, _ "(@@@) @@@-@@@@ Ext. @@@@") DynItem.BusinessFaxNumber = Format(Fax, _ "(@@@) @@@-@@@@ Ext. @@@@") 'Save the contact object DynItem.Save End If End Sub