Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Exporting Contact Information to Microsoft Excel

Microsoft Outlook is not only a great program for sending, organizing, and doing whatever else you need to do with email, but it’s also a handy place to store contact information. Outlook can import contact information sent as a “vCard,” sort and search contacts, and if you have the right hardware it can even dial the person’s phone number for you. All of that is great…as long as you’re working in Outlook. But what if you’d like to work with contact information outside of Outlook? Is there any way to quickly and easily export contact information to Microsoft Excel or some other program? Is there any way to script that process?

You know, one of these days we’re going to pose a question like that and the answer will be, “Nope,” and the column will just end right then and there. But today isn’t one of those days. Instead, the answer today is the same as it always is: of course there’s a quick and easy way to do that. And we’re here to show you how.

Don’t we get tired of asking rhetorical questions all the time? Shouldn’t we try something different for a change? Wouldn’t it be better – hey! Let’s get back to business, OK?

The secret to exporting contact information to Excel lies in getting the contact information in the first place; once you have that the rest is actually pretty easy. Because of that, let’s start by figuring out how to extract contact information from Outlook; after we’ve mastered that task we’ll talk about writing that information to an Excel spreadsheet.

Here’s a script that retrieves the FullName and BusinessTelephoneNumber properties for all the contacts in an Outlook address book:

On Error Resume Next

Const olFolderContacts = 10

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")

Set colContacts = objNamespace.GetDefaultFolder(olFolderContacts).Items

For Each objContact In colContacts
    Wscript.Echo objContact.FullName, objContact.BusinessTelephoneNumber
Next

The script starts off by defining a constant named olFolderContacts and setting the value to 10; we’ll use this constant to tell the script to retrieve information from the Contacts folder in Outlook. We then use this line of code to bind to the current instance of Outlook (this script assumes that Outlook is already running):

Set objOutlook = CreateObject("Outlook.Application")

Having made a connection with Outlook we then use the GetNamespace method to bind to the MAPI namespace; this namespace (which is actually the only namespace you can bind to) lets us work with various objects in Outlook, including folder objects. In fact, to retrieve a list of all the contacts in the Contacts folder all we have to do is use this line of code:

Set colContacts = objNamespace.GetDefaultFolder(olFolderContacts).Items

What we’re doing here is returning a collection of contacts and storing that collection in a variable named colContacts. As you can see, we call the GetDefaultFolder method, passing it the value olFolderContacts we defined earlier. At the same time we request the Items property; this is simply a list of all the items in the specified folder. Because we’re connecting to the Contacts folder the Items property consist of a collection of contacts.

The rest is easy: we create a For Each loop to cycle through the collection, echoing – in this case – the contact’s FullName and BusinessTelephoneNumber. And when we do that we get back information similar to this:

Ken Myer (410) 555-9683
Pilar Ackerman (425) 555-2005
Jonathan Haas (425) 555-5581

Nice, huh? And, needless to say, FullName and BusinessTelephoneNumber are not the only properties available for a contact; for a complete list, take a look at the Microsoft Outlook VBA Language Reference on MSDN.

OK, that’s the good news. Now comes the bad news. You might have noticed that all we echoed back in the previous script was the contact’s name and phone number. As long as we echo these non-email property values we can run our script in fully-automated fashion. But suppose we wanted to echo the contact’s name and email address, using code like this:

For Each objContact In colContacts
    Wscript.Echo objContact.FullName, objContact.Email1Address
Next

Can we do this in a script? Yes. Can we do this in fully-automated, unattended fashion? No. Instead, the moment your script tries to access the Email1Address property the following dialog pops up on your screen:

You will need to manually answer this dialog box before the script can proceed. (And because we’re in a For Each loop, if you answer No you’ll have to answer this dialog box for each contact in the list. If you answer Yes it assumes Yes for all contacts.) If you don’t answer it, the dialog box will time out and the script will not be able to retrieve the email addresses.

As you probably guessed, this is a security feature built into Outlook; it’s there to prevent viruses from sending email to all your contacts. Of course, it also prevents you from starting your script and then walking away, confident that the script will complete without any user intervention. (On the bright side, after you allow access and click Yes the script will run in uninterrupted fashion.) We won’t delve into all the whys and wherefores of Outlook security today. Just keep in mind that if you retrieve any email-related property you’ll need to deal with this dialog box.

OK, now what about exporting this information to Excel? Well, that’s pretty easy to do. We won’t discuss the Excel portion of the script in any detail today; if you’re looking for basic information on writing data to an Excel spreadsheet you might want to read this Scripting Clinic column on MSDN. Here’s the script, writes the FullName and BusinessTelephoneNumber to an Excel spreadsheet:

On Error Resume Next

Const olFolderContacts = 10

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")

Set colContacts = objNamespace.GetDefaultFolder(olFolderContacts).Items

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1) = "Name"
objExcel.Cells(1, 2) = "Business Phone"

i = 2

For Each objContact In colContacts
    objExcel.Cells(i, 1).Value = objContact.FullName
    objExcel.Cells(i, 2).Value = objContact.BusinessTelephoneNumber
    i = i + 1
Next

Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit

What if you wanted to include more than just the FullName and BusinessTelephoneNumber properties? That’s easy enough: you just need to do two things.

First, create some additional column headers. For example, suppose you wanted to include the CompanyName and Birthday properties. In that case, your code for configuring the column headers might look like this:

objExcel.Cells(1, 1) = "Name"
objExcel.Cells(1, 2) = "Business Phone"
objExcel.Cells(1, 3) = "Company"
objExcel.Cells(1, 4) = "Birthday"

Second, you need to include CompanyName and Birthday in the For Each loop, the loop where you actually write the information to the spreadsheet. For example:

For Each objContact In colContacts
    objExcel.Cells(i, 1).Value = objContact.FullName
    objExcel.Cells(i, 2).Value = objContact.BusinessTelephoneNumber
    objExcel.Cells(i, 3).Value = objContact.CompanyName
    objExcel.Cells(i, 4).Value = objContact.Birthday
    i = i + 1
Next

Run the script and you should wind up with a spreadsheet that looks something like this:

Now is that cool or what?

No, wait; that wasn’t a rhetorical question, we’d really like to hear your answer. Wait, come back, hey….