Microsoft Corporation
April 7, 1997
Click to copy the Addressexpt sample discussed in this article.
This paper describes the Address Export solution sample (included with this paper) that uses the mail merge functionality of Microsoft® Word to create a list of addresses from one of three address book sources. The address information can be stored in a Word table or users can export the information to a Microsoft Excel spreadsheet.
The three address book sources Word mail merge can retrieve addresses from are: Microsoft Outlook® Address Book, Microsoft Schedule+ Contacts, or a Personal Address Book. The Outlook Address Book is an address book automatically created from any contacts in the Outlook Contacts folder that include an entry in the e-mail field or a fax phone number field. A Personal Address Book is a custom address book created for use with Outlook, Microsoft Exchange server, Schedule+ 7.0, or a similar address list created with a MAPI-compatible messaging system. Personal Address Book files have a .pab extension and can be stored on a disk.
The Address Export solution consists of a Word template named AdExport.dot. The template includes all the necessary code modules and forms, as well as a menu customization. If the template is copied to the Program Files\Microsoft Office\Office\Startup folder, the solution is automatically loaded when Word starts. To run the solution, click Address Exporter (Tools menu).
Note The Address Export template can also be loaded and unloaded using the Templates and Add-ins dialog box (Tools menu). Because the menu customization is saved in the Address Export template, the Address Exporter menu item is automatically added to the Tools menu when the template is loaded and is removed when the template is unloaded.
The code behind the Address Export solution can be viewed by opening the AdExport.dot file in Word, then switching to the Visual Basic® Editor. The Address Export solution includes three forms, used to create three custom dialog boxes. You can view the individual forms in the Visual Basic Editor by expanding the Forms folder in the AdExport project. The following sections show the project's forms and describe the purpose of each form.
Figure 1. Address Type dialog box
The Address Type dialog box prompts users for an address book source. This dialog box is displayed when users run the AddressBookInfo macro by clicking Address Exporter (Tools menu).
Figure 2. Field Names dialog box
The Field Names dialog box is displayed after the address book source is selected. The Available Field Names box on the left includes the field names from the address book. Selecting a field name and then clicking the Copy button moves the field name to the Fields To Use box on the right.
Figure 3. Arrange Fields dialog box
The Arrange Fields dialog box is displayed after the address book field names are selected. This dialog box allows users to arrange the fields as they will appear from left to right in a Word table or Microsoft Excel spreadsheet.
This section describes the functions in the modules and forms that are part of the Address Export project.
The module named CodeModule includes three public functions and two private functions.
This public function displays the Address Type dialog box with the Outlook Address Book option selected.
This public function retrieves the field names from a mail merge data source and adds them to the lbxAvailableFields list in the Field Names dialog box. The function then displays the Field Names dialog box.
This public function inserts mail merge fields for the items in the strFields array (the field names selected in the Field Names dialog box). The MergeToDoc function is then called, and the strFields array is passed to that function.
This private function, which is called by the InsertFields function, merges the main document with the address book data source and sends the results to a new document. The field names stored in the strFields array are added to the document header of the merge results document. The main document is closed without saving changes.
This private function exports the address information arranged in a Word table to a Microsoft Excel worksheet. The function uses Automation to copy the address information from the Word table to a new Microsoft Excel worksheet. Search and replace instructions are used to remove soft returns prior to the copy and paste operation. After the information is pasted into Microsoft Excel, the soft returns are restored by another search and replace operation. This function is called by the MergeToDoc function when users export the information to Microsoft Excel.
This section describes the functions in the three form modules that are part of the Address Export project.
The AddressBookInfo function (stored in the CodeModule module) displays the Address Type dialog box. The AddressType form includes the following private functions.
This private function runs when users click the Cancel button in the Address Type dialog box. The function unloads the AddressType form.
This private function sets up a catalog mail merge with the specified address book as the data source. At the end of the function, the GetFieldNames function is called.
This form is displayed after users select an address book data source. The Field Names dialog box is used to request the fields for the resulting table of address information.
This private function runs when users click the Cancel button in the Field Names dialog box. The function unloads the FieldNames form and closes the main document without saving changes.
This private function retrieves the items from lbxFieldsToUse list and stores them in the strFields array. The FieldNames form is unloaded. The items in the strFields array are added to the lbxFieldNames list in the ArrangeFields form, and the Arrange Fields dialog box is displayed.
This private function runs when users click the Copy button in the Field Names dialog box. The function takes the item selected in lbxAvailableFields, adds it to the lbxFieldsToUse, and removes the item from the lbxAvailableFields.
This private function runs when users click the Remove button in the Field Names dialog box. The function takes the item selected in the lbxFieldsToUse list, adds it to the lbxAvailableFields list, and removes the item from the lbxFieldsToUse list.
This form is displayed after users choose fields in the Field Names dialog box.
This private function retrieves the items from the lbxFieldNames list and stores them in the strFields array. The ArrangeFields form is unloaded, and the InsertFields function is called.
This private function is called when users click the Move Up button in the Arrange Fields dialog box. The function moves the selected item in the lbxFieldNames list up one position by removing the item and then adding it back again up one position.
This private function is called when users click the Move Down button in the Arrange Fields dialog box. The function moves the selected item in the lbxFieldNames list down one position by removing the item and then adding it back again down one position.
This solution demonstrates how you can use Visual Basic for Applications to access information stored in an address book. In addition to using the mail merge functionality in Word, the solution uses Automation to export the address information from the Word table to a Microsoft Excel spreadsheet.
The solution is distributed as a template that can be loaded manually using the Templates and Add-ins dialog box (Tools menu), or, if the template is in the Program Files\Microsoft Office\Office\Startup folder, loaded automatically when Word starts. To run the solution, click Address Exporter (Tools menu).