Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I get a list of my appointments for a specific month (for example, all my appointments for December, 2007)?

-- HG

SpacerHey, Scripting Guy! AnswerScript Center

Hey, HG. We’ll get to your question in just a moment. Before we do that, however, the Scripting Guy who writes this column wants to take a little time and enjoy the last few days of August (and, in effect, the last few days of summer). And what a summer it’s been here in the Seattle area; this past weekend, for example, the temperature nearly reached 70 degrees Fahrenheit! (And no, we are not making that up.) At one point the Scripting Guy who writes this column actually had the back door to his house propped open, although that changed the moment the wind started blowing and the rain started falling. But that was all right: needless to say, after dealing with daytime temperatures of 68 degrees Fahrenheit, well, a little breeze and a little rain felt pretty good.

The important thing is that, after a relatively tough winter (which really wasn’t all that tough, as long you didn’t mind being without power for a 6-day stretch) the weather gods obviously felt sorry for those of us who live in Seattle. “Those poor Seattle-area people,” they must have thought. “We gave them such a lousy winter; how can we make it up to them? Oh, wait, we know: we’ll give them a summer where the temperature rarely climbs above 70 degrees and the sun hardly ever shines. That should make everyone happy.”

And, of course, it did.

Note. The whole Seattle area seems to be under a curse of some kind: we’re spending billions for dollars to build a light rail system that doesn’t actually go anywhere; we’re stuck serving as the home of the Seattle Supersonics (although that, at least, appears to be temporary); and now we’re forced to endure both a lousy winter and a lousy summer. What did we ever do to deserve this?

Hey, wait a minute: who said, “You gave the world Microsoft, didn’t you?”

Oh, well. As it turns out, that tiny glimmer of light that might have been a tiny of sliver of sunshine has now disappeared behind our omnipresent gray clouds. With that in mind, let’s turn the lights on, crank up the heat a little, and conclude the summer with a bang: by writing a script that retrieves a list of appointments scheduled for the month of December, 2007.

You know, a script like this one:

Const olFolderCalendar = 9

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

Set colItems = objFolder.Items

strFilter = "[Start] >= '12/1/2007' AND [Start] <= '12/31/2007'"
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

As you can see, we start out by defining a constant named olFolderCalendar and setting the value to 9; we’ll use that constant to tell the script which Outlook folder we want to connect to. (As a matter of fact, we do want to connect to the Calendar folder. Good guess!) After defining the constant, we create an instance of the Outlook.Application object, then use the GetNamespace method to bind to the MAPI namespace (which – in case you’re wondering – happens to be the only namespace that we can bind to).

Note. So if the MAPI namespace is the only namespace we can bind to, then do we even have to bind to it? Why don’t we just automatically connect to the MAPI namespace? Well, that’s because … because … well, just because, OK?

After we’ve made the connection to the MAPI namespace, we then call the GetDefaultFolder method to hook us up to the Outlook calendar (note that we pass GetDefaultFolder the constant olFolderCalendar that we created at the start of the script):

Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)

From there, we can get a collection of all our appointments by doing nothing more complicated than creating an object reference to the folder’s Items property, like so:

Set colItems = objFolder.Items

That was pretty easy, wasn’t it? But party’s over: now we’re going to have to do a little bit of work. (But don’t worry; it truly is just a “little bit” of work.) As you no doubt recall, we aren’t interested in a collection of all our appointments; we’re only interested in those appointments scheduled to take place during the month of December, 2007. Therefore, and in order to restrict our collection to appointments scheduled for the month of December, 2007, we need to apply a Filter to the collection, something we do with these two lines of code:

strFilter = "[Start] >= '12/1/2007' AND [Start] <= '12/31/2007'"
Set colFilteredItems = colItems.Restrict(strFilter)

So what’s going on here? Well, in the first line we’re setting the criteria for the Filter: we only want those appointments where the value of the Start property (note that property names must be enclosed in square brackets) is greater than or equal to December 1, 2007 (12/1/2007) and where the value of the Start property is less than or equal to December 31, 2007. The net effect? We’ll only get back appointments that take place (i.e., that start) in December, 2007.

In the second line of code, we simply call the Restrict method, passing along the filter we created (represented by the variable strFilter). That’s going to give us back a brand-new collection, one consisting only of those appointments that take place in December, 2007. From there all we have to do is set up a For Each loop to loop through this sub-collection, doing nothing more inside that loop than echoing back some of the key appointment property values (such as meeting name, date, duration, and location):

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

That’s all there is to it.

Now, one thing to keep in mind here is that this script will not necessarily show any recurring appointments that take place in December, 2007; that’s because Outlook treats recurring appointments slightly different than it does other meetings. If the first occurrence of a recurring appointment takes place in December 2007 then that first occurrence will be returned by the script. However, if the first occurrence for the recurring appointment took place earlier then December, 2007, well …. For HG this wasn’t a problem; she was specifically interested in “one-off” meetings and appointments. However, if that is a problem for you (that is, if you’d like to get a list of recurring appointments as well), take a look at the Office Space article Retrieving a List of Recurring Appointments from Microsoft Outlook and see if that helps.

Office Outlook 2007: If you run this script against Outlook 2007, you will see some recurring meetings that fall in December 2007. However, you’ll see only those recurring meetings where the first occurrence hasn’t happened yet. In addition, the meeting date (objItem.Start) will show the first date in the recurring appointment. For example, if you have a recurring meeting the first Friday of every month starting in October 2007, the meeting will show up in your output for December meetings (since there will be a meeting scheduled for Friday, December 7) but the meeting date of your output will show October 5. If the first occurrence of that meeting was in the past (say, June 1, 2007) you won’t see the December occurrence.

And yes, we know: some of you are interested in retrieving a list of the actual days and dates for recurring appointments (as opposed to a note that simply says that this meeting occurs every Tuesday from now until January 1, 2008). That’s something we’ll probably address in a future Hey, Scripting Guy! But not today; today isn’t the future.

Or at least we hope that today isn’t the future. Things are depressing enough around here as they are. Oh, well; tomorrow the temperature is expected to reach 74 degrees Fahrenheit. Talk about a heat wave, eh?

Editor’s Note: The Scripting Guy who writes this column went to the dentist today. We hope that explains any small hint of grouchiness you might have detected in today’s column. We’re not sure how to explain yesterday’s column…or the day before…or….