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

Posted on 2011-10-23
Last Modified: 2012-05-12
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!
Question by:BITASCII
    LVL 59

    Accepted Solution

    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:


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

    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
    End Sub

    Open in new window


    Author Comment

    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.  

    Author Closing Comment

    Works for calendar shared using Public Folders.

    Author Comment

    For calendars shared using delegate rights, modify Chris's solution above with the GetSahredDefaultFolder method of Outllook Namespace, see

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now