September 1, 2021

Get Data from Outlook Address Book Automatically

How to get data from Outlook Address book including business address automatically using VBA. Watch the training video below:

Retrieve business addresses from Outlook automatically

Before we write the VBA code we need to understand that we are going to interact with the email application Outlook via Excel. Therefore we click on Tools in the Visual Basic for Applications window and select References.

VBA Window –> Tools –> References

Next we check the check-box next to the Microsoft Outlook Object Library in the References window and click OK.

Now we write the code. If we have the names of the contacts in our worksheet and wish to extract a property like the business address of the contact then we use the following macro code:

Option Explicit

Sub GetBusinessAddressFromOutlookContacts()
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Sheet1.Range(“A:A”))
‘MsgBox lastrow
Dim i As Long
Dim oApp As Object
‘Namespace represents an abstract root object for any data source
‘The only data source supported is MAPI,
‘which allows access to all Outlook data stored in the user’s mail stores.
Dim oNspc As Object
Dim oItm As Object
Dim oMapiFldr As Outlook.MAPIFolder

‘Outlook not already loaded so load it
Set oApp = CreateObject(“Outlook.Application”)
‘Messaging Application Programming Interface (MAPI)
‘is a client protocol that lets users access their mailbox by using Outlook
Set oNspc = oApp.GetNamespace(“MAPI”)
Set oMapiFldr = oNspc.GetDefaultFolder(olFolderContacts)
For Each oItm In oMapiFldr.Items
For i = 1 To lastrow
If oItm.FullName = Cells(i, 1) Then
Cells(i, 2) = oItm.BusinessAddress

Cells(i,3) = oItm.Email1Address
End If

Next i

Next oItm

End Sub

Some of the concepts like namespace and MAPI are explained in the code using comments.

The aim is to access the contacts folder in Outlook that has the different types of data like Job Title, Full Name, First Name, Last Name, Company Name, Department, Business Address, Phone, etc. We can loop through each of these properties of the contacts and get the data from Outlook into our Excel worksheet for further processing. We can create a database in our Excel worksheet for later use.

So if we wish to extract many or all of the properties of our contacts we can use the following VBA code:

Sub GetBusinessAddressFromOutlookContacts2()
‘https://www.exceltrainingvideos.com by Dr Dinesh K Takyar
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Sheet1.Range(“A:A”))
‘MsgBox lastrow
Dim i As Long
Dim oApp As Object
‘Namespace represents an abstract root object for any data source
‘The only data source supported is MAPI,
‘which allows access to all Outlook data stored in the user’s mail stores.
Dim oNspc As Object
Dim oItm As Object
Dim oMapiFldr As Outlook.MAPIFolder

‘Outlook not already loaded so load it
Set oApp = CreateObject(“Outlook.Application”)
‘Messaging Application Programming Interface (MAPI)
‘is a client protocol that lets users access their mailbox by using Outlook
Set oNspc = oApp.GetNamespace(“MAPI”)
Set oMapiFldr = oNspc.GetDefaultFolder(olFolderContacts)
i = 1
For Each oItm In oMapiFldr.Items
‘For i = 1 To lastrow
Cells(i, 4) = oItm.FullName
Cells(i, 5) = oItm.BusinessAddress
Cells(i, 6) = oItm.Email1Address
‘Next i
i = i + 1
Next oItm

End Sub

Further Reading:

NameSpace

You can download a sample file for practice:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.