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.

Creating a New Contact in Microsoft Outlook

A week or two ago we promised that we’d start including more Microsoft Outlook scripts in this column, and if there’s one thing you can say about the Scripting Guys it’s this: we always keep our promises.

Well, OK: we sometimes keep our promises.

OK, fine: there’s a first time for everything. Happy now?

Actually you should be happy, because we have a nifty little script for you today, one that creates a new Microsoft Outlook contact. We’ll show you a basic script for creating a contact, then show you a somewhat more realistic scenario: a script that reads information from an Excel spreadsheet and then creates a contact item for each person listed on that spreadsheet.

Yes, that would be a handy little script to have, wouldn’t it?

Before we do that, however, let’s make sure we understand the basic principles behind creating a contact. Here’s a script that creates a contact for Ken Myer, filling in some additional contact properties along the way:

Const olContactItem = 2

Set objOutlook = CreateObject("Outlook.Application")
Set objContact = objOutlook.CreateItem(olContactItem)

objContact.FullName = "Ken Myer"
objContact.Email1Address = "kenmyer@fabrikam.com"
objContact.CompanyName = "Fabrikam"
objContact.JobTitle = "Network Administrator"
objContact.HomeTelephoneNumber = "555-555-8888"
objContact.HomeAddress = "3725 205th NE" & vbCrLf & "Redmond, WA 98052"
objContact.Birthday = "9/15/1966"

objContact.Save

Hey, had we known creating a contact was this easy we would have written this column a long time ago! As you can see we begin by defining a constant named olContactItem and setting the value of this constant to 2; we’ll use this to tell Outlook what kind of item to create. We then connect to Outlook (we’re assuming that Outlook is already running) and call the CreateItem method to create the new contact:

Set objOutlook = CreateObject("Outlook.Application")
Set objContact = objOutlook.CreateItem(olContactItem)

Next we assign values to a bunch of contact properties, including FullName, Email1Address, and CompanyName. By no means do these represent all the properties of a contact; for a complete list see the Microsoft Outlook VBA Language Reference on MSDN. After configuring the desired property values we then call the Save method to save our new contact:

objContact.Save

That’s all there is to it.

Now, what about that script that reads information from an Excel spreadsheet and then creates a contact item for each person on that sheet? Well, suppose we have a simple little spreadsheet that looks like this:

Here’s a script that creates a contact item for each of three people listed on this spreadsheet (C:\Scripts\Contacts.xls):

Const olContactItem = 2

Set objOutlook = CreateObject("Outlook.Application")

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\contacts.xls")

x = 1

Do Until objExcel.Cells(x,1).Value = ""

    Set objContact = objOutlook.CreateItem(olContactItem)
    objContact.FullName = objExcel.Cells(x,1).Value
    objContact.CompanyName = objExcel.Cells(x,2).Value
    objContact.Email1Address = objExcel.Cells(x,3).Value
    objContact.Save

    x = x + 1
Loop

objExcel.Quit

We aren’t going to talk too much about the Excel code; for that you might want to peruse this Scripting Guys article on MSDN. But, then again, we never promised we’d do everything for you.

Really? How come we don’t remember that…?