This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

July 2000

Insert Outlook Contact Information into Word Documents

by Mike D. Jones

Word's Mail Merge option provides a powerful tool for importing addresses into form letters and other documents on a large scale. With this option, you can use the contact names and addresses you've established in Outlook 2000. However, sometimes the Mail Merge feature can be too much of a good thing. For example, if you have one letter to a single contact and need to add an address or other personal information to the letter, it's overkill to create an entire Mail Merge document for just one person.

The alternative, of course, is to launch Outlook and select the Letter action for that contact. When you do this, however, Outlook opens another instance of Word and creates a new letter from scratch. If you've already started your own document, this new letter does you little good. As a result, you may be asked to build a mini-address book listing all the contacts in an Outlook address book and have that data available for insertion anywhere in a Word document. Fortunately, using OLE Automation, you can provide just that.

In this article, we'll show you how to download Outlook contact information into a VBA UserForm, like the one in Figure A. With this form, you can insert contact details anywhere in a Word document without launching either Outlook or Mail Merge.

Figure A: This UserForm displays contacts from Outlook's contact folder.

The technique

For our technique, we'll access Outlook's object model directly from Word. We'll create a simple user form with one combobox and one listbox. The combobox will hold a list of contacts and their relevant details. After you select a contact, the UserForm will fill the listbox with the appropriate values. You'll then be able to select the information you want placed in the Word document. Before we begin, however, let's look at some of the more relevant items in Outlook's object model.

Outlook's object model

Figure B shows an abbreviated version of Outlook's object model. What may not be apparent in the model is that the MAPI folder consists of several subfolders that represent each Outlook section-Mail, Contacts, Journal, Tasks, etc. To return an object variable for one of these folders, use the

GetDefaultFolder()

method. This method takes one argument, foldervalue, inside the parenthesis. The values you use for this argument are listed in Table A. Outside Outlook, you can use the VBA constants. In VB Script, however, you must use the numeric values themselves.

Figure B: All Outlook sections-Mail, Contacts, Journal, Tasks, etc.-are really just separate subfolders contained in the MAPI folder.

Table A: Outlook's folder values

So, to return a variable for the Tasks folder, you'd use the following; assuming, of course, that you've already declared an Outlook Namespace variable called

olNSpc

:

Set MyFldr = olNSpc.GetDefaultFolder(olFolderTasks)

Folder Items

In turn, each Outlook folder contains a collection of

Items

, which are the detail records for each section. For example, the Mike D. Jones contact is one

Item

in the Contact folder. Like all collections, you refer to individual items by index number or by name. For example, the following would refer to the first item in the

MyFolder

folder:

MyFolder.Items(1)

Finally, Outlook regards each item's fields as an item property. So, something like

MyFolder.Items(1).FullName

would represent the value in the item's Full Name field.

Build the mini address book

As we mentioned, our UserForm has one combobox and one listbox. In addition, we'll add two command buttons, one to insert the selected information from the listbox, and the other to insert all details for the selected contact. We'll attach an event procedure to the form's Initialize event that will create an Automation reference to the Outlook application, and then access the Contact folder and grab the information we want to place in a combobox.

Once you select a contact from the control, the relevant information will be displayed in a listbox. From there, you can select the exact information you want to enter into your document. Let's start by creating the UserForm.

To begin in Word, press [Alt][F11] to open the Visual Basic Explorer. Select Insert | UserForm to create a blank form. Using Figure A as a guide, set the form and control properties as shown in Table B.

Table B: Form properties

Add the code

Now that we've given the form some physical structure, we can move on to the Automation section. First, we'll add the code that will extract the contact information from Outlook and place it in the combobox. Since we're going to use Outlook's object model, we need to add a reference to Outlook in our VBA project. To do so, select the Tools option from the menu bar, and then select References. In the References dialog box, scroll down and select the Microsoft Outlook 9.0 Object Library, and then click OK.

