Powershell Needful Things put that in your pipeline

28Oct/0926

Bulk export calendars from Exchange mailboxes

I have never really had the need for a script like this, so when our catering manager at the office logged a support call, requesting an export of all calendars for all of our meeting rooms, I had to investigate the possibilities. He basically needed this information in order to determine how busy the individual meeting rooms were during the last year.
Following a quick, unsuccessful, Internet probing for tools or scripts that could do this, my initial feeling was to say “No sorry, can’t be done, or if we do it, it was going to be a manual task.”
A manual task, which involves, granting access to the room mailbox, logging onto the mailbox using Outlook, and exporting the calendar data to Excel. Sounds easy, but doing that a hundred times is very unproductive and torturous to say the least.

I decided to attempt to script it, and the result is something I am both proud of and ashamed of at the same time, as I am convinced there must be a better way.

It’s a very rough method, which involves the following process:

  • Get a list of rooms from a text file (as it was emailed to me). You could use get-mailbox instead.
  • Add-mailbox permission to the current user
  • Create an Outlook profile
  • Logon to the profile
  • Export the Calendar to CSV
  • Remove-MailboxPermission

I could automate most of the above, but creating new profiles on demand is something I’ve never had to do, and frankly, I had no idea how to get around this problem. After speaking to some of the developers at work, who promised me some dotnet code which could do it (which I am still waiting for might I add :)), I decided to use PRF files.

I have used PRF files very successfully in the past, on Terminal server deployments to automatically setup Outlook profiles.

I downloaded the ORK and created a PRF which I used as a template for the script. The blank PRF is attached to this post to save you the time and effort of using ORK.

The script finds and replaces the UserName and HomeServer in the PRF, although any Exchange server should resolve you to your mailbox server. It then creates a PRF and starts Outlook with the /importPRF switch. Some extra information, for anyone wanting to actually deploy or use the PRF file; the %HomeServer% variable in the PRF does not work the same way %UserName% works, if you want use the PRF, you need to specify one of your mailbox servers instead.

While Outlook is open on that profile, the script attaches to Outlook using a COM object and downloads the calendar for the specified date.

The calendar fields can be customised to suit your needs. In my case we simply needed the Start and End date, the duration, and the Organizer.

The export data is saved and the PRF is removed, sadly the swarm of profiles will remain, and you have to manually remove them. You could remove them from HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles but I have not added that to the script.

I hope this can help you, if you ever get a freaky request like this.

The script and the PRF template can be downloaded from here:

Comments (26) Trackbacks (1)
  1. Thank you very much for this script. helped a lot.

    • One thing I could not get working was the add permission commands

    • Hi Neill,

      Thank you very much for you positive feedback, and I am glad the script helped you!

      Browse around the site, there are a couple of administration helper scripts like this one.

      Jean

      • Hi Jean,

        Thanks for your reply. I will have a look around.
        I am getting the following error when running your script. It works fine but I have to grant myself permissions to the mailbox manually. Any ideas?

        The term ‘Add-MailboxPermission’ is not recognized as the name of a cmdlet, function, script file, or operable program
        Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
        At C:\test\export-calendar.ps1:12 char:24
        + Add-MailboxPermission <<< $null
        + CategoryInfo : ObjectNotFound: (Add-MailboxPermission:String) [], CommandNotFoundException
        + FullyQualifiedErrorId : CommandNotFoundException

  2. I think that was the issue. I will test it again and let you know.

  3. Thank you so much Jeff. It worked like a charm!

  4. The links seem to be broken, do you have a direct link to them?

    Thanks,

    Brian

  5. Hi, I came across URL while searching for export resource calendar details in Outlook. However, the link to download the script isn’t availble. Has it purposely been removed? Can you email it to me?

    Thanks so much.

    Pamela

    • Hi Pamela,

      I have sent you a copy from my private email address.

      Jean

      • Please can you forward me the scripts to export calendars from exchange?
        actually the only thing i need to do is be able to export a specific calendar from an outlook profile from the actual date to CSV file (any format is ok but CSV is easier for me)
        Thanks

        • Hi Nicolas,

          The scripts should be available for download using the link at the bottom of the page. If you still have problems downloading, please let me know, and I will email the scripts to you.

          Jean

  6. Something does not work here.
    When Outlook is invoked with the /impotrPRF switch, the Outlook window pops up with an error “The profile name is invalid. Enter a valid profile name.” and after I hit OK, the profile selection dialog appears. In the end, no calender items are exported …

    Hagen

  7. Thanx for the script it really make my day!! Only 1 question i got a message in Outlook to give permissions to a program who is trying to enter Outlook(translated from Dutch). How do i solve this?
    Regards
    Rob

  8. Thanks very much for this script 🙂
    I’ve learned a lot from it.

    Just a quick note for anyone having problems starting outlook from the script:
    change the line
    & $olEXE /importPRF $newPRF /profile $room
    to
    & $olEXE “/importPRF” $newPRF “/profile” $room

    This was somehow an issue when I ran it, though I understand the others did not encounter this problem.

  9. Hi Jean,

    The script works and goes through the steps fine, but all the output files is my own calendar not the rooms Calendar. I can see outlook (2007) opens and closes and also I see the rooms added to Outlook but the export it just my own Calendar

    also I got the following error on the exchange power shell for each room
    WARNING: Can’t remove the access control entry on the object “XYZ” for account “XXX\ABCD” because the ACE doesn’t exist on the object.

    Can you please let me know what I am doing wrong.

    Thank you for your help,
    Mostafa Salama

  10. Thanks for your script, Jean Louw. Very helpful to export 20 meeting room calendars.

    I have problems switching mailboxes with the script:
    The permissions are added and removed fine,
    the Outlook profiles are generated fine,
    Outlook opens fine,
    killing outlook processes works fine,
    but my problem is that the first mailbox on the list is opened every single time by the script and the calendar export is therefore always made on the first mailbox.
    I don’t know how to force PowerShell to open Outlook with current mailbox profile XXX ?
    FYI: Running PowerShell 4.0, Outlook 2010 32-bit, Windows 2008 R2 Server

    Br Peter J

    • Hi there Peter,

      Sorry for the late reply, I have had some problems with access to my blog. Have you worked around this problem?

      Thanks

      Jean

  11. Thanks for your script, Jean Louw. Very helpful to my job: export 20 meeting room calendars.

    I have problems switching mailboxes in the script:
    The permissions are added and removed fine,
    the Outlook profiles are generated fine,
    Outlook opens fine,
    killing outlook processes works fine,
    but my problem is that the first mailbox on the list is opened every single time by the script and the calendar export is therefore always made on the first mailbox.
    I don’t know how to force PowerShell to open Outlook with current mailbox profile XXX ?
    FYI: Running PowerShell 4.0, Outlook 2010 32-bit, Windows 2008 R2 Server
    Br Peter J

    • Hi there Peter,

      Sorry for the late reply, I had some problems accessing the blog. Have you managed to work around your problem?

      Thanks

      Jean


Leave a comment