Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the 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.

Adding a List of Holidays to Microsoft Outlook

Here’s a true story for you. One of the Scripting Guys (let’s call him “Greg”) took several days off this past Christmas. On Monday, January 3rd, he reported for work, bright and early. (Typically Greg comes in early and then leaves early. And, no, he doesn’t always leave really early. Just during baseball season.)

Around 9:30 AM or so Greg - who is not the most observant fellow - noticed that there weren’t very many people in the building; he assumed people were just taking their time rolling in after a three-day weekend. By 10:30 Greg was beginning to get a bit suspicious; after all, there was nobody else in the building. A short while later he checked one of Microsoft’s internal Web sites. Sure enough, January 3rd was an official Microsoft holiday: he could have - and should have - stayed home and slept in that morning.

Now, is this Greg’s fault? Of course not: it’s Microsoft’s fault. After all, an organization that truly cared about its employees (particularly the dumber ones, like Greg) would have sent a script around to everyone, a script that would have populated Outlook with a list of all the official company holidays. A script that might have looked something like this:

Const olFolderCalendar = 9
Const olAppointmentItem = 1
Const olOutOfOffice = 3

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

Set objDictionary = CreateObject("Scripting.Dictionary")
objDictionary.Add "November 24, 2005", "Thanksgiving"    
objDictionary.Add "November 25, 2005", "Thanksgiving"    
objDictionary.Add "December 26, 2005", "Christmas Day"

colKeys = objDictionary.Keys

For Each strKey in colKeys
    dtmHolidayDate = strKey
    strHolidayName = objDictionary.Item(strKey)

    Set objHoliday = objOutlook.CreateItem(olAppointmentItem)  
    objHoliday.Subject = strHolidayName
    objHoliday.Start = dtmHolidayDate & " 9:00 AM"
    objHoliday.End = dtmHolidayDate & " 10:00 AM"
    objHoliday.AllDayEvent = True
    objHoliday.ReminderSet = False
    objHoliday.BusyStatus = olOutOfOffice
    objHoliday.Save
Next

Editor’s Note. Not only did “Greg” miss the holiday, he also missed the link on the internal Web page that says “Please click on the following link to add all of the Holidays for the current year to your Outlook calendar.” On the bright side, if he had seen this he wouldn’t have written this script for you to enjoy today, and the extra day made up for one or two of the - we won’t say how many, his manager might be watching - days he left early.

No, it would not be better to just go to work on holidays rather than have to figure out how this script works. Trust us: this script is actually pretty straightforward. It starts off by defining three constants: olFolderCalendar, olAppointmentItem, and olOutOfOffice. The first constant (olFolderCalendar) will be used to tell the script to open the Outlook calendar; the other two constants will be used when setting an all-day appointment for each holiday.

After defining the constants the next three lines of code create an instance of the Outlook.Application object, bind to the MAPI namespace, and then connect to the Outlook calendar. After that we use the following code to define the official company holidays (we’re only defining three such holidays in our example, but you can add as many days off as your company is willing to give you):

Set objDictionary = CreateObject("Scripting.Dictionary")
objDictionary.Add "November 24, 2005", "Thanksgiving"    
objDictionary.Add "November 25, 2005", "Thanksgiving"    
objDictionary.Add "December 26, 2005", "Christmas Day"

What we’re doing is using the Dictionary object (part of the Script Runtime library) to hold information about each holiday. The Dictionary object is designed to hold key-item values; in our case, the key is the date (for example November 24, 2005) and the item is the holiday name (Thanksgiving). Note that - at least in our sample script - it’s important that the date be set as the key and the name be set as the item. Why? Well, Dictionary keys must be unique. As you can see, Microsoft employees are given two days off for Thanksgiving. Suppose we listed the holiday name as the key:

objDictionary.Add "Thanksgiving", "November 24, 2005"  
objDictionary.Add "Thanksgiving", "November 25, 2005"  
objDictionary.Add "Christmas Day", "December 26, 2005"

That would result in two keys with the same name - Thanksgiving - and would cause the script to fail. Make sure your Dictionary keys are unique.

After populating the Dictionary we then use this line of code to return a collection of all the key-item pairs in that Dictionary:

colKeys = objDictionary.Keys

Note. OK, it is a little weird to add all the holidays into a Dictionary object and then immediately ask to get that same information back. But that’s because - in an attempt to keep the script simple - we hard-coded in the holiday information. If you were reading this information in from a text file or something, you wouldn’t know all the names and dates in advance and this wouldn’t look so weird.

Once we have our collection we can then set up a For Each loop to walk through the names and dates and then schedule an all-day appointment for each holiday.

How do we do that? To begin with, we assign the date (the Dictionary key) and the holiday name (the corresponding Dictionary item) to a pair of variables:

dtmHolidayDate = strKey
strHolidayName = objDictionary.Item(strKey)

We then use the CreateItem method to create an instance of the AppointmentItem object. How does CreateItem know to whip up an AppointmentItem? That’s easy: because we passed the method the constant olAppointmentItem. That all sounds like a lot of work, but it takes just one line of code:

Set objHoliday = objOutlook.CreateItem(olAppointmentItem)

Next we configure various property values for our new appointment:

objHoliday.Subject = strHolidayName
objHoliday.Start = dtmHolidayDate & " 9:00 AM"
objHoliday.End = dtmHolidayDate & " 10:00 AM"
objHoliday.AllDayEvent = True
objHoliday.ReminderSet = False
objHoliday.MeetingStatus = olNonMeeting
objHoliday.BusyStatus = olOutOfOffice

Most of these property values are self-explanatory; if you need more information on any of them, however, you might want to check out the Microsoft Outlook VBA Language Reference. After configuring the property values we call the Save method to save the new appointment to our Outlook calendar:

objHoliday.Save

Having saved the first holiday we then loop around and repeat the process until we’ve run out of elements in the Dictionary. From that point on there will never be any question as to which days are company holidays and which days aren’t.

In other words, a script like this will prevent Greg from doing anything equally dumb in the future, right?

Well, remember, we’re writing scripts here, not working miracles. But it won’t hurt.