Hey, Scripting Guy! How Can I Retrieve Free/Busy Time From Microsoft Outlook?

ScriptingGuy1

Hey, Scripting Guy! How can I write a script that retrieves free/busy time from Outlook?
— JH

Hey, JH. As we noted yesterday, the Scripting Guys are back from a whirlwind trip to Orlando, where we flew into town, delivered a pair of instructor-led labs (Windows PowerShell for VBScripters) and then – wanting to take advantage of all the many tourist activities available in the great state of Florida – immediately rushed back to the airport and sat on the tarmac for several hours waiting out a thunderstorm. Before we go any further today we’d like to take a minute to thank everyone who attended our two labs; we appreciate it, and we hope you found those labs useful. We’d also like to take a minute to report back some interesting facts and figures about the city of Orlando:

At the hotel where the Scripting Guys stayed, you could – for just $100 – take advantage of a spa treatment in which your body was rubbed with coconut oil, drenched in a warm milk bath, and then wrapped in aluminum foil. According to the brochure, this is an “ancient Balinese ritual.” The question that immediately popped into our minds, of course, was this: didn’t they use to have cannibals in Bali? (If they didn’t, apparently they have them now.) Of course, if being rubbed with coconut oil and drenched in warm milk didn’t appeal to you you could instead be coated with lime juice and then rubbed with ginger salt before being wrapped in aluminum foil.

And yes, as near as we can tell pretty much everything you do in Orlando results in you being wrapped in aluminum foil. Interesting.

At Universal Studios there’s a sign that reads “Valet Bus Parking.” We assume that means valet and bus parking, but we aren’t sure; maybe Universal Studios really does offer valet bus parking. Unfortunately, we couldn’t verify that because we didn’t bring our bus with us. Maybe next year.

We know that a number of you have been wondering, “Is the Scripting Editor good at anything?” (Obviously she isn’t very good at editing; if she was, this column might actually make sense once in a while.) Well, we now have a definitive answer for you: no, she isn’t good at anything. Or at least she isn’t anywhere near as good as the Scripting Guy who writes this column. For example, at Universal Studios we rode the Men in Black ride twice; this is a ride where you use a little laser gun to shoot aliens, racking up points the more aliens you shoot. How did that little competition end up? Let’s put it this way: the Scripting Guy who writes this column kicked the Scripting Editor’s butt twice. And how many times did the Scripting Editor come out on top?

You know what? We’ll let you do the math on that one.

Although the answer is this: zero. Which also matches the number of wins the Scripting Editor had in miniature golf, despite her somewhat questionable sportsmanship on the 18^th and final hole. (Where she twice hit the Scripting Guy who writes this column on the arm as he tried to putt, and another time blocked the hole with her putter. And yet she still lost.)

At any rate, Orlando was fun, and we again want to thank everyone who attended our two talks. (We also wanted to mention the fact that, in conjunction with our trip, we’ve updated and enhanced both our VBScript to Windows PowerShell Conversion Guide and our Windows PowerShell Owner’s Manual.) But you know what? That was then, and this is now. Which must mean it’s time to answer JH’s question about retrieving free/busy time from Microsoft Outlook:

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

