Hey, Scripting Guy! How can I get a list of all the upcoming meetings that have been scheduled by a specific person (namely, my manager)?

— GH

Hey, GH. Before we tackle this question we’d like to reassure anyone who looked out their window recently and saw pigs flying; that’s to be expected. Likewise, any of our readers who happen to be residents of Hades might have been alarmed by the recent cold snap down there. Trust us; there’s nothing to be alarmed about. Pigs flying, hell freezing over – those things are bound to happen when the Scripting Son draws a walk in his last at-bat of the high school baseball season.

That’s right, after 18 games and 60-some at-bats the Scripting Son finally drew a walk, and in his last at-bat of the season to boot. Not that this is particularly unusual. Three years ago, for example, he was on an all-star team that toured Japan. During that time he played in 14 games and walked once: in his final at-bat. That same year he went the entire regular season (nearly 40 games) without walking at all; oddly enough, in his first playoff game, he then walked three times. Last year the Scripting Son played in 60 games and walked four times. Etc., etc.

Needless to say, the Scripting Son doesn’t like to walk.

So what difference does this make to you, GH? Quite a bit, believe it or not. After all, the Scripting Guy who writes this column has always said, “Write a script that retrieves a list of upcoming appointments that have been scheduled by a specific person? When pigs fly!”

Well, in that case:

Const olFolderCalendar = 9

Set objOutlook = CreateObject(“Outlook.Application”)
Set objNamespace = objOutlook.GetNamespace(“MAPI”)
Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)
Set colItems = objFolder.Items
strFilter = “[Organizer] = ‘Ken Myer'”
Set colFilteredItems = colItems.Restrict(strFilter)
For Each objItem In colFilteredItems
    If objItem.Start > Now Then
        Wscript.Echo “Meeting name: ” & objItem.Subject
        Wscript.Echo “Meeting date: ” & objItem.Start
        Wscript.Echo “Duration: ” & objItem.Duration & ” minutes”
        Wscript.Echo “Location: ” & objItem.Location
        Wscript.Echo
    End If
Next

Before we explain how this works we need to go turn up the heat; for some reason it seems a little colder here than it usually is.

OK, that’s better. As you can see, we start out by defining a constant named olFolderCalendar and setting the value to 9; we’ll use this constant to tell Outlook which folder (the Calendar folder) we want to work with. Next we create an instance of the Outlook.Application object, then connect to the MAPI namespace (which happens to be the only namespace we can connect to). We then use the GetDefaultFolder method to bind to the Calendar folder in Outlook:

Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)

That was pretty easy, wasn’t it?

Our next step is to create an object reference to the folder’s Items property:

Set colItems = objFolder.Items

What does that do for us? That grabs a collection of all our appointments (that is, everything in the Calendar folder) and stashes it in a variable named colItems.

And yes, you’re right: we’re not interested in all the items in the Calendar folder, are we? Instead, we’re only interested in upcoming meetings that have been organized by our manager, Ken Myer. Somehow we need to filter our collection. But how?

Well, here’s one suggestion: why not apply a filter?

strFilter = “[Organizer] = ‘Ken Myer'”
Set colFilteredItems = colItems.Restrict(strFilter)

As you can see, in the first line of code we assign a filter value to the variable strFilter. Filters are made up of two parts: a property name (enclosed in square brackets) and a property value (in this case, a value enclosed in single quote marks, because we are dealing with a string). We want to limit our data to meetings arranged by Ken Myer; that is, meetings where the Organizer property is equal to Ken Myer. Hence the two parts of our filter: [Organizer] and ‘Ken Myer’.

In line 2, we then call the Restrict method to apply this filter to our collection. That’s going to create a new collection (named colFilteredItems) that contains information only about those meetings organized by Ken Myer.

That’s almost what we need. However, this sub-collection will contain all the meetings organized by Ken Myer, including those that have already taken place. Because GH is only interested in upcoming meetings, we need to weed out the meetings that have already taken place.

In theory, we could do that by making a fancier filter. However, filtering on dates can be a bit complicated; therefore, we decided to take the easy way out. Rather than apply a double filter – one that limits returned data to meetings organized by Ken Myer, provided that those meetings haven’t been held yet – we applied a filter that returns all the meetings organized by Ken Myer. We then set up a For Each loop to walk through the complete collection of meetings. And what’s the first thing we do in that loop? Check to see if the meeting has already been held:

If objItem.Start > Now Then

If the meeting’s Start time is later than the current date and time (which we can determine using VBScript’s Now function) that means that the meeting hasn’t taken place yet. Therefore, we go ahead and echo back the meeting’s name, start time, duration, and location; that’s what this block of code is for:

Wscript.Echo “Meeting name: ” & objItem.Subject
Wscript.Echo “Meeting date: ” & objItem.Start
Wscript.Echo “Duration: ” & objItem.Duration & ” minutes”
Wscript.Echo “Location: ” & objItem.Location

And then we loop around and repeat the process with the next meeting in the collection.

One thing to watch out for here. Because of the way Outlook stores recurring appointments, those appointments might not show up in your output. That’s due, in large part, to the fact that the start date for a recurring appointment might be long-since past. Because of that, you might need to use two scripts to make sure you get the desired information: the script we just showed you, and a second script designed to retrieve the recurring appointments organized by Ken Myer. What might that second script look like? It might look a little like this:

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 AND [Organizer] = ‘Ken Myer'”
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
        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
    End If
Next

Of course, you could always combine the two scripts into a single script. But we’ll let you take care of that yourself.

Meanwhile, the Scripting Son is getting ready for his summer ball season, a season in which he’ll play another 50-to-60 games. Will he draw many walks during this season? Probably not; at least when it comes to baseball the Scripting Son is impatient, he’s impetuous, and he just goes out there and acts without thinking.

Wonder where he learned all that from ….