Export shared outlook calendars to single excel spreadsheet

Hello.  I'm trying to export multiple shared office 2007 calendars to a single excel spreadsheet each night, listing "appointments" by time.  In addition to exporting the appointments from multiple shared calendars, I need to "pull" multiple pieces of information from the "subject" field from each appointment, and populate individual columns within the spreadsheet.  

Here's some background information on our calendar setup:

- There are multiple shared calendars (one calendar for each meeting room/location), named "RoomA", "RoomB", "RoomC", etc...
- Meetings in each room are scheduled as "appointments" in each calendar

- The subject field within each "appointment" contains multiple pieces of information -- a typical subject looks like this:
      
      (RoomA) Meeting Title - Hosted by John Doe - Loc1, Loc2, Loc3

      - (Room A) would be the meeting room name (same as the calendar name)
      - Meeting Title is the name of the meeting
      - Hosted by Jane Doe lists the Name of the person holding the meeting
      - Loc1, Loc2, Loc3 indicates any external "branches or participants" that we have to connect into the meeting
      **Each of these items contained in the appointment "Subject" field need to be split into individual columns in excel (not just one large "Subject" field with all of this information jammed into a single column)

- The location field in the Outlook appointment contains the name of the last person to create/modify a meeting
- The start time and end time fields are also used within the Outlook appointment to specify the start/end time of each meeting
- The "body" of the appointment contains a sentence or two of notes about each meeting

So, we are trying to find a way to export the pieces of information above (from multiple shared calendars) into a single Excel spreadsheet each day.  We would like the Spreadsheet to have the following columns:

StartTime    EndTime    Room    MeetingTitle    HostedBy    ExternalParticipants    CreatedBy    Notes


Other important notes:
- We use dashes (-) to seperate "fields" within the Subject field of each appointment, after the initial (Room1) portion in quotations, which specifies the room name
- We have a shared calendar for each meeting room, which we'd like to pull the above information from, and use to export into a single Excel spreadsheet (Say, the script would run on the "RoomA" calendar first, then "RoomB", "RoomC", and all appointments from each calendar would populate a single spreadsheet).  

I know this is a large request (at least to me it seems to be), but any assistance would be greatly appreciated.  I've done some basic vbscripts, but never anything involving exporting and sorting information from Outlook.  Thanks for any assistance.  

-Chris
damoncf1234Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
Hi, Chris.

I might be able to help with this.  Since you describe this as a daily process, where does the data go in the spreadsheet?  Will there be a separate sheet for each calendar or do the items all go in one sheet?  If the latter, does the process simply append the data each day or does it need to wipe the sheet out first before exporting the data?
damoncf1234Author Commented:
Thanks for the response.  We'd like the items from each calendar to go into one sheet.  We'd like the sheet to be erased each day (before the script is run for the "new" day).  Right now we do this all manually at midnight each night, which is a pain... :)

Thanks
David LeeCommented:
Seems simple enough.  What timeframe do you want to export for?  The entire calendar including appointments that occur in the past, all appointments for the current day, all appointments from today forward, or something else?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

David LeeCommented:
I have the basic script ready.  I'll finalize and post it as soon as I have the answer to my last question.
damoncf1234Author Commented:
BlueDevilFan,

Thanks for the quick response.  We're looking for the current day (24 hours) (basically, we'll run it each night, right after midnight, and it will "capture" all of the upcoming meetings in all of the conference rooms for the "present" day.  

Thanks again for your response.  
David LeeCommented:
You're welcome.  

Ok, this should do it.  Follow these instructions to add the code to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

For this to work

1.  Outlook must be running.
2.  The account that this runs under must have access to the shared calendars
2.  The spreadsheet must already exist

To run

1.  Run the macro RunExportCalendarsToExcel

Sub RunExportCalendarsToExcel()
    'Change the name of the conference room on the next line.  The name must match the name of the mailbox.'
    ExportCalendarToExcel "Room A", True
    'Repeat the next line for each subsequent conference room.  Be sure to change the name.'
    ExportCalendarToExcel "Room B"
End Sub

