Outlook Appointment export to CSV/Excel, including appointment item body text

Outlook 2010's export to file wizard (File|Options|Advanced|Export...) doesn't refernce appointment item body, would anyone have a VBA solution to export Outlook apppointment items from a shared calendar to CSV or Excel file, including appointment item body as string?  

To limit file size, only appointments who'se body text contains the string: "Catering: " need to be exported.  The shared Calendar is named "C3 Conference Center Calendar” in folder view. To be useful, the CSV file would include key fields from the appoitnment item, for example:  StartDate&Time (AppointmentItem.Start), EndDate&Time (AppointmentItem.End), Alldayevent (AppointmentItem.AllDayEvent), BillingInformation (AppointmentItem.BillingInformation), Categories (AppointmentItem.Categories), Description/Subject (AppointmentItem.Subject), Location(AppointmentItem.Location) + body as string.

We tried outlook's export wizard,  File | Options | Advanced | Export... but that does not reference the appointment item body.  Appreciate any help. Thanks!
BITASCIIAV Technology SupportAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:
If I follow the request then the following sub exportcon in an outlook module will do the job.  It opens a new copy of a workbook with the data for you to save as you wish.

It is possible that the path to the folder is incorrect but if so then outlook place the cursor on the folder and then in the VBE immediate window type:

?application.ActiveExplorer.CurrentFolder.FolderPath

and advise the result ... tweaking it will then a simple matter.

Chris
Sub exportCon()
Dim fldr As Object
Dim appt As Object
Dim xlApp As Object
Dim xlWB As Object
Dim xlSh As Object
Dim xlRow As Long

    Set fldr = Application.Session.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("C3 Conference Center Calendar")
    Set xlApp = CreateObject("excel.application")
    xlApp.Visible = True
    Set xlWB = xlApp.workbooks.Add
    Set xlSh = xlWB.sheets(1)
    xlSh.Range("a1:H1") = Array("Start Date & Time", "End Date & Time", "All Day?", "Billing Information", "Categories", "Subject", "Location", "Body")
    xlRow = 1
    For Each appt In fldr.Items
        If InStr(1, appt.Body, "catering:", vbTextCompare) > 0 Then
            xlRow = xlRow + 1
            xlSh.Range("A" & xlRow) = Format(appt.Start, "dd mmm yyyy, (hh:mm)")
            xlSh.Range("B" & xlRow) = Format(appt.End, "dd mmm yyyy, (hh:mm)")
            xlSh.Range("C" & xlRow) = CBool(appt.AllDayEvent)
            xlSh.Range("D" & xlRow) = appt.BillingInformation
            xlSh.Range("E" & xlRow) = appt.Categories
            xlSh.Range("F" & xlRow) = appt.Subject
            xlSh.Range("G" & xlRow) = appt.Location
            xlSh.Range("H" & xlRow) = appt.Body
        End If
    Next
    xlSh.Range("a1:h1").entirecolumn.Autofit
End Sub

Open in new window

0
 
BITASCIIAV Technology SupportAuthor Commented:
Chris, Spot on: wee folder path tweak needed - as you have predicted! The calendar is (unfortunately) not shared using Public Folders.

In Immediate Window ?Application.ActiveExplorer.CurrentFolder.FolderPath resolves to "\\C3 Conference Center \Calendar".  The "C3 Conference Center" appears to be an outlook account with its own default folders -- our  group apparently has full rights to that account's Calendar.  Using our Calendar View we can see, select and edit that account's Calendar.

What is the recommended method for setting the fldr object to return \\C3 Conference Center \Calendar, please?  I am looking into the Application.GetNamespace Method but this is unfamiliar territory and no success so far.  
0
 
BITASCIIAV Technology SupportAuthor Commented:
Works for calendar shared using Public Folders.
0
 
BITASCIIAV Technology SupportAuthor Commented:
For calendars shared using delegate rights, modify Chris's solution above with the GetSahredDefaultFolder method of Outllook Namespace, see http://msdn.microsoft.com/en-us/library/aa220116(office.11).aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.