Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Article
10/22/2009

Office Space

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.

Retrieving a List of Recurring Appointments from Microsoft Outlook

On the whole the Scripting Guys aren’t very good at meeting deadlines or dealing with dates. Is that because we’re lazy and irresponsible? Of course not; instead, it’s all Microsoft Outlook’s fault. We try to plan ahead, but we always forget about recurring meetings and appointments that end up throwing a monkey wrench into things. What we need is a quick and easy way to pull up a list of all our recurring meetings, including the dates, times, and locations for all those meetings. However, as far we know Outlook doesn’t provide a quick and easy way to get information about recurring appointments. Therefore, don’t blame us when a forgotten meeting causes us to miss a deadline; blame Outlook.

Note. Which, of course, dovetails nicely with the Scripting Guys motto: The buck stops over there. Not here. Over there, in that other guy’s office.

What’s that? You say we should pull ourselves up by our bootstraps and quit relying on Microsoft Outlook to do all the work for us? You say we should write a script to return this information? Hmmm … you know, that’s so crazy it just might work:

Const olFolderCalendar = 9

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

Set colItems = objFolder.Items

Set colFilteredItems = colItems.Restrict("[IsRecurring] = TRUE")

For Each objItem In colFilteredItems
    Set objPattern = objItem.GetRecurrencePattern
    If objPattern.PatternEndDate > Now Then
        Wscript.Echo "Meeting name: " & objItem.Subject
        Wscript.Echo "Duration: " & objItem.Duration & " minutes"
        Wscript.Echo "Location: " & objItem.Location
        Wscript.Echo
    End If
Next

This script begins by creating a constant named olFolderCalendar and setting the value to 9; we’ll use this later on to tell Outlook which folder (Calendar) we want to retrieve information from. Next we create an instance of the Outlook.Application object and then use the GetNamespace method to bind to the MAPI namespace. (And, yes, even though MAPI is the only namespace you can bind to, this line of code is still required.) We then use the GetDefaultFolder method to bind to the Outlook calendar:

Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)

(See? We told you we would use the constant olFolderCalendar later on!)

After binding to the Calendar we can then use this line of code to retrieve a collection of all the items found in the Calendar folder (in case you’re wondering, all the items will be instances of the AppointmentItem object):

Set colItems = objFolder.Items

Well, that’s a good point: we now have a collection of all the appointments and meetings in the Calendar. However, what we really wanted was a collection of recurring appointments and meetings. But that’s OK; we can use the Restrict method to filter this collection, and weed out non-recurring appointments and meetings. Which is exactly what we do:

Set colFilteredItems = colItems.Restrict("[IsRecurring] = TRUE")

As you can see, we’re creating a new collection (this one named colFilteredItems); we do this by calling the Restrict method and passing it the following parameter:

"[IsRecurring] = TRUE"

You’re way of ahead of us: as a matter of fact IsRecurring is a property of the AppointmentItem object, a property that determines whether or not a given appointment is scheduled to take place more than once. If IsRecurring is True we have ourselves a recurring appointment; if IsRecurring is False, then it’s one-and-done (which is still our favorite kind of meeting).

Note. Like to know more about the Restrict method? Then take a look at this previous Office Space column.

What we do next is set up a For Each loop to walk through this filtered collection of items; for each meeting we then report back the Subject, Duration, and Location. That gives us back data that looks like this:

Meeting name: Updated: Scripting Guys Strategy Meeting
Duration: 60 minutes
Location: 43/3000

Meeting name: Updated: Script Center Refresh
Duration: 60 minutes
Location: Conf Room 5/1255 (10)

Cool, huh?

Well, OK, that’s another good point. Without denigrating this particular script (after all, we worked very hard on it) it’s only moderately useful. Although it lets us know that we have several recurring appointments it doesn’t tell us anything about when those appointments take place: no dates, no times, nothing. No wonder we have so much trouble making plans and keeping deadlines! See, we told you it was Outlook’s fault!

