Via your scripts, you can add Contacts, Journal and Calendar entries with ease. No bull.

Outlook Automation with OLE

Via your scripts, you can add Contacts, Journal and Calendar entries with ease. No bull.

• By Chris Brooke
• May 01, 2001

Q: What do a matador and Office automation have in common?

A: OLE. Sorry you had to be subjected to that. My wife is due any day now, and I’m not thinking very clearly. And while I’m not going to be spending any more precious words talking about bullfighting, I will be finishing up my discussion of automation, which is made possible by what? Yes... OLE!

Last month we used automation to open Outlook and send an e-mail alert. However, Outlook isn’t just e-mail. It includes Tasks, Calendar, Journal and Contacts. Today, we’re going to look at adding contact information from within your scripts.

' NewContact.vbs
Option Explicit
Dim objOutlook, objContact

' Start Outlook.
Set objOutlook = CreateObject("Outlook.Application")

' Create and Open a new contact.
Set objContact = objOutlook.CreateItem(2)
   ' 2=Outlook Contact Item

' Setup Contact information...
With objContact
   .FullName = "Chris Brooke"
   .Birthday = "4/12/1965"
   .CompanyName = "ComponentSource"
   .HomeTelephoneNumber = "770-250-6100"
   .Email1Address = "[email protected]"
   .JobTitle = "Product and Technology Editor"
   .HomeAddress = "3391 Town Point Drive Suite 350" &
      vbCr & "Kennesaw, GA 30144"
End With

' Save Contact...
objContact.Save

The first thing I’m sure you’ve noticed is the use of color in the script. This style follows the Visual Basic standard to make the script easier to analyze. Reserved words are in blue and comments are in green. Even though our perennial favorite script editor Notepad (or “Visual Notepad,” as it’s called by true believers) doesn’t allow different colors, I include them to 1) make things clearer, and 2) get you on the road to thinking like a VB programmer (you know you love it!).

First, I create an instance of Outlook the same as before. The only difference is that I then create a contact item rather than a mail item. If instances are already open, they’ll be used automatically. By now, I’m sure you’ve started to see a pattern emerging. This same script can be used—with minor modifications—to create a Journal entry, Calendar entry and so on.

The “meat” of the script is the section in which I simply create a Contact item, add the information, and save it to the mailbox. In this script I’m saving it to the Contacts folder of the current mailbox. If I wanted to, I could save the information in a Contacts folder in the Public folders of my Exchange Server.

Homework
Now, rarely are you going to “hard-wire” Contact information into your script. Rather, you’ll want to import it from a text file, database, Excel spreadsheet or Word document. This could be done using the FileSystemObject that I’ve explored previously (depending on the file format that the data is saved in), but wouldn’t it be much better to use the associated application to get at the data? The answer is, of course, a resounding “Yes!” Chances are, you’re likely to receive contact information in a spreadsheet or delimited file that you can open in Excel. Using Excel automation you can write a script to retrieve the information from the spreadsheet and add it to Outlook. Hmm... sounds like a good assignment for homework! I’ll even give you some pointers for opening Excel spreadsheets using automation:

• Remember to specify the proper worksheet.
• Remember to have a clear and standard format for these documents.
• Be prepared to do some real digging into the Excel object model.

One final word of advice: In order to effectively automate this process, you’ll need a pretty big LOOP (best handled by using Subs and Functions—remember those?) and some error handling. You’ll need to be prepared for importing from a file that could possibly not (say it isn’t so!) be in the correct format. For the purposes of your homework, however, I’ll only be grading on “getting the job done.” In other words—you can assume that the file is clean and in the proper format.

Now It Gets Interesting
One of the really cool things about automation is that you have a high level of control over document properties, attributes and so on. This is used primarily for embedding Office functionality into, say, a Visual Basic application. For the purposes of admin scripting, you aren’t likely to use most of these exposed features, but it’s good to know that they’re there, just in case.

Also, don’t forget that you can create documents using automation as easily as you can open/read them. Again, this is best used when you need to automate functions such as embedding a spreadsheet into a Word document or changing the properties of existing documents. If you ever get to the point when you’re using scripts to create documents from scratch, you have way too much free time!

Coming Up
Next month I’ll announce the second winner in our “Scripting Suggestions” e-mail campaign. I’ll try to get it written before the baby gets here. Otherwise, you may be subjected to even more stupid jokes brought on by 2 a.m. feedings and a sudden upswing in the amount of baby talk and incoherent “cooing” coming from my mouth.

Shortly after submitting this article, the columnist was pleased to report the birth of Samuel John Brooke. Mother and son are doing fine.