Using Automation to Access Microsoft Outlook

To access Outlook's object model directly from Excel, begin by establishing a reference to the Microsoft Outlook 10.0 or 9.0 Object Library. The example procedure that follows will insert your Outlook contact information into an Excel spreadsheet.

Sub GetContacts()

Dim objOut As Outlook.Application

Dim objNspc As NameSpace

Dim objItem As ContactItem

Dim Headings As Variant

Dim i As Integer ' array element

Set objOut = New Outlook.Application Set objNspc = objOut.GetNamespace("MAPI")

Headings = Array("Full Name", "Street", "City", _

"State", "Zip Code", "E-Mail")


For Each cell In Range("A1:F1") cell.FormulaR1C1 = Headings(i) i = i + 1


For Each objItem In objNspc.GetDefaultFolder _ (olFolderContacts).Items With ActiveSheet

.Cells(r, 1).Value = objItem.FullName .Cells(r, 2).Value = objItem.BusinessAddress

.Cells(r, 3) .Cells(r, 4) .Cells(r, 5) .Cells(r, 6) End With r = r + 1 Next objItem Set objItem = Nothing Set objNspc = Nothing Set objOut = Nothing End Sub

The GetContacts procedure starts by declaring an object variable called objOut to hold a reference to the Outlook application. This variable is defined by a specific object type (Outlook.Application); therefore, VBA will use early binding.

Notice that in this procedure, we use the New keyword (discussed in the previous section) to create a new instance of an Outlook Application object, return a reference to that instance, and assign the reference to the objOut variable being declared.

In order to access contact items in Outlook, you also need to declare object variables to reference the Outlook Namespace and Item objects. The Namespace object represents the message store known as MAPI (Messaging Application Programming Interface). The Namespace object contains folders (Contacts, Journal, Tasks, etc.), which in turn contain items. An item is a particular instance of Outlook data, such as an e-mail message or a contact.

After writing column headings to the worksheet using the For...Each...Next loop, the procedure uses another For...Each...Next loop to iterate through the Items collection in the Contacts folder. The GetDefaultFolder method returns an object variable for the Contact folder. This method takes one argument, the constant representing the folder you want to access. After all the contact items are written to an Excel spreadsheet, the procedure releases all object variables by setting them to Nothing.

Note: When you run the GetContacts procedure, you may get a warning message that the program is trying to access e-mail addresses. Click OK in the message box to allow the operation.

0 0

Post a comment