Sub ExportCalendarToExcel(strCalendarName As String, Optional bolClearWorksheet As Boolean)
    Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem, olkRecipient As Outlook.Recipient
    Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long
    Dim arrTitle As Variant
    
    'Launch Excel and open the spreadsheet'
    Set excApp = CreateObject("Excel.Application")
    excApp.Visible = True
    'Change the name and path of the spreadsheet on the next line'
    Set excWkb = excApp.Workbooks.Open("C:\eeTesting\Chris.xlsx")
    Set excSht = excWkb.Worksheets(1)
    If bolClearWorksheet Then
        Set excRng = excSht.Range("A1").CurrentRegion
        lngRow = excRng.Rows.count
        excApp.Rows(2 & ":" & lngRow).Delete
        lngRow = 2
    Else
        lngRow = excSht.UsedRange.Rows.count + 1
    End If
    
    'Connect to and process the shared calendar'
    Set olkRecipient = Session.CreateRecipient(strCalendarName)
    Set olkFolder = Session.GetSharedDefaultFolder(olkRecipient, olFolderCalendar)
    Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(Date & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(Date & " 11:59pm", "ddddd h:nn AMPM") & "'")
    olkItems.Sort "[Start]"
    olkItems.IncludeRecurrences = True
    For Each olkAppt In olkItems
        arrTitle = Split(olkAppt.Subject, "-")
        excSht.Cells(lngRow, 1) = olkAppt.Start
        excSht.Cells(lngRow, 2) = olkAppt.End
        excSht.Cells(lngRow, 3) = strCalendarName
        excSht.Cells(lngRow, 4) = Trim(arrTitle(0))
        excSht.Cells(lngRow, 5) = Trim(arrTitle(1))
        excSht.Cells(lngRow, 6) = Trim(arrTitle(2))
        excSht.Cells(lngRow, 7) = olkAppt.Organizer
        excSht.Cells(lngRow, 8) = olkAppt.Body
        lngRow = lngRow + 1
    Next
    
    'Save the spreadsheet and exit Excel'
    Set excRng = Nothing
    Set excSht = Nothing
    excWkb.Save
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    
    'Clean-up the Outlook objects'
    Set olkFolder = Nothing
    Set olkItems = Nothing
    Set olkAppt = Nothing
End Sub

Open in new window

damoncf1234Author Commented:
BlueDevilFan,

Sorry for the delay; Each time I try to run the script, I get the following error:

Run-time error '-2147352567 (80020009)':
Outlook does not recognize one or more names.

I click the "debug" button, and the following line is highlighted:
Set olkFolder = Session.GetSharedDefaultFolder(olkRecipient, olFolderCalendar)

If I mouse-over the highlighted line, I can see that it "resolves" olkFolder to the first calendar name.  

I've tried changing the way I specify the calendar name(s) up top, and commented-out all but one of the calendars.  I have one question -- I mentioned that these are "shared calendars" -- they are located in the public folders -- they're not individual calendars shared from user mailboxes or anything... Does this make a difference?

Thanks

-Chris

David LeeCommented:
"they are located in the public folders -- they're not individual calendars shared from user mailboxes or anything... Does this make a difference?"

Yes, that makes a huge difference.  I had interpreted "shared" to mean a mailbox calendar that had been shared.  I'll adjust the code and repost.
David LeeCommented:
Here's the modified code.  This version assumes that the calendars are all located at the root of the public folders.  IN other words

+ Public Folders
   + All Public Folders
      + Room A
      + Room B

If that's not correct, then I need to know if the calendars are all at the same level and the path to the parent folder if they are at the same level.  If they are at different levels, then I'll need to make a change to the code.
Sub RunExportCalendarsToExcel()
    'Change the name of the conference room on the next line.  The name must match the name of the mailbox.'
    ExportCalendarToExcel "Room A", True
    'Repeat the next line for each subsequent conference room.  Be sure to change the name.'
    ExportCalendarToExcel "Room B"
End Sub

