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.