Exporting data to other applications

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.

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

Documentation Feedback

---