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 On Error Resume Next '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("email@example.com") Set subFolder1 = cFolder.Folders("TDF Accounts") '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
From novice to tech pro — start learning today.