Oh, right: bootstraps and all that. OK, let’s see what we can do about fixing this problem. Information about when – and how often – recurring appointments occur is stored in a separate object: the RecurrencePattern object. What we need to do is call the GetRecurrencePattern method to retrieve the RecurrencePattern object associated with a given appointment. After we do that we can then echo back information such as how often the appointment recurs (RecurrenceType), the start time for each appointment (StartTime), the day the series of appointments began (PatternStartDate), and the day the series of appointments is scheduled to end (PatternEndDate). Here’s a revised script that does all that:

Const olFolderCalendar = 9

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

Set colItems = objFolder.Items

strFilter = "[IsRecurring] = TRUE"

Set colFilteredItems = colItems.Restrict(strFilter)

For Each objItem In colFilteredItems
    Set objPattern = objItem.GetRecurrencePattern
    Wscript.Echo "Meeting name: " & objItem.Subject
    Wscript.Echo "Duration: " & objItem.Duration & " minutes"
    Wscript.Echo "Location: " & objItem.Location
    Wscript.Echo "Recurrence type: " & objPattern.RecurrenceType
    Wscript.Echo "Start time: " & objPattern.StartTime
    Wscript.Echo "Start date: " & objPattern.PatternStartDate
    Wscript.Echo "End date: " & objPattern.PatternEndDate
    Wscript.Echo
Next

When we run this script we get back information similar to this:

Meeting name: Updated: Scripting Guys Strategy Meeting
Duration: 60 minutes
Location: 43/3000
Recurrence type: 1
Start time: 1:00:00 PM
Start date: 5/27/2004
End date: 12/31/4500 11:59:00 PM

Meeting name: Updated: Script Center Refresh
Duration: 60 minutes
Location: Conf Room 5/1255 (10)
Recurrence type: 1
Start time: 11:00:00 AM
Start date: 6/25/2004
End date: 11/25/2005

What we did here was add two new things to our original script. First, we added this line of code to retrieve the RecurrencePattern object associated with each appointment:

Set objPattern = objItem.GetRecurrencePattern

Second, we added these three lines of code to echo back the properties of that RecurrencePattern object. Note that in these three lines we’re using the object reference objPattern and not the object reference objItem. That’s because we’re dealing with the RecurrencePattern object and not the AppointmentItem object. And, yes, it is confusing. But you’ll get used to it.

Here are the three lines of code that echo back the properties of the RecurrencePattern object:

Wscript.Echo "Start time: " & objPattern.StartTime
Wscript.Echo "Start date: " & objPattern.PatternStartDate
Wscript.Echo "End date: " & objPattern.PatternEndDate

Believe it or not, we’re now on the right track. For example, we now know how often these appointments recur; the only problem is that the RecurrenceType is reported back as one of the following integer values:

Constant	Value
olRecursDaily	0
olRecursMonthly	2
olRecursMonthNth	3
olRecursWeekly	1
olRecursYearly	5
olRecursYearNth	6

That means we need to modify our script once again. This time around, we’ll use a Select Case block to convert RecurrenceType to a string value. That Select Case block looks like this:

Select Case objPattern.RecurrenceType
    Case 0 Wscript.Echo "Recurs daily."
    Case 1 Wscript.Echo "Recurs weekly."
    Case 2 Wscript.Echo "Recurs every N months."
    Case 3 Wscript.Echo "Recurs monthly."
    Case 5 Wscript.Echo "Recurs yearly."                
    Case 6 Wscript.Echo "Recurs every N years."
End Select

In this revised script, we no longer echo back the value of RecurrenceType. Instead, we check the value of RecurrenceType, then echo back a more useful string value instead. For example, suppose RecurrenceType equals 1. In that case, we echo back the message “Recurs weekly.” That’s what this particular line of code does:

Case 1 Wscript.Echo "Recurs weekly."

When we run the revised script, we get back output similar to this:

Meeting name: Updated: Scripting Guys Strategy Meeting
Duration: 60 minutes
Location: 43/3000
Recurs weekly.
Start time: 1:00:00 PM
Start date: 5/27/2004
End date: 12/31/4500 11:59:00 PM

Meeting name: Updated: Script Center Refresh
Duration: 60 minutes
Location: Conf Room 5/1255 (10)
Recurs weekly.
Start time: 11:00:00 AM
Start date: 6/25/2004
End date: 11/25/2005