Set objRecipient = objNameSpace.CreateRecipient("kenmyer")
strFreeBusyData = objRecipient.FreeBusy(#6/16/2008#, 60)

dtmStartDate = #6/16/2008#

For i = 1 to Len(strFreeBusyData) Step 24 
    Wscript.Echo dtmStartDate
    strDay = Mid(strFreeBusyData, i, 24)

    For x = 1 to 12
        If x = 1 Then
            strTime = "12 AM: "
        Else
            strTime = x - 1 & " AM: "
        End If 

        intFreeBusy = Mid(strDay, x, 1)
        If intFreeBusy = 1 Then
            strFreeBusy = "Busy"
        Else
            strFreeBusy = "Free"
        End If

        Wscript.Echo strTime & strFreeBusy
    Next

    For x = 13 to 24
        If x = 13 Then
            strTime = "12 PM: "
        Else
            strTime = x - 13 & " PM: "
        End If 

        intFreeBusy = Mid(strDay, x, 1)
        If intFreeBusy = 1 Then
            strFreeBusy = "Busy"
        Else
            strFreeBusy = "Free"
        End If

        Wscript.Echo strTime & strFreeBusy
    Next

    Wscript.Echo
    dtmStartDate = dtmStartDate + 1

    If dtmStartDate > #6/17/2008# Then
        Exit For
    End If
Next

What’s that? Yes, this is a complicated-looking little script, isn’t it? That’s due to the … interesting … way in which Outlook returns free/busy time. If you request free/busy time for a two-day period Outlook will return a value similar to this:

000000000100000000000000000000000010000000000000

As it turns out, that’s not ancient Balinese; instead there’s method to this madness. Outlook returns a 1 or a 0 for each hour of the day: a 1 means that the person is busy at that time, and a 0 means that the person is free at that time. For a two-day period that means that we’re going to get a string of 48 1s and 0s. (Why? Because there are 48 hours in a two-day period.) The first digit in this string represents the hour from midnight to 1:00 AM on day 1; the second digit represents the hour from 1:00 AM to 2:00 AM on day 1; and so on. If we break this value down we’ll see that the free/busy time for the first 10 hours of day 1 look like this:

12 AM: Free
1 AM: Free
2 AM: Free
3 AM: Free
4 AM: Free
5 AM: Free
6 AM: Free
7 AM: Free
8 AM: Free
9 AM: Busy

What that means is that we can figure out free/busy time from the clump of data Outlook returns to us. The only question remaining is this: how do we figure out free/busy time from the clump of data Outlook returns to us?

Well, that question and this question: who the heck brings their cat with them in vacation?

To begin with, we create an instance of the Outlook.Application object and then use the GetNamespace method to bind to the MAPI namespace; that gets us hooked up to Outlook. Once we’re connected to Outlook we use this line of code to create a new Recipient object for the person whose schedule we need to access (in this case a user with the email address kenmyer):

Set objRecipient = objNameSpace.CreateRecipient("kenmyer")

We then use the FreeBusy method to retrieve free/busy information for kenmyer, starting on June 16, 2008:

strFreeBusyData = objRecipient.FreeBusy(#6/16/2008#, 60)

Before we go much further there are a few things we should discuss here. As you can see, we pass the FreeBusy method two parameters: the starting date (6/16/2008) and the value 60. What’s the 60 for? This value indicates the time interval we want to use; in this case, we’re asking Outlook to return free/busy time in one-hour (60 minute) chunks. You could use a different time interval if you prefer; for example, if you set this value to 15 then you’ll get back a free/busy indicator for every 15 minutes as opposed to every hour. Of course, that also means you’ll get back 96 of these indicators for each day. (Why? Because there are 96 15-minute increments in the course of a single day.) In turn, that means you’ll have to rewrite this script to work with a day that has 96 indicators; the script we wrote assumes you have only 24 indicators per day.

See? We told you this was a little complicated.

Keep in mind, too, that when you call the FreeBusy method Outlook is going to pop up a dialog box that says, “Hey, someone is trying to access email addresses in your mailbox. Do you want to allow this?” You must grant access for a specified amount of time (between 1 and 10 minutes) and then click Yes. If you don’t grant access, or the dialog box times out, your script will fail.

Period.

As we noted, after we run the FreeBusy method we get back a value similar to this:

000000000100000000000000000000000010000000000000

So what are we going to do with this value? Well, after assigning the start date to a variable named dtmStartDate, we’re going to set up a For Next loop that looks like this:

For i = 1 to Len(strFreeBusyData) Step 24

As we determined earlier, each group of 24 digits in our value represents a single day; that means that, in order to extract the data for each day, we need to grab digits in blocks of 24. That’s exactly what we’re doing with the preceding line of code. In this For Next loop we’re starting with 1 and continuing on through the total number of characters in the string strFreeBusyData. (We can easily calculate the total number of characters by using the Len function.) To make it easier to grab characters in blocks of 24, we also added the parameter Step 24. This means our loop won’t run like this:

1
2
3
4
5

Instead, the loop will skip along by 24s, like this:

1
25
49
73
97

And so on.

Inside the loop, we first echo back the start date, then run this line of code:

strDay = Mid(strFreeBusyData, i, 24)

Here we’re using the Mid function to grab blocks of 24 characters from the string strFreeBusyData. The first time through the loop our counter variable i will be equal to 1; thus we’ll start with the first character in the string and grab the next 24 characters. The second time through the loop i will be equal to 25; thus the second time through the loop we’ll start with character 25 and grab the next 24 characters. Etc. etc.,

At this point we could simply display the data for day 1 like this:

000000000100000000000000

Or, if we wanted to get a tiny bit fancier, like this:

0
0
0
0
0
0
0
0
0
1

Either way would be easy, but we’re not sure how useful a bunch of 1s and 0s truly are. Therefore, we instead use this block of code to extract and format the first 12 digits in our day 1 value:

For x = 1 to 12
    If x = 1 Then
        strTime = "12 AM: "
    Else
        strTime = x - 1 & " AM: "
    End If 

    intFreeBusy = Mid(strDay, x, 1)
    If intFreeBusy = 1 Then
        strFreeBusy = "Busy"
    Else
        strFreeBusy = "Free"
    End If

    Wscript.Echo strTime & strFreeBusy
Next

What we’re doing here is setting up another For Next loop, this one looping through digits 1 through 12 in our day 1 data. For the first digit we assign the label 12 AM: ; that’s what this line of code does:

strTime = "12 AM: "

For the next 11 digits we take the value of the counter variable x and subtract 1 from it in order to create a label:

strTime = x - 1 & " AM: "

Why do we do that? Well, the second time through the loop (when x is equal to 2) we’re dealing with the 1:00 AM hour. Subtracting 1 from 2 gives us – hey, what do you know: 1 AM.

After creating the appropriate label we then use this line of code to extract the indicator value for a given time:

intFreeBusy = Mid(strDay, x, 1)

And because it’s too much trouble to remember if 0 equals free and 1 equals busy, or vice-versa, we use this block of code to convert this indicator to a string value:

If intFreeBusy = 1 Then
    strFreeBusy = "Busy"
Else
    strFreeBusy = "Free"
End If

Last, but surely not least, we echo back our time label and the string that tells us whether kenmyer is free or busy at that time:

Wscript.Echo strTime & strFreeBusy

The net result will be output that looks like this:

6/16/2008
12 AM: Free
1 AM: Free
2 AM: Free
3 AM: Free
4 AM: Free
5 AM: Free
6 AM: Free
7 AM: Free
8 AM: Free
9 AM: Busy
10 AM: Free
11 AM: Free

We then use a similar approach to format the times from 12:00 PM through 11:00 PM (digits 13 through 24). After echoing back these values, and a blank line, we then increment the value of dtmStartDate by 1:

dtmStartDate = dtmStartDate + 1

Why do we do that? Well, when you get back data using the FreeBusy method you get back all the free/busy data for a one-month period (beginning, of course, with the data specified). What if you only want to get back data for a two-day period (like we do here)? Well, an easy way to do this is to simply check and see if you’ve reached the desired end date; if so, you can then exit the For Next loop. This block of code checks to see if dtmStartDate is greater than June 17, 2008; if it is, then we exit the For Next loop and bring the script to an end:

If dtmStartDate > #6/17/2008# Then
    Exit For
End If

And that, at long last, is that.

By the way, and before anyone asks, we don’t know if we’ll be going to Orlando again next year. In fact, we don’t know if we’ll be going anywhere anytime soon; things are in a bit of a flux around here at the moment. However, if we get a chance to go back to Orlando in 2009 you can bet we’ll be there. And if the Scripting Editor gets a chance to compete against the Scripting Guy who writes this column you can bet she’ll get her butt kicked once again.

No matter what they’re competing in.

But that’s next year; right now this aluminum foil is beginning to itch a little. (We probably need to be basted again.) We’ll see everyone on Monday.

ScriptingGuy1

Follow