?
Solved

Outlook 2003: Export Public Folder Calendar to Excel

Posted on 2010-01-12
6
Medium Priority
?
701 Views
Last Modified: 2012-05-08
Is there a way to export a public folder calendar to Excel?  The public folder calendars are not available when performing a File > Export within Outlook.  Thanks for any input!  
0
Comment
Question by:bsohn417
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
Berkson Wein earned 2000 total points
ID: 26298716
Easiest way that I know of is to create a new subfolder under your personal calendar.  Then copy all calendar items from the public calendar.  Select a list view that shows them all to do this.
THen you should be able to export the new subfolder.
Hope this helps.
 
0
 
LVL 76

Expert Comment

by:David Lee
ID: 26302548
Hi, bsohn417.

I can do this with a fairly simple bit of scripting.  Is that an option?
0
 
LVL 8

Author Comment

by:bsohn417
ID: 26306345
BlueDevilFan, sure.  If you have something handy I'd appreciate it.  
0
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 26308969
Is this a one time thing, or do you need to do this frequently?  If it's a one time job, a script is probably overkill...
 
0
 
LVL 76

Expert Comment

by:David Lee
ID: 26416193
@bsohn417 - Apologies, I lost track of this question.  Here's the code for doing this.  Follow these instructions to use it.

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

To export a folder

1.  Select the folder in the navigation window.
2.  Run the script.

Outlook 2000 - 2003 includes a security feature that prohibits scripts/programs from accessing Outlook fields that contain or may contain email addresses without the user's knowledge and permission.  This script is going to trigger that built-in security feature.  When it runs a dialog-box is going to pop up warning that an application is accessing your mailbox and asking for permission to continue.  Say "Yes".
Sub ExportItemsToCSV()
    Const MACRO_NAME = "Export Items to CSV"
    Dim objFSO As FileSystemObject, _
        objFile As TextStream, _
        olkFolder As Object, _
        olkItem As Object, _
        olkProp As Outlook.ItemProperty, _
        strHeader As String, _
        strValues As String, _
        strTemp As String, _
        bolHeaderWritten As Boolean
    On Error Resume Next
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Edit the file name and path on the next line.'
    Set objFile = objFSO.CreateTextFile("C:\eeTesting\Export2CSV.csv", True)
    Set olkFolder = Application.ActiveExplorer.CurrentFolder
    If olkFolder.DefaultItemType = olAppointmentItem Then
        olkFolder.Items.Sort "[Start]"
        olkFolder.Items.IncludeRecurrences = True
    End If
    For Each olkItem In olkFolder.Items
        strValues = ""
        For Each olkProp In olkItem.ItemProperties
            If (olkProp.Type <> olOutlookInternal) And (olkProp.Name <> "HTMLBody") Then
                If Not bolHeaderWritten Then
                    strHeader = strHeader & Chr(34) & olkProp.Name & Chr(34) & ","
                End If
                strTemp = olkProp.Value
                If Err.Number <> 0 Then
                    msgbox "Name: " & olkProp.Name & vbCrLf & "Type: " & olkProp.Type & vbCrLf & "Value: " & olkProp.Value, vbCritical + vbOKOnly, "Error Reading Property"
                End If
                Select Case olkProp.Name
                    Case "Body", "Subject"
                        strTemp = Replace(olkProp.Value, Chr(34), Chr(34) & Chr(34))
                End Select
                strValues = strValues & Chr(34) & strTemp & Chr(34) & ","
            End If
            DoEvents
        Next
        If Not bolHeaderWritten Then
            strHeader = Mid(strHeader, 1, Len(strHeader) - 1)
            objFile.WriteLine strHeader
            bolHeaderWritten = True
        End If
        strValues = Mid(strValues, 1, Len(strValues) - 1)
        objFile.WriteLine strValues
        DoEvents
    Next
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
    Set olkContact = Nothing
    msgbox "Export complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Open in new window

0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Office 365 has multiple features and services which are specially designed to help businesses to reduce their dependence on on-premises IT resources. It also offers great flexibility and enhanced security. But like any other data, Office 365 mailbo…
What is the biggest problem in managing an exchange environment today? It is the lack of backups, disaster recovery (DR) plan, testing of the DR plan or believing that it won’t happen to us.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

601 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