Now, right-click on the form and select View Code from the shortcut menu. When the VBE displays the module window, select UserForm from the Object dropdown list in the upper-left corner. Then, select the Initialize event from the Procedure dropdown list in the upper-right corner. The Visual Basic Explorer provides the beginning and ending Sub statements. At the insertion point, enter the remaining code from Listing A.

Listing A: The UserForm's Initialize event

Private Sub UserForm_Initialize() 
      Dim oApp As Outlook.Application
      Dim oNspc As NameSpace
      Dim oItm As ContactItem
      Dim x As Integer
      If Not DisplayStatusBar Then
      DisplayStatusBar = True
      End If
      StatusBar = "Please Wait..."
      x = 0
      Set oApp = CreateObject("Outlook.Application")
      Set oNspc = oApp.GetNamespace("MAPI")
      For Each oItm In oNspc.GetDefaultFolder _
      (olFolderContacts).Items
      With Me.cboContactList
      .AddItem (oItm.FullName)
      .Column(1, x) = oItm.BusinessAddress
      .Column(2, x) = oItm.BusinessAddressCity
      .Column(3, x) = oItm.BusinessAddressState
      .Column(4, x) = oItm.BusinessAddressPostalCode
      .Column(5, x) = oItm.Email1Address
      End With
      x = x + 1
      Next oItm
      StatusBar = ""
      Set oItm = Nothing
      Set oNspc = Nothing
      Set oApp = Nothing
      End Sub

When the form opens, this event code fills the combobox with items from the contact folder. To display the selected information from the combobox, select cboContactList from the Object dropdown list, select Change from the Procedure dropdown list, and finally enter the code from Listing B.

Listing B: The combobox Change event

Private Sub cboContactList_Change() 
      Dim x As Integer
      With lstFieldList
      If .ListCount > 0 Then
      For x = 0 To .ListCount - 1
      .RemoveItem (0)
      Next x
      End If
      For x = 0 To cboContactList.ColumnCount - 1
      .AddItem (Me.cboContactList.Column(x))
      Next x
      End With
      End Sub

Listing C shows the code for all the buttons on the form: cmbAll, cmbDetail, and cmbClose. Select them from the Module Window's Object dropdown list and add the code to their Click event.

Listing C: Command button Click events

Private Sub cmbAll_Click() 
      Call InsertIntoDoc(True)
      End Sub
      Private Sub cmbDetail_Click()
      Call InsertIntoDoc(False)
      End Sub
      Private Sub cmbClose_Click()
      Unload Me
      End Sub

Notice that the two buttons cmbAll and cmbDetail call the same

InsertIntoDoc

sub procedure but pass a separate argument. This procedure inserts the selected details into the Word document. To create the procedure, add the code from Listing D into the module's General Declarations section. The form is now complete, so save it and return to Word.

Listing D: The InsertIntoDoc subroutine Listing D: The InsertIntoDoc subroutine

Public Sub InsertIntoDoc(All As Boolean) 
      Dim itm As Variant
      With lstFieldList
      For x = 0 To .ListCount - 1
      If All Then .Selected(x) = All
      If .Selected(x) = True Then
      With Selection
      .InsertAfter (lstFieldList.List(x))
      .Collapse (wdCollapseEnd)
      .Paragraphs.Add
      End With
      End If
      Next x
      End With
      End Sub

Test the form

Our last step is to create a one-line macro to display the form. To do so, press [Alt][F8] to call up the Macros dialog box. Type ShowOutlookFrm as the macro name, and then click Create. At the insertion point, enter

frmOutlookAddress.Show

Click the Save button and close the Visual Basic Editor. Display the Macros dialog box and double-click on ShowOutlookFrm. After a brief delay while the procedure gathers the information from Outlook, the form you see in Figure A loads the contact information, which is then ready to be inserted as necessary into your Word document.

Copyright © 2000 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.