Ws.Range("A2:E" & lastRow).Sort Key1:=ws.Range("B2"), Order1:=xlAscending LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row 'determine last data row, basis column B (contains Last Name): Ws.Cells(i, 5).Value = cItem.MobileTelephoneNumber Ws.Cells(i, 3).Value = cItem.Email1Address 'post only those contacts which have a Last Name: 'start posting in worksheet from the second row: 'post each outlook item in the items collection, to a new worksheet row: Set cFolder = nsOutlook.Folders("Personal Folders") 'set reference to the folder named "cont" under the parent folder "Personal Folders": 'export contacts from a specific Contact Items Folder. Ws.Cells(1, 5).Value = "Mobile Telephone Number" 'set and format headings in the worksheet: 'set the worksheet where you want to post Outlook data: 'determine number of items in the collection: Set delFolder = nsOutlook.GetDefaultFolder(olFolderDeletedItems) '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 nsOutlook = applOutlook.GetNamespace("MAPI") create an instance) a NameSpace object variable, to access existing Outlook items. 'use the GetNameSpace method to instantiate (ie. Set applOutlook = New Outlook.Application 'Create a new instance of the Outlook application. 'In this example, 5 types of information from Outlook Contact Items will be posted to the following columns of the specified Worksheet:ĭim i As Long, n As Long, c As Long, lastRow As Long Once this reference is added, a new instance of Outlook application can be created by using the New keyword. '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. 'This example exports contacts (only those contacts which have a Last Name) from a specific Contact Items Folder. 'Automating Outlook from Excel: Post data to Excel Worksheet from Outlook Contacts. CreateObject creates a new instance of Outlook and GetObject returns an already running instance of the Outlook object.Ĭlick here for a detailed explanation of Automation using Early Binding and Late Binding.Įxample 1: This example exports contacts from a specific Contact Items Folder to an Excel Worksheet, using Early Binding.
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. 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. Once this reference is added, a new instance of Outlook application can be created by using the New keyword.Īutomate 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. This is a must when you automate using Early Binding (explained earlier). 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. When you use vba in an Office Application, say Outlook, a reference to the Outlook Object Library is set by default. Examples have been given to automate using both Early Binding and Late Binding.
#How to export contacts from outlook to excel how to
In this section it is explained how to export and post data from Outlook Contacts to an Excel Worksheet, using Automation in vba. Export contacts from Outlook to Excel - automate in vbaĮxport contacts from a specific Contact Items Folder to an Excel Worksheet, using Early BindingĮxport contacts from the default Contact Items Folder to an Excel Worksheet, using Late Bindingĭata from Outlook Contact Items can be posted to an Excel Worksheet, by automating Microsoft Outlook from Excel, using vba.
0 kommentar(er)
