Use PowerShell to Export Outlook Calendar Information

Use PowerShell to Export Outlook Calendar Information

  • Comments 10
  • Likes

Summary: Learn how to use Windows PowerShell to export Microsoft Outlook calendar information.

Hey, Scripting Guy! Question  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

Hey, Scripting Guy! Answer 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.

Image of command output

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Nice and useful script - but this function is almost begging to get some parameters. This is just a suggestion - I might do an implementation later unless Ed or some of the readers in here make the changes before me:

    Get-OutlookCalendar -Today

    Get-OutlookCalendar -FromDate [datetime]"5/1/2011"

    Get-OutlookCalendar -Filter "powershell"

    All of the above are filters that can be reproduced by piping to where-object (as in the blog text), but having them as parameters would make it somewhat more convenient to use.

    Another parameter that might be useful would be a "-outlookuser" parameter - something that could enable the script to look into another users calendar (given that the current user has sufficient permissions of cause). I frequently check my colleagues' calendars in Outlook - having a scriptable option to do that would be great. This can not be achieved just by filtering the output of the existing function - but if the script can communicate with the Exchange server it might be possible to get more calendars out?

    regards

    Jens / @jensgc

  • This code has a significant limitation: it doesn't return all occurrences of recurrent meetings.

  • nice script thanks for this one. but this is only working on the default caldendar. so my question is how can i get those statistics of other mailboxes or ressources where i have fullaccess rights?

  • Hi Dave and sba923,

    I have exactly the same requirement. 1) get all occurence of recurring meetings and 2) statistics of other mailboxes or ressources where i have fullaccess rights.

    Any solution / script you found out for that? Any reference would help.

    Thanks!

  • Hi Ed,

    Nice job... just what I needed.  However I have hit a mild performance snag when looking at other users calendars which are massive (25meg).  Is there any server side filtering that can be done (a little like Jens suggested i guess) which could speed the return of info back to the client?

    @Jens... did you manage to get anywhere with this????

    @ Dave and Vinay,

    I am sure you will have found a solution to the issue of getting access to other users calendars, but if not, or for anyone else stunbling on this thread, I have uploaded a little bit of code I cobbled together from various sites and a bit of gental poking by me.  There are a couple of functions in there - including a little bit of code for service manager - but I guess you can ignore that.  To be honest, its probably not the pretyest code, but it works.  there is also a couple of bits for creating new and updating existing appointments

    skydrive.live.com

    hope it helps sopmeone out there.

    x

  • To use server side filtering and processing you will need to use EWS.

    msdn.microsoft.com/.../bb204119(v=exchg.150).aspx

  • Thanks JRV...

    I will have a look into this.  Not sure how viable a solution it is, as the blog I am looking at looks to want to embed passwords in the script.

    I will do more research though as it looks intersting.

    Thanks again for the tip.

    Cheers

    Ed

  • To answer my own wonderings...

    Turns out you can use the windows identity - which you can turn into a variable without prompt or storing of password creds...

    Here is the link to anyone interested

    gsexdev.blogspot.co.uk/.../basic-powershell-script-to-show.html

    Looks like there is a fair bit of very useful info on his blogs.

  • Nice script, im looking for a server side version . basically i need to get all users calendar tjek if thay are attending lunch (are they in house or out) any ida?
    thnx
    martin

  • Nice script, im looking for a server side version . basically i need to get all users calendar tjek if thay are attending lunch (are they in house or out) any ida?
    thnx
    martin