{"id":55353,"date":"2008-06-14T02:08:00","date_gmt":"2008-06-14T02:08:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/06\/14\/hey-scripting-guy-how-can-i-retrieve-freebusy-time-from-microsoft-outlook\/"},"modified":"2008-06-14T02:08:00","modified_gmt":"2008-06-14T02:08:00","slug":"hey-scripting-guy-how-can-i-retrieve-freebusy-time-from-microsoft-outlook","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-retrieve-freebusy-time-from-microsoft-outlook\/","title":{"rendered":"Hey, Scripting Guy! How Can I Retrieve Free\/Busy Time From Microsoft Outlook?"},"content":{"rendered":"

\"Hey, <\/p>\n

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

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, JH. As we noted yesterday<\/b><\/a>, 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 VB<\/i>Scripter<\/i>s<\/i>) and then \u2013 wanting to take advantage of all the many tourist activities available in the great state of Florida \u2013 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\u2019d 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\u2019d also like to take a minute to report back some interesting facts and figures about the city of Orlando:<\/p>\n

At the hotel where the Scripting Guys stayed, you could \u2013 for just $100 \u2013 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 \u201cancient Balinese ritual.\u201d The question that immediately popped into our minds, of course, was this: didn\u2019t they use to have cannibals in Bali? (If they didn\u2019t, apparently they have them now.) Of course, if being rubbed with coconut oil and drenched in warm milk didn\u2019t appeal to you you could instead be coated with lime juice and then rubbed with ginger salt before being wrapped in aluminum foil.<\/p>\n

And yes, as near as we can tell pretty much everything you do in Orlando results in you being wrapped in aluminum foil. Interesting.<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. Despite the temptation to be basted in warm milk and then wrapped in foil, the Scripting Guys steered clear of this ancient Balinese ritual. They also skipped the room service which the hotel offered for both dogs and cats. (Although, to tell you the truth, some of the entrees available for dogs did<\/i> sound pretty good.)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

At Universal Studios there\u2019s a sign that reads \u201cValet Bus Parking.\u201d We assume that means valet and<\/i> bus parking, but we aren\u2019t sure; maybe Universal Studios really does<\/i> offer valet bus parking. Unfortunately, we couldn\u2019t verify that because we didn\u2019t bring our bus with us. Maybe next year.<\/p>\n

We know that a number of you have been wondering, \u201cIs the Scripting Editor good at anything<\/i>?\u201d (Obviously she isn\u2019t 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\u2019t<\/i> good at anything. Or at least she isn\u2019t anywhere near as good as the Scripting Guy who writes this column. For example, at Universal Studios we rode the Men in Black<\/b><\/a> 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\u2019s put it this way: the Scripting Guy who writes this column kicked the Scripting Editor\u2019s butt twice. And how many times did the Scripting Editor come out on top? <\/p>\n

You know what? We\u2019ll let you do the math on that one.<\/p>\n

Although the answer is this: zero<\/i>. Which also matches the number of wins the Scripting Editor had in miniature golf, despite her somewhat questionable sportsmanship on the 18th<\/sup> 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<\/i> lost.)<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. This is where the Scripting Editor will undoubtedly insert an Editor\u2019s note stating that her laser gun didn\u2019t seem to work right, and that the first time on Men in Black she had to sit in the middle seat and it\u2019s impossible<\/i> to shoot when you\u2019re in the middle seat, and if she hadn\u2019t gotten an 8 on that one hole in miniature golf she probably would have won, and \u2013 well, you get the idea. Are we saying that the Scripting Editor is a poor sport? Heavens no. We\u2019re simply saying that even though she lost fair and square, she\u2019s spending all her time making up excuses as to why<\/i> she lost. Does that sound like a poor sport to any of you?<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

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\u2019ve updated and enhanced both our VBScript to Windows PowerShell Conversion Guide<\/b><\/a> and our Windows PowerShell Owner\u2019s Manual<\/b><\/a>.) But you know what? That was then, and this is now. Which must mean it\u2019s time to answer JH\u2019s question about retrieving free\/busy time from Microsoft Outlook:<\/p>\n

