Use PowerShell to Export Outlook Calendar Information
Summary: Learn how to use Windows PowerShell to export Microsoft Outlook calendar information.
Hey, Scripting Guy! I was reading your Hey Scripting Guy! blogs that were talking about the 2011 Scripting Games. I was unable to compete this year, but that did not prevent me from watching the games. I bet you did not know that Windows PowerShell scripting was a spectator sport did you? Anyway, during the wrap-up blogs, you seemed to harp on things like “functions always need to return an object” and “avoid using Write-Host.” Now, I know that you did not write all of the blogs, but because the information appeared in the Hey Scripting Guy! blog, I am assuming that you at least read some of these things. Here is my question: If these are the sort of things that I am supposed to do in my scripts, why don’t you do them in your blogs? Put another way, here is a script you wrote to work with Microsoft Outlook calendar appointments, and it probably would have received a 2 in the 2011 Scripting Games.
—SH
Hello SH,
Microsoft Scripting Guy, Ed Wilson, is here. OK, SH you got me. I do not always floss my teeth every day, I do not always exercise an hour every day, I occasionally drink coffee, and have even been known to eat a pizza. I have tons of commitments, many of which are actually contradictory; I am swamped with meetings, email, and conference calls; not to mention that it takes a very long time to write each blog. When I decide to do special events like the 2011 Scripting Games, things get even more hectic. This event took nearly six months of preparation, and it involved dozens of hours of meetings with legal, hundreds of emails to sponsors, judges, and guest commentators. I had numerous conversations, in person and via email, with Joel Bennett (aka JayKul of PoshCode), and I even setup a tracking project on CodePlex.
While all this was going on, I still had to publish a blog seven days a week. Oh yeah, there was the little matter of making up 20 events and writing 20 articles to describe those events and writing sample solutions for each of those events—that took several months to do as well.
In short, I am a lot like you. I have to make choices as to where I spend my time. I could reduce the number of blogs that I write, and maybe only publish one a week. I could then spend the time to implement robust error handling, return objects from functions, implement comment-based Help, and the like. Or I can write and publish blogs seven days a week and write code that illustrates how to perform certain actions. Along the way, I can also illustrate how to perform robust error handling, return objects from functions, implement comment-based Help and the like—this is the approach I have chosen. In reality, all code does not need to be highly reusable, robust, and heavily commented.
Oh, by the way, a 2 in the 2011 Scripting Games is a good score. It means that the script works and meets the requirements—dude, there is nothing wrong with that! On the other hand, when one gets a 2 out of 5, it means that there is room for improvement.
The script I wrote last year, was designed to accomplish a single thing—it returned a week’s worth of meetings. By converting the script to a function that returns an object, you now have a lot of flexibility in that you can return any different combination of date ranges, and you can sort or group your meetings in any way that you wish.
In fact, you can use the power of Windows PowerShell to manipulate your Outlook appointments however you see fit. If you find a particular way of working with the data that you like, you can copy the function to your new script and use it there. You can store the function into its own Windows PowerShell script, in a particular location and dot source the script that contains the function into you new “master” script that creates the view you like.
In addition, you can put the function into a module and use it from there. By making the code reusable, it opens up myriad possibilities for building upon the work that I did in writing the code to retrieve Outlook appointments. In fact, you do not need to touch the Get-OutlookCalendar function again. All you need to do is decide how you want to use the information.
The complete Get-OutlookCalendar function is shown here. I have also copied it to the Scripting Guys Script Repository.
Get-OutlookCalendar function
Function Get-OutlookCalendar
{
<#
.Synopsis
This function returns appointment items from default Outlook profile
.Description
This function returns appointment items from default Outlook profile. It
uses the Outlook interop assembly to use the olFolderCalendar enumeration.
It creates a custom object consisting of Subject, Start, Duration, Location
for each appointment item.
.Example
Get-OutlookCalendar |
where-object { $_.start -gt [datetime]”5/10/2011″ -AND $_.start -lt `
[datetime]”5/17/2011″ } | sort-object Duration
Displays subject, start, duration and location for all appointments that
occur between 5/10/11 and 5/17/11 and sorts by duration of the appointment.
The sort is shortest appointment on top.
.Notes
NAME: Get-OutlookCalendar
AUTHOR: ed wilson, msft
LASTEDIT: 05/10/2011 08:36:42
KEYWORDS: Microsoft Outlook, Office
HSG: HSG-05-24-2011
.Link
Http://www.ScriptingGuys.com/blog
#Requires -Version 2.0
#>
Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null
$olFolders = “Microsoft.Office.Interop.Outlook.OlDefaultFolders” -as [type]
$outlook = new-object -comobject outlook.application
$namespace = $outlook.GetNameSpace(“MAPI”)
$folder = $namespace.getDefaultFolder($olFolders::olFolderCalendar)
$folder.items |
Select-Object -Property Subject, Start, Duration, Location
} #end function Get-OutlookCalendar
To use the Get-OutlookCalendar function, you need to first load it into memory. The easiest way to do this is to open the script that contains the Get-OutlookCalendar function and execute the code. Now, I say open the script that contains the Get-OutlookCalendar function, but the function could be contained in a module, in which case you would need to import the module by using the Import-Module cmdlet. After you have loaded the function, you can use “normal” Windows PowerShell cmdlets to manipulate the data.
The largest portion of the Get-OutlookCalendar function is the comment-based Help. It was also the easiest to create. This is because I used my Add-Help function that I added to my Windows PowerShell ISE profile. Refer to the Automatically Add Comment-Based Help to Your PowerShell Scripts Weekend Scripter blog for the Add-Help function and its use. The complete Help portion of the function is shown here.
<#
.Synopsis
This function returns appointment items from default Outlook profile
.Description
This function returns appointment items from default Outlook profile. It
uses the Outlook interop assembly to use the olFolderCalendar enumeration.
It creates a custom object consisting of Subject, Start, Duration, Location
for each appointment item.
.Example
Get-OutlookCalendar |
where-object { $_.start -gt [datetime]”5/10/2011″ -AND $_.start -lt `
[datetime]”5/17/2011″ } | sort-object Duration
Displays subject, start, duration and location for all appointments that
occur between 5/10/11 and 5/17/11 and sorts by duration of the appointment.
The sort is shortest appointment on top.
.Notes
NAME: Get-OutlookCalendar
AUTHOR: ed wilson, msft
LASTEDIT: 05/10/2011 08:36:42
KEYWORDS: Microsoft Outlook, Office
HSG: HSG-05-24-2011
.Link
Http://www.ScriptingGuys.com/blog
#Requires -Version 2.0
#>
Following the Help portion of the function, I use the Add-Type cmdlet to add the Outlook interop assembly. I then create the OlDefaultFolders type enumeration. This allows me to reference the calendar folder by using the enumeration OlFolderCalendar instead of using the numerical equivalent. I prefer to avoid the direct use of numbers without explanation. In the VBScript days, I would create a variable called OlFolderCalendar and assign the value 9 to it.
By the way, finding the numerical value of enum is often an exercise in futility. Whereas the enum names themselves are always documented on MSDN, the numerical value is often not documented. This is one reason I wrote my Get-EnumAndValues function that is discussed in the Weekend Scripter blog, Enumerations and Values. In fact, the use of enumerations is a very important subject, and it is something about which I have written several blogs. You should check them out.
After I create the calendar folder enumeration, I connect to the MAPI namespace and retrieve the calendar folder. This is all pretty standard stuff when working with Microsoft Outlook. These five lines of code are shown here.
Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null
$olFolders = “Microsoft.Office.Interop.Outlook.OlDefaultFolders” -as [type]
$outlook = new-object -comobject outlook.application
$namespace = $outlook.GetNameSpace(“MAPI”)
$folder = $namespace.getDefaultFolder($olFolders::olFolderCalendar)
The really cool thing is that the next two lines of code are the ones that convert the Windows PowerShell script from a single purpose script into a highly reusable function. What is really, really cool, is that it takes less code “to do the right thing” than it did to create a single purpose script. Here are the two lines of code:
$folder.items |
Select-Object -Property Subject, Start, Duration, Location
“But wait. I thought you said that functions should return objects,” you may ask.
“Yes that is correct,” I answer.
The really, really, really cool thing is that Select-Object returns a custom object. This is the easiest way to create an object.
Suppose that you decide you want to see a grouping of all the meetings for a particular week, arranged by the duration of the meetings. You want to see the shortest meetings on top and the longest meetings on the bottom of the list. You can use the following code to accomplish this task.
Get-OutlookCalendar |
where-object { $_.start -gt [datetime]”5/10/2011″ -AND $_.start -lt `
[datetime]”5/17/2011″ } | sort-object Duration
Perhaps, you are interested in where most of your appointments are occurring. This time, you do not want to limit the results to a particular time span. You want the most frequent locations to appear on the top of the list. To do this, you could use the following command.
Get-OutlookCalendar | Group-Object -Property Location |
Sort-Object count –Descending
By converting the old Windows PowerShell single-purpose script into a function, not only is the code simpler and easier to understand, it is also more flexible. An example of using the Get-OutlookCalendar function appears in the following image.
The complete Get-OutlookCalendar function is available in the Scripting Guys Script repository.
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
Light
Dark
0 comments