[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1383
  • Last Modified:

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!
0
BITASCII
Asked:
BITASCII
  • 3
1 Solution
 
Chris BottomleyCommented:
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
 
BITASCIIAuthor 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
 
BITASCIIAuthor Commented:
Works for calendar shared using Public Folders.
0
 
BITASCIIAuthor 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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now