Sub ExportCalendarToExcel(strCalendarName As String, Optional bolClearWorksheet As Boolean)
    Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem
    Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long
    Dim arrTitle As Variant
    
    'Launch Excel and open the spreadsheet'
    Set excApp = CreateObject("Excel.Application")
    excApp.Visible = True
    'Change the name and path of the spreadsheet on the next line'
    Set excWkb = excApp.Workbooks.Open("C:\eeTesting\Chris.xlsx")
    Set excSht = excWkb.Worksheets(1)
    If bolClearWorksheet Then
        Set excRng = excSht.Range("A1").CurrentRegion
        lngRow = excRng.Rows.count
        excApp.Rows(2 & ":" & lngRow).Delete
        lngRow = 2
    Else
        lngRow = excSht.UsedRange.Rows.count + 1
    End If
    
    'Connect to and process the shared calendar'
    Set olkFolder = Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders(strCalendarName)
    Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(Date & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(Date & " 11:59pm", "ddddd h:nn AMPM") & "'")
    olkItems.Sort "[Start]"
    olkItems.IncludeRecurrences = True
    For Each olkAppt In olkItems
        arrTitle = Split(olkAppt.Subject, "-")
        excSht.Cells(lngRow, 1) = olkAppt.Start
        excSht.Cells(lngRow, 2) = olkAppt.End
        excSht.Cells(lngRow, 3) = strCalendarName
        excSht.Cells(lngRow, 4) = Trim(arrTitle(0))
        excSht.Cells(lngRow, 5) = Trim(arrTitle(1))
        excSht.Cells(lngRow, 6) = Trim(arrTitle(2))
        excSht.Cells(lngRow, 7) = olkAppt.Organizer
        excSht.Cells(lngRow, 8) = olkAppt.Body
        lngRow = lngRow + 1
    Next
    
    'Save the spreadsheet and exit Excel'
    Set excRng = Nothing
    Set excSht = Nothing
    excWkb.Save
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    
    'Clean-up the Outlook objects'
    Set olkFolder = Nothing
    Set olkItems = Nothing
    Set olkAppt = Nothing
End Sub

Open in new window

damoncf1234Author Commented:
BlueDevilFan,

The individual calendars are all in the same folder in the following location:
"All Public Folders\Conference Rooms\"

Thanks
-Chris
David LeeCommented:
Chris,

Modify line #29 from

    Set olkFolder = Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders(strCalendarName)

to

    Set olkFolder = Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Conference Rooms").Folders(strCalendarName)
damoncf1234Author Commented:
BlueDevilFan,

Thanks, that fixed the issue.  Is there any way to add some type of "pop-up" that would allow a user to specify a specific date to run this on (for example, 4/27/10) instead of just the current date (I know I didn't ask this originally).

Also, is there a way to add a "static" heading to the top row of each column in the excel spreadsheet, to identify what each column contains?

Thanks,

Chris
David LeeCommented:
Chris,

I added the ability to select a date.  Use the revised code below.

I can add code to write the heading, but you'd said that the process is writing to the same spreadsheet each time so I assumed that the headings would already be in place.  Let me know how you'd like to proceed.
Sub RunExportCalendarsToExcel()
    'Change the name of the conference room on the next line.  The name must match the name of the mailbox.'
    ExportCalendarToExcel "Room A", True
    'Repeat the next line for each subsequent conference room.  Be sure to change the name.'
    ExportCalendarToExcel "Room B"
End Sub

Sub ExportCalendarToExcel(strCalendarName As String, Optional bolClearWorksheet As Boolean)
    Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem
    Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long
    Dim arrTitle As Variant, datDate As Date
    
    'Launch Excel and open the spreadsheet'
    Set excApp = CreateObject("Excel.Application")
    excApp.Visible = True
    'Change the name and path of the spreadsheet on the next line'
    Set excWkb = excApp.Workbooks.Open("C:\eeTesting\Chris.xlsx")
    Set excSht = excWkb.Worksheets(1)
    If bolClearWorksheet Then
        Set excRng = excSht.Range("A1").CurrentRegion
        lngRow = excRng.Rows.count
        excApp.Rows(2 & ":" & lngRow).Delete
        lngRow = 2
    Else
        lngRow = excSht.UsedRange.Rows.count + 1
    End If
    
    'Connect to and process the shared calendar'
    Set olkFolder = Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Conference Rooms").Folders(strCalendarName)
    datDate = InputBox("Enter the date you want to export for.", "Export Calendars to Excel", Date)
    Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(datDate & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datDate & " 11:59pm", "ddddd h:nn AMPM") & "'")
    olkItems.Sort "[Start]"
    olkItems.IncludeRecurrences = True
    For Each olkAppt In olkItems
        arrTitle = Split(olkAppt.Subject, "-")
        excSht.Cells(lngRow, 1) = olkAppt.Start
        excSht.Cells(lngRow, 2) = olkAppt.End
        excSht.Cells(lngRow, 3) = strCalendarName
        excSht.Cells(lngRow, 4) = Trim(arrTitle(0))
        excSht.Cells(lngRow, 5) = Trim(arrTitle(1))
        excSht.Cells(lngRow, 6) = Trim(arrTitle(2))
        excSht.Cells(lngRow, 7) = olkAppt.Organizer
        excSht.Cells(lngRow, 8) = olkAppt.Body
        lngRow = lngRow + 1
    Next
    
    'Save the spreadsheet and exit Excel'
    Set excRng = Nothing
    Set excSht = Nothing
    excWkb.Save
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    
    'Clean-up the Outlook objects'
    Set olkFolder = Nothing
    Set olkItems = Nothing
    Set olkAppt = Nothing
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
damoncf1234Author Commented:
BlueDevilFan,

