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.
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.
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
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
Set MyFldr = olNSpc.GetDefaultFolder(olFolderTasks)
In turn, each Outlook folder contains a collection of
, which are the detail records for each section. For example, the Mike D. Jones contact is one
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
Finally, Outlook regards each item's fields as an item property. So, something like
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
|ScrollBars||0 - fmScrollBarsNone|
|SpecialEffect||6 - fmSpecialEffectBump|
|ColumnWidths||72 pt;1 pt;1 pt;1 pt;1 pt;1 pt|
|ListStyle||1 - fmListStyleOption|
|MultiSelect||1 - fmMultiSelectMulti|
|SpecialEffect||2 - fmSpecialEffectSunken|
|Caption||Select A Contact|
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
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
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.