Set objOutlook = CreateObject(\"Outlook.Application\")\nSet objNamespace = objOutlook.GetNamespace(\"MAPI\")\n\nSet objRecipient = objNameSpace.CreateRecipient(\"kenmyer\")\nstrFreeBusyData = objRecipient.FreeBusy(#6\/16\/2008#, 60)\n\ndtmStartDate = #6\/16\/2008#\n\nFor i = 1 to Len(strFreeBusyData) Step 24 \n    Wscript.Echo dtmStartDate\n    strDay = Mid(strFreeBusyData, i, 24)\n\n    For x = 1 to 12\n        If x = 1 Then\n            strTime = \"12 AM: \"\n        Else\n            strTime = x - 1 & \" AM: \"\n        End If \n\n        intFreeBusy = Mid(strDay, x, 1)\n        If intFreeBusy = 1 Then\n            strFreeBusy = \"Busy\"\n        Else\n            strFreeBusy = \"Free\"\n        End If\n\n        Wscript.Echo strTime & strFreeBusy\n    Next\n\n    For x = 13 to 24\n        If x = 13 Then\n            strTime = \"12 PM: \"\n        Else\n            strTime = x - 13 & \" PM: \"\n        End If \n\n        intFreeBusy = Mid(strDay, x, 1)\n        If intFreeBusy = 1 Then\n            strFreeBusy = \"Busy\"\n        Else\n            strFreeBusy = \"Free\"\n        End If\n\n        Wscript.Echo strTime & strFreeBusy\n    Next\n\n    Wscript.Echo\n    dtmStartDate = dtmStartDate + 1\n\n    If dtmStartDate > #6\/17\/2008# Then\n        Exit For\n    End If\nNext\n<\/pre>\n
What\u2019s that? Yes, this is<\/i> a complicated-looking little script, isn\u2019t it? That\u2019s due to the \u2026 interesting \u2026 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:<\/p>\n
000000000100000000000000000000000010000000000000<\/pre>\n
As it turns out, that\u2019s not<\/i> ancient Balinese; instead there\u2019s 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\u2019re 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\u2019ll see that the free\/busy time for the first 10 hours of day 1 look like this:<\/p>\n
12 AM: Free\n1 AM: Free\n2 AM: Free\n3 AM: Free\n4 AM: Free\n5 AM: Free\n6 AM: Free\n7 AM: Free\n8 AM: Free\n9 AM: Busy\n<\/pre>\n
What that means is that we can<\/i> figure out free\/busy time from the clump of data Outlook returns to us. The only question remaining is this: how<\/i> do we figure out free\/busy time from the clump of data Outlook returns to us?<\/p>\n
Well, that question and this<\/i> question: who the heck brings their cat with them in vacation?<\/p>\n
To begin with, we create an instance of the Outlook.Application<\/b> object and then use the GetNamespace<\/b> method to bind to the MAPI namespace; that gets us hooked up to Outlook. Once we\u2019re connected to Outlook we use this line of code to create a new Recipient<\/b> object for the person whose schedule we need to access (in this case a user with the email address kenmyer<\/i>):<\/p>\n
Set objRecipient = objNameSpace.CreateRecipient(\"kenmyer\")<\/pre>\n
We then use the FreeBusy<\/b> method to retrieve free\/busy information for kenmyer, starting on June 16, 2008:<\/p>\n
strFreeBusyData = objRecipient.FreeBusy(#6\/16\/2008#, 60)<\/pre>\n
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<\/b>) and the value 60<\/b>. What\u2019s the 60 for? This value indicates the time interval we want to use; in this case, we\u2019re 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\u2019ll get back a free\/busy indicator for every 15 minutes as opposed to every hour. Of course, that also means you\u2019ll 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\u2019ll 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.<\/p>\n
See? We told you this was a little complicated.<\/p>\n
Keep in mind, too, that when you call the FreeBusy method Outlook is going to pop up a dialog box that says, \u201cHey, someone is trying to access email addresses in your mailbox. Do you want to allow this?\u201d You must grant access for a specified amount of time (between 1 and 10 minutes) and then click Yes<\/b>. If you don\u2019t grant access, or the dialog box times out, your script will fail.<\/p>\n
Period.<\/p>\n
As we noted, after we run the FreeBusy method we get back a value similar to this:<\/p>\n
000000000100000000000000000000000010000000000000<\/pre>\n
So what are we going to do<\/i> with this value? Well, after assigning the start date to a variable named dtmStartDate, we\u2019re going to set up a For Next loop that looks like this:<\/p>\n
For i = 1 to Len(strFreeBusyData) Step 24<\/pre>\n
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\u2019s exactly what we\u2019re doing with the preceding line of code. In this For Next loop we\u2019re 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<\/b> function.) To make it easier to grab characters in blocks of 24, we also added the parameter Step 24<\/b>. This means our loop won\u2019t run like this:<\/p>\n
1\n2\n3\n4\n5\n<\/pre>\n
Instead, the loop will skip along by 24s, like this:<\/p>\n
1\n25\n49\n73\n97\n<\/pre>\n
And so on.<\/p>\n
Inside the loop, we first echo back the start date, then run this line of code:<\/p>\n
strDay = Mid(strFreeBusyData, i, 24)\n<\/pre>\n
Here we\u2019re using the Mid<\/b> 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\u2019ll 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\u2019ll start with character 25 and grab the next 24 characters. Etc. etc.,<\/p>\n
At this point we could simply display the data for day 1 like this:<\/p>\n
000000000100000000000000\n<\/pre>\n
Or, if we wanted to get a tiny bit fancier, like this:<\/p>\n
0\n0\n0\n0\n0\n0\n0\n0\n0\n1\n<\/pre>\n
Either way would be easy, but we\u2019re 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:<\/p>\n
For x = 1 to 12\n    If x = 1 Then\n        strTime = \"12 AM: \"\n    Else\n        strTime = x - 1 & \" AM: \"\n    End If \n\n    intFreeBusy = Mid(strDay, x, 1)\n    If intFreeBusy = 1 Then\n        strFreeBusy = \"Busy\"\n    Else\n        strFreeBusy = \"Free\"\n    End If\n\n    Wscript.Echo strTime & strFreeBusy\nNext\n<\/pre>\n
What we\u2019re 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: <\/b>; that\u2019s what this line of code does:<\/p>\n
strTime = \"12 AM: \"\n<\/pre>\n
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:<\/p>\n
strTime = x - 1 & \" AM: \"\n<\/pre>\n
Why do we do that? Well, the second time through the loop (when x is equal to 2) we\u2019re dealing with the 1:00 AM hour. Subtracting 1 from 2 gives us \u2013 hey, what do you know: 1 AM.<\/p>\n
After creating the appropriate label we then use this line of code to extract the indicator value for a given time:<\/p>\n
intFreeBusy = Mid(strDay, x, 1)\n<\/pre>\n
And because it\u2019s 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:<\/p>\n
If intFreeBusy = 1 Then\n    strFreeBusy = \"Busy\"\nElse\n    strFreeBusy = \"Free\"\nEnd If\n<\/pre>\n
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:<\/p>\n
Wscript.Echo strTime & strFreeBusy\n<\/pre>\n
The net result will be output that looks like this:<\/p>\n
6\/16\/2008\n12 AM: Free\n1 AM: Free\n2 AM: Free\n3 AM: Free\n4 AM: Free\n5 AM: Free\n6 AM: Free\n7 AM: Free\n8 AM: Free\n9 AM: Busy\n10 AM: Free\n11 AM: Free\n<\/pre>\n
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:<\/p>\n
dtmStartDate = dtmStartDate + 1\n<\/pre>\n
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\u2019ve 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:<\/p>\n
If dtmStartDate > #6\/17\/2008# Then\n    Exit For\nEnd If\n<\/pre>\n
And that, at long last, is that.<\/p>\n
By the way, and before anyone asks, we don\u2019t know if we\u2019ll be going to Orlando again next year. In fact, we don\u2019t know if we\u2019ll 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\u2019ll be there. And if the Scripting Editor gets a chance to compete against the Scripting Guy who writes this column you can bet she\u2019ll get her butt kicked once again.<\/p>\n
No matter what they\u2019re competing in. <\/p>\n\n<\/thead>\n\n\n
\n
Editor\u2019s Note:<\/b> Okay, time to set the record straight. Yes, the Scripting Editor did<\/i> get crushed on the Men in Black ride. As far as miniature golf, the Scripting Guy who writes this column neglected to mention who it was who hit 2 hole-in-ones. No, it wasn\u2019t the Scripting Guy who writes this column. (Take a guess on how many he had \u2026 or didn\u2019t have, as the case may be.) And ask the Scripting Guy who writes this column how does on Rock Band.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
But that\u2019s next year; right now this aluminum foil is beginning to itch a little. (We probably need to be basted again.) We\u2019ll see everyone on Monday.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u2013 wanting to take advantage […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[212,49,3,5],"class_list":["post-55353","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-outlook","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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 \u2013 wanting to take advantage […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55353","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=55353"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55353\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=55353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55353"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}