Yes, we'll be writing to the same spreadsheet each day.  I assumed that part of your script would be deleting the spreadsheet (and removing all of the previous day's meetings), which would take out everything, including the first row with the headings.  

I'll try running your latest version tomorrow morning, and specifying another date.  

Thanks,
-Chris  
damoncf1234Author Commented:
BlueDevilFan,

Sorry for the delay, I had to go on a trip to a remote location, and didn't have access to a particulare network to test your solution.  Your last solution worked, and performed the functions that we were looking for.  I noticed that some of the people that input meetings on some calendars do not follow our standards, which causes some errors to come up as the macro is run in Outlook.  I put in an "on error resume next" in the code, which lets it keep it running if some of the fields are missing.  

Is there a way to run this as a "stand alone" vbscript, instead of having to launch it within Outlook?  

Thanks for your help.  

-Chris  
David LeeCommented:
Hi, Chris.

No problem on the delay.  

To run as a standalone script the code has to be converted from VBA to VBScript.  Not too difficult to do.  The code below should get the job done.
'Change the name of the conference room on the next line.  The name must match the name of the mailbox.'
ExportCalendarToExcel "Room A", True
'Repeat the next line for each subsequent conference room.  Be sure to change the name.'
ExportCalendarToExcel "Room B", False
WScript.Quit

