Import Contacts from Excel to Outlook – automate in vba
—————————————————————————————————————–
Contents:
Automating Microsoft Outlook from Excel
—————————————————————————————————————–
You can import contacts into your Outlook contacts folder, from contact information available in an Excel worksheet, by automating in vba. In this section it is explained how to export and post data from an Excel Worksheet to Outlook Contacts, 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 data from an Excel Worksheet to the default Contacts folder (new contact items added with Application.CreateItem Method), using Early Binding.
Sub ExcelWorksheetDataAddToOutlookContacts1()
‘Automating Outlook from Excel: This example uses the Application.CreateItem Method to export data from an Excel Worksheet to the default Contacts 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.
‘Ensure that the worksheet data to be posted to Outlook, starts from row number 2:
‘Ensure corresponding columns of data in the Worksheet, as they will be posted in the Outlook Contacts Folder:
‘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 ciOutlook As Outlook.ContactItem
Dim delFolder As Outlook.folder
Dim delItems As Outlook.Items
Dim lLastRow As Long, i As Long, n As Long, c As Long
‘determine last data row in the worksheet:
lLastRow = Sheets(“Sheet1”).Cells(Rows.Count, “A”).End(xlUp).Row
‘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
‘—————————-
‘post each row’s data on a separate contact item form:
For i = 2 To lLastRow
‘Use the Application.CreateItem Method to create a new contact Outlook item in the default Contacts folder. Using this method a new contact item is always created in the default Contacts folder.
‘create and display a new contact form for input:
Set ciOutlook = applOutlook.CreateItem(olContactItem)
‘display the new contact item form:
ciOutlook.Display
‘set properties of the new contact item:
With ciOutlook
.firstName = Sheets(“Sheet1”).Cells(i, 1)
.LastName = Sheets(“Sheet1”).Cells(i, 2)
.Email1Address = Sheets(“Sheet1”).Cells(i, 3)
.CompanyName = Sheets(“Sheet1”).Cells(i, 4)
.MobileTelephoneNumber = Sheets(“Sheet1”).Cells(i, 5)
End With
‘close the new contact item form after saving:
ciOutlook.Close olSave
Next i
‘quit the Oulook application:
applOutlook.Quit
‘clear the variables:
Set applOutlook = Nothing
Set nsOutlook = Nothing
Set ciOutlook = Nothing
Set delFolder = Nothing
Set delItems = Nothing
End Sub
Example 2: This example exports data from an Excel Worksheet to the specified Contacts folder (new contact items added with Items.Add Method), using Early Binding.
Sub ExcelWorksheetDataAddToOutlookContacts2()
‘Automating Outlook from Excel: This example uses the Items.Add Method to export data from an Excel Worksheet to the specified (not necessarily default) Contacts 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.
‘Ensure that the worksheet data to be posted to Outlook, starts from row number 2:
‘Ensure corresponding columns of data in the Worksheet, as they will be posted in the Outlook Contacts Folder:
‘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 cItem As Outlook.ContactItem
Dim delItems As Outlook.Items
Dim lLastRow As Long, i As Long, n As Long, c As Long
‘determine last data row in the worksheet:
lLastRow = Sheets(“Sheet1”).Cells(Rows.Count, “A”).End(xlUp).Row
‘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 reference to the folder (named “cont”) where a new item is to be created:
Set cFolder = nsOutlook.Folders(“Personal Folders”)
Set subFolder1 = cFolder.Folders(“cont”)
‘post each row’s data on a separate contact item form:
For i = 2 To lLastRow
‘Use the Items.Add Method to create a new Outlook contact item in a specific Contacts folder (Export Excel Worksheet Data To Outlook Contacts).
‘Note that if the item type is not specified after Add [viz. Add(olContactItem)], it defaults to its parent folder’s type:
Set cItem = subFolder1.Items.Add
‘display the new contact item form:
cItem.Display
‘set properties of the new contact item:
With cItem
.firstName = Sheets(“Sheet1”).Cells(i, 1)
.LastName = Sheets(“Sheet1”).Cells(i, 2)
.Email1Address = Sheets(“Sheet1”).Cells(i, 3)
.CompanyName = Sheets(“Sheet1”).Cells(i, 4)
.MobileTelephoneNumber = Sheets(“Sheet1”).Cells(i, 5)
End With
‘close the new contact item form after saving:
cItem.Close olSave
Next i
‘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 cItem = Nothing
Set delItems = Nothing
End Sub
Example 3: This example exports data from an Excel Worksheet to the default Contacts folder (new contact items added with Items.Add Method), using Late Binding.
For live code of this example, click to download excel file.
Sub ExcelWorksheetDataAddToOutlookContacts3()
‘Automating Outlook from Excel: This example uses the Items.Add Method to export data from an Excel Worksheet to the default Contacts 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.
‘Ensure that the worksheet data to be posted to Outlook, starts from row number 2:
‘Ensure corresponding columns of data in the Worksheet, as they will be posted in the Outlook Contacts Folder:
‘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 oDelFolder As Object
Dim oCItem As Object
Dim oDelItems As Object
Dim lLastRow As Long, i As Long, n As Long, c As Long
‘determine last data row in the worksheet:
lLastRow = Sheets(“Sheet1”).Cells(Rows.Count, “A”).End(xlUp).Row
‘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 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)
‘post each row’s data on a separate contact item form:
For i = 2 To lLastRow
‘Using the Items.Add Method to create a new Outlook contact item in the default Contacts folder.
Set oCItem = oCFolder.Items.Add
‘display the new contact item form:
oCItem.Display
‘set properties of the new contact item:
With oCItem
.firstName = Sheets(“Sheet1”).Cells(i, 1)
.LastName = Sheets(“Sheet1”).Cells(i, 2)
.Email1Address = Sheets(“Sheet1”).Cells(i, 3)
.CompanyName = Sheets(“Sheet1”).Cells(i, 4)
.MobileTelephoneNumber = Sheets(“Sheet1”).Cells(i, 5)
End With
‘close the new contact item form after saving:
‘The numerical value of olSave is 0. The following code has replaced the Outlook’s built-in constant olSave by its numerical value 0.
oCItem.Close 0
Next i
‘quit the Oulook application:
oApplOutlook.Quit
‘clear the variables:
Set oApplOutlook = Nothing
Set oNsOutlook = Nothing
Set oCFolder = Nothing
Set oDelFolder = Nothing
Set oCItem = Nothing
Set oDelItems = Nothing
MsgBox “Successfully Exported Worksheet Data to the Default Outlook Contacts Folder.”
End Sub