Export contacts from Outlook to Excel – automate in vba
————————————————————————————————————————–
Contents:
Automating Microsoft Outlook from Excel
Export contacts from a specific Contact Items Folder to an Excel Worksheet, using Early Binding
Export contacts from the default Contact Items Folder to an Excel Worksheet, using Late Binding
————————————————————————————————————————–
Data from Outlook Contact Items can be posted to an Excel Worksheet, by automating Microsoft Outlook from Excel, using vba. In this section it is explained how to export and post data from Outlook Contacts to an Excel Worksheet, using Automation in vba. Examples have been given to automate using both Early Binding and Late Binding.
When you use vba in an Office Application, say Outlook, a reference to the Outlook Object Library is set by default. When you Automate to work with Outlook objects from another application, say Excel, you can add a reference to the Outlook object library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Outlook’s predefined constants. This is a must when you automate using Early Binding (explained earlier). Once this reference is added, a new instance of Outlook application can be created by using the New keyword.
Automate Outlook from Excel, using Late Binding: You need not add a reference to the Outlook object library in Excel (your host application), in this case you will not be able to use the Outlook’s predefined constants and will need to replace them by their numerical values in your code. In Late Binding, the object variable is declared as an Object Type which can be a reference to any object, and this makes an object late bound. In Late Binding, the object library is not exposed during design-time, but the binding happens during run-time using the CreateObject or the GetObject functions. CreateObject creates a new instance of Outlook and GetObject returns an already running instance of the Outlook object.
Click here for a detailed explanation of Automation using Early Binding and Late Binding.
Example 1: This example exports contacts from a specific Contact Items Folder to an Excel Worksheet, using Early Binding.
Sub OutlookContactsToExcelWorksheet1()
‘Automating Outlook from Excel: Post data to Excel Worksheet from Outlook Contacts.
‘This example exports contacts (only those contacts which have a Last Name) from a specific Contact Items Folder.
‘Automate using Early Binding: Add a reference to the Outlook Object Library in Excel (your host application) by clicking Tools-References in VBE, which will enable using Outlook’s predefined constants. Once this reference is added, a new instance of Outlook application can be created by using the New keyword.
‘In this example, 5 types of information from Outlook Contact Items will be posted to the following columns of the specified Worksheet:
‘Column A: First Name
‘Column B: Last Name
‘Column C: Email Address
‘Column D: Company Name
‘Column E: Mobile Telephone Number
Dim applOutlook As Outlook.Application
Dim nsOutlook As Outlook.Namespace
Dim cFolder As Outlook.Folder
Dim subFolder1 As Outlook.Folder
Dim delFolder As Outlook.Folder
Dim delItems As Outlook.Items
Dim cItems As Outlook.Items
Dim cItem As Outlook.ContactItem
Dim i As Long, n As Long, c As Long, lastRow As Long
Dim wb As Workbook, ws As Worksheet
‘Create a new instance of the Outlook application. Set the Application object as follows:
Set applOutlook = New Outlook.Application
‘use the GetNameSpace method to instantiate (ie. create an instance) a NameSpace object variable, to access existing Outlook items. Set the NameSpace object as follows:
Set nsOutlook = applOutlook.GetNamespace(“MAPI”)
‘—————————-
‘Empty the Deleted Items folder in Outlook so that when you quit the Outlook application you bypass the prompt: Are you sure you want to permanently delete all the items and subfolders in the “Deleted Items” folder?
‘set the default Deleted Items folder:
Set delFolder = nsOutlook.GetDefaultFolder(olFolderDeletedItems)
‘set the items collection:
Set delItems = delFolder.Items
‘determine number of items in the collection:
c = delItems.Count
‘start deleting from the last item:
For n = c To 1 Step -1
delItems(n).Delete
Next n
‘—————————-
‘set the workbook:
Set wb = ThisWorkbook
‘set the worksheet where you want to post Outlook data:
Set ws = wb.Sheets(“Sheet1”)
‘set and format headings in the worksheet:
ws.Cells(1, 1).Value = “First Name”
ws.Cells(1, 2).Value = “Last Name”
ws.Cells(1, 3).Value = “Email Address”
ws.Cells(1, 4).Value = “Company Name”
ws.Cells(1, 5).Value = “Mobile Telephone Number”
With ws.Range(“A1:E1”)
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
‘export contacts from a specific Contact Items Folder.
‘set reference to the folder named “cont” under the parent folder “Personal Folders”:
Set cFolder = nsOutlook.Folders(“Personal Folders”)
Set subFolder1 = cFolder.Folders(“cont”)
‘set the items collection:
Set cItems = subFolder1.Items
‘post each outlook item in the items collection, to a new worksheet row:
i = 1
For Each cItem In cItems
‘start posting in worksheet from the second row:
i = i + 1
‘post only those contacts which have a Last Name:
If cItem.LastName <> “” Then
ws.Cells(i, 1).Value = cItem.firstName
ws.Cells(i, 2).Value = cItem.LastName
ws.Cells(i, 3).Value = cItem.Email1Address
ws.Cells(i, 4).Value = cItem.CompanyName
ws.Cells(i, 5).Value = cItem.MobileTelephoneNumber
End If
Next
‘determine last data row, basis column B (contains Last Name):
lastRow = ws.Cells(Rows.Count, “B”).End(xlUp).Row
‘format worksheet data area:
ws.Range(“A2:E” & lastRow).Sort Key1:=ws.Range(“B2”), Order1:=xlAscending
ws.Range(“A2:E” & lastRow).HorizontalAlignment = xlLeft
ws.Columns(“A:E”).EntireColumn.AutoFit
‘quit the Oulook application:
applOutlook.Quit
‘clear the variables:
Set applOutlook = Nothing
Set nsOutlook = Nothing
Set cFolder = Nothing
Set subFolder1 = Nothing
Set delFolder = Nothing
Set delItems = Nothing
Set cItems = Nothing
Set cItem = Nothing
End Sub
Example 2: This example exports contacts from the default Contact Items Folder to an Excel Worksheet, using Late Binding.
For live code of this example, click to download excel file.
Sub OutlookContactsToExcelWorksheet2()
‘Automating Outlook from Excel: Post data to Excel Worksheet from Outlook Contacts.
‘This example exports contacts (whose Last Name starts with “S”) from the default Contact Items Folder.
‘Automate Outlook from Excel, using Late Binding. You need not add a reference to the Outlook library in Excel (your host application), in this case you will not be able to use the Outlook’s predefined constants and will need to replace them by their numerical values in your code.
‘In this example, 5 types of information from Outlook Contact Items will be posted to the following columns of the specified Worksheet:
‘Column A: First Name
‘Column B: Last Name
‘Column C: Email Address
‘Column D: Company Name
‘Column E: Mobile Telephone Number
Dim oApplOutlook As Object
Dim oNsOutlook As Object
Dim oCFolder As Object
Dim oSubFolder1 As Object
Dim oDelFolder As Object
Dim oDelItems As Object
Dim oCItems As Object
Dim oCItem As Object
Dim i As Long, n As Long, c As Long, lLastRow As Long
Dim wb As Workbook, ws As Worksheet
‘Create a new instance of the Outlook application, if an existing Outlook object is not available.
‘Set the Application object as follows:
On Error Resume Next
Set oApplOutlook = GetObject(, “Outlook.Application”)
‘if an instance of an existing Outlook object is not available, an error will occur (Err.Number = 0 means no error):
If Err.Number <> 0 Then
Set oApplOutlook = CreateObject(“Outlook.Application”)
End If
‘disable error handling:
On Error GoTo 0
‘use the GetNameSpace method to instantiate (ie. create an instance) a NameSpace object variable, to access existing Outlook items. Set the NameSpace object as follows:
Set oNsOutlook = oApplOutlook.GetNamespace(“MAPI”)
‘—————————-
‘Empty the Deleted Items folder in Outlook so that when you quit the Outlook application you bypass the prompt: Are you sure you want to permanently delete all the items and subfolders in the “Deleted Items” folder?
‘set the default Deleted Items folder:
‘The numerical value of olFolderDeletedItems is 3. The following code has replaced the Outlook’s built-in constant olFolderDeletedItems by its numerical value 3.
Set oDelFolder = oNsOutlook.GetDefaultFolder(3)
‘set the items collection:
Set oDelItems = oDelFolder.Items
‘determine number of items in the collection:
c = oDelItems.Count
‘start deleting from the last item:
For n = c To 1 Step -1
oDelItems(n).Delete
Next n
‘—————————-
‘set the workbook:
Set wb = ThisWorkbook
‘set the worksheet where you want to post Outlook data:
Set ws = wb.Sheets(“Sheet1”)
‘set and format headings in the worksheet:
ws.Cells(1, 1) = “First Name”
ws.Cells(1, 2) = “Last Name”
ws.Cells(1, 3) = “Email Address”
ws.Cells(1, 4) = “Company Name”
ws.Cells(1, 5) = “Mobile Telephone Number”
With ws.Range(“A1:E1”)
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
‘export contacts from the default Contact Items Folder.
‘set reference to the default Contact Items folder:
‘The numerical value of olFolderContacts is 10. The following code has replaced the Outlook’s built-in constant olFolderContacts by its numerical value 10.
Set oCFolder = oNsOutlook.GetDefaultFolder(10)
‘set the items collection:
Set oCItems = oCFolder.Items
‘post each outlook item in the items collection, to a new worksheet row:
i = 1
For Each oCItem In oCItems
‘start posting in worksheet from the second row:
i = i + 1
‘post only those contacts whose Last Name starts with “S”:
If Left(oCItem.LastName, 1) = “S” Then
ws.Cells(i, 1) = oCItem.firstName
ws.Cells(i, 2) = oCItem.LastName
ws.Cells(i, 3) = oCItem.Email1Address
ws.Cells(i, 4) = oCItem.CompanyName
ws.Cells(i, 5) = oCItem.MobileTelephoneNumber
End If
Next
‘determine last data row, basis column B (contains Last Name):
lLastRow = ws.Cells(Rows.Count, “B”).End(xlUp).Row
‘format worksheet data area:
ws.Range(“A2:E” & lLastRow).Sort Key1:=ws.Range(“B2”), Order1:=xlAscending
ws.Range(“A2:E” & lLastRow).HorizontalAlignment = xlLeft
ws.Columns(“A:E”).EntireColumn.AutoFit
‘quit the Oulook application:
oApplOutlook.Quit
‘clear the variables:
Set oApplOutlook = Nothing
Set oNsOutlook = Nothing
Set oCFolder = Nothing
Set oSubFolder1 = Nothing
Set oDelFolder = Nothing
Set oDelItems = Nothing
Set oCItems = Nothing
Set oCItem = Nothing
End Sub