Sub ExportCalendarToExcel(strCalendarName, bolClearWorksheet)
    Const olPublicFoldersAllPublicFolders = 18
    Dim olkApp, olkSes, olkFolder, olkItems, olkAppt 
    Dim excApp, excWkb, excSht, excRng, lngRow
    Dim arrTitle, datDate

    'Connect to Outlook'
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    'Change the profile name on the next line'
    olkSes.Logon "Outlook"
    
    'Launch Excel and open the spreadsheet'
    Set excApp = CreateObject("Excel.Application")
    excApp.Visible = True
    'Change the name and path of the spreadsheet on the next line'
    Set excWkb = excApp.Workbooks.Open("C:\eeTesting\Chris.xlsx")
    Set excSht = excWkb.Worksheets(1)
    If bolClearWorksheet Then
        Set excRng = excSht.Range("A1").CurrentRegion
        lngRow = excRng.Rows.count
        excApp.Rows(2 & ":" & lngRow).Delete
        lngRow = 2
    Else
        lngRow = excSht.UsedRange.Rows.count + 1
    End If
    
    'Connect to and process the shared calendar'
    Set olkFolder = olkSes.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Conference Rooms").Folders(strCalendarName)
    datDate = InputBox("Enter the date you want to export for.", "Export Calendars to Excel", Date)
    Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(datDate & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datDate & " 11:59pm", "ddddd h:nn AMPM") & "'")
    olkItems.Sort "[Start]"
    olkItems.IncludeRecurrences = True
    For Each olkAppt In olkItems
        arrTitle = Split(olkAppt.Subject, "-")
        excSht.Cells(lngRow, 1) = olkAppt.Start
        excSht.Cells(lngRow, 2) = olkAppt.End
        excSht.Cells(lngRow, 3) = strCalendarName
        excSht.Cells(lngRow, 4) = Trim(arrTitle(0))
        excSht.Cells(lngRow, 5) = Trim(arrTitle(1))
        excSht.Cells(lngRow, 6) = Trim(arrTitle(2))
        excSht.Cells(lngRow, 7) = olkAppt.Organizer
        excSht.Cells(lngRow, 8) = olkAppt.Body
        lngRow = lngRow + 1
    Next
    
    'Save the spreadsheet and exit Excel'
    Set excRng = Nothing
    Set excSht = Nothing
    excWkb.Save
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    
    'Clean-up the Outlook objects'
    Set olkFolder = Nothing
    Set olkItems = Nothing
    Set olkAppt = Nothing
    olkSes.Logoff
    Set olkSes = Nothing
    Set olkApp = Nothing
End Sub

Open in new window

Sanda PeirisCommented:
Hi David,

Very useful article. i have a question. when i run the macro, i'm getting 2012 dates. any reason for that? i used your modification on the script to select the date. but still i'm getting old date like 2012
Sanda PeirisCommented:
this is the code i use

Sub RunExportCalendarsToExcel()
    'Change the name of the conference room on the next line.  The name must match the name of the mailbox.'
    ExportCalendarToExcel "SG-Share Calendar", True
End Sub

Sub ExportCalendarToExcel(strCalendarName As String, Optional bolClearWorksheet As Boolean)
    Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem, olkRecipient As Outlook.Recipient
    Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long
    Dim arrTitle As Variant
   
    'Launch Excel and open the spreadsheet'
    Set excApp = CreateObject("Excel.Application")
    excApp.Visible = True
    'Change the name and path of the spreadsheet on the next line'
    Set excWkb = excApp.Workbooks.Open("d:\Sanda.xlsx")
    Set excSht = excWkb.Worksheets(1)
    If bolClearWorksheet Then
        Set excRng = excSht.Range("A1").CurrentRegion
        lngRow = excRng.Rows.Count
        excApp.Rows(2 & ":" & lngRow).Delete
        lngRow = 2
    Else
        lngRow = excSht.UsedRange.Rows.Count + 1
    End If
   
   'Connect to and process the shared calendar'
    Set olkRecipient = Session.CreateRecipient(strCalendarName)
    Set olkFolder = Session.GetSharedDefaultFolder(olkRecipient, olFolderCalendar)
    datDate = InputBox("Enter the date you want to export for.", "Export Calendars to Excel", Date)
    Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(datDate & " 0:01am", "ddddd h:nn AMPM") & "' AND [Start] < '" & Format(datDate & " 11:59pm", "ddddd h:nn AMPM") & "'")
    olkItems.Sort "[Start]"
    olkItems.IncludeRecurrences = True
    For Each olkAppt In olkItems
        arrTitle = Split(olkAppt.Subject, "-")
        excSht.Cells(lngRow, 1) = olkAppt.Start
        excSht.Cells(lngRow, 2) = olkAppt.End
        excSht.Cells(lngRow, 3) = strCalendarName
        excSht.Cells(lngRow, 4) = olkAppt.Organizer
        excSht.Cells(lngRow, 5) = olkAppt.Body
        lngRow = lngRow + 1
    Next
   
    'Save the spreadsheet and exit Excel'
    Set excRng = Nothing
    Set excSht = Nothing
    excWkb.Save
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
   
    'Clean-up the Outlook objects'
    Set olkFolder = Nothing
    Set olkItems = Nothing
    Set olkAppt = Nothing
End Sub



attached the result
Sanda.xlsx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.