There’s really only one thing missing now: the actual days/dates that the meetings take place. To tell you the truth, we’ve saved that part for last because now things get a little complicated. Each RecurrenceType has a set of properties that help you pinpoint days and dates; you can find a list of RecurrenceTypes and their associated properties in the Microsoft Outlook VBA Language Reference. For example, weekly appointments have the following two associated properties:

Interval, which tells you how often the meeting recurs (every week, every 2 weeks, every 3 weeks, etc.)
DayofWeekMask, which tells you the day of the week (Monday, Tuesday, Wednesday, etc.) that the meeting falls on.

Like RecurrenceType, many of these properties return integer values; for example, DayOfWeekMask returns one of the following:

Constant	Value
olSunday	1
olMonday	2
olTuesday	4
olWednesday	8
olThursday	16
olFriday	32
olSaturday	64

In other words, to return truly useful information we need to convert all these integer values to something a bit easier to understand. We don’t have room in this column to describe each and every conversion in detail; we will, however, tack a script on to the end of this column that performs these conversions for you.

Note. In order to keep things relatively simple, we’re going to cheat a little and assume that all our meetings occur only a specific day (e.g., the second Thursday of each month). What about meetings that occur on, say, the second and Thursday of each month? We can get that information using a script, but it’s a tad bit complicated, and is something we’ll have to address in a future column. In other words, if you have oddball meeting schedules like that today’s script will be partially, but not completely, useful to you. But we’ll fix that later on.

For now, let’s show you code that converts the DayOfWeekMask integer to the name of the day that the meeting occurs on:

Select Case objPattern.DayOfWeekMask
    Case 1
        strDay = "Sunday"
    Case 2
        strDay = "Monday"
    Case 4
        strDay = "Tuesday"
    Case 8
        strDay = "Wednesday"
    Case 16
        strDay = "Thursday"
    Case 32
        strDay = "Friday"
    Case 64
        strDay = "Saturday"
End Select

When you run this script you get back output that looks like this:

Meeting name: Updated: Scripting Guys Strategy Meeting
Duration: 60 minutes
Location: 43/3000
Occurs every Thursday.
Start time: 1:00:00 PM
Start date: 5/27/2004
End date: 12/31/4500 11:59:00 PM

Meeting name: Updated: Script Center Refresh
Duration: 60 minutes
Location: Conf Room 5/1255 (10)
Occurs every Friday.
Start time: 11:00:00 AM
Start date: 6/25/2004
End date: 11/25/2005

Yes, much better.

There’s actually more things we can do with recurring appointments, but that’s probably enough for one day. (Don’t worry; we’re pretty sure your head will stop spinning sooner or later.) In the meantime – and, as promised – here’s a more complete script for returning (and translating into readable form) recurring meetings and appointments. Incidentally, we also added an extra If-Then block to the script:

If objPattern.PatternEndDate > Now Then

This simply checks to see if the recurring appointment series has already expired. If it has, the PatternEndDate will not be greater than the current date and time, and the appointment won’t show up in our output. That ensures that we get back only information about upcoming meetings and appointments.

Here’s the script:

Const olFolderCalendar = 9

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

Set colItems = objFolder.Items

strFilter = "[IsRecurring] = TRUE"

Set colFilteredItems = colItems.Restrict(strFilter)

For Each objItem In colFilteredItems
    Set objPattern = objItem.GetRecurrencePattern
    If objPattern.PatternEndDate > Now Then
        Wscript.Echo "Meeting name: " & objItem.Subject
        Wscript.Echo "Duration: " & objItem.Duration & " minutes"
        Wscript.Echo "Location: " & objItem.Location
                Select Case objPattern.RecurrenceType
            Case 0 
                Wscript.Echo "Recurs daily."
            Case 1 
                If objPattern.Interval = 1 Then
                    Select Case objPattern.DayOfWeekMask
                        Case 1
                            Wscript.Echo "Occurs every Sunday."
                        Case 2
                            Wscript.Echo "Occurs every Monday."
                        Case 4
                            Wscript.Echo "Occurs every Tuesday."
                        Case 8
                            Wscript.Echo "Occurs every Wednesday."
                        Case 16
                            Wscript.Echo "Occurs every Thursday."
                        Case 32
                            Wscript.Echo "Occurs every Friday."
                        Case 64
                            Wscript.Echo "Occurs every Saturday."
                        End Select
                Else
                    Select Case objPattern.DayOfWeekMask
                        Case 1
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                " weeks on Sunday."
                        Case 2
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Monday."
                        Case 4
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Tuesday."
                        Case 8
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Wednesday."
                        Case 16
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Thursday."
                        Case 32
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Friday."
                        Case 64
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Saturday."
                        End Select
                    End If
            Case 2 Wscript.Echo 
               intInstance = objPattern.Instance
                Select Case intInstance
                    Case 1
                        strInstance = "first"
                    Case 2
                        strInstance = "second"
                    Case 3
                        strInstance = "third"
                    Case 4
                        strInstance = "fourth"
                    Case 5
                        strInstance = "fifth"
                    Case 6
                        strInstance = "sixth"
                End Select
                Select Case objPattern.DayOfWeekMask
                    Case 1
                        strDay = "Sunday"
                    Case 2
                        strDay = "Monday"
                    Case 4
                        strDay = "Tuesday"
                    Case 8
                        strDay = "Wednesday"
                    Case 16
                        strDay = "Thursday"
                    Case 32
                        strDay = "Friday"
                    Case 64
                        strDay = "Saturday"
                End Select
                intInterval = objPattern.Interval
                If intInterval = 1 Then
                    Wscript.Echo "Occurs on the " & strInstance & " " & strDay &  _
                        " of each month."
                Else
                    Wscript.Echo "Occurs on the " & strInstance & " " & strDay & _
                        " every " & intInterval & " months."
                End If         
            Case 3 
                If objPattern.Interval = 1 Then
                    Wscript.Echo "Recurs each month."
                Else
                    Wscript.Echo "Recurs every " & objPattern.Interval & " month."
                End If 
                intInstance = objPattern.Instance
                Select Case intInstance
                    Case 1
                        strInstance = "first"
                    Case 2
                        strInstance = "second"
                    Case 3
                        strInstance = "third"
                    Case 4
                        strInstance = "fourth"
                    Case 5
                        strInstance = "fifth"
                    Case 6
                        strInstance = "sixth"
                End Select
                Select Case objPattern.DayOfWeekMask
                    Case 1
                        strDay = "Sunday"
                    Case 2
                        strDay = "Monday"
                    Case 4
                        strDay = "Tuesday"
                    Case 8
                        strDay = "Wednesday"
                    Case 16
                        strDay = "Thursday"
                    Case 32
                        strDay = "Friday"
                    Case 64
                        strDay = "Saturday"
                End Select
                Wscript.Echo "Occurs on the " & strInstance & " " & strDay & " of the month."

            Case 5 
                strMonth = MonthName(objPattern.MonthOfYear)
                Wscript.Echo "Occurs each year on " & strMonth & " " & _
                    objPattern.DayofMonth & "."                 
            Case 6 
                intInstance = objPattern.Instance
                Select Case intInstance
                    Case 1
                        strInstance = "first"
                    Case 2
                        strInstance = "second"
                    Case 3
                        strInstance = "third"
                    Case 4
                        strInstance = "fourth"
                    Case 5
                        strInstance = "fifth"
                    Case 6
                        strInstance = "sixth"
                End Select
                Select Case objPattern.DayOfWeekMask
                    Case 1
                        strDay = "Sunday"
                    Case 2
                        strDay = "Monday"
                    Case 4
                        strDay = "Tuesday"
                    Case 8
                        strDay = "Wednesday"
                    Case 16
                        strDay = "Thursday"
                    Case 32
                        strDay = "Friday"
                    Case 64
                        strDay = "Saturday"
                End Select
                strMonth = MonthName(objPattern.MonthOfYear)
                Wscript.Echo "Occurs on the " & strInstance & " " & strDay & " of " & _
                    strMonth & " each year."
        End Select
        Wscript.Echo "Start time: " & objPattern.StartTime
        Wscript.Echo "Start date: " & objPattern.PatternStartDate
        Wscript.Echo "End date: " & objPattern.PatternEndDate
        Wscript.Echo
    End If
Next

Partager via

Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Retrieving a List of Recurring Appointments from Microsoft Outlook

Ressources supplémentaires