Solved

I am looking for VB code that I could add to a macro that would export a calendar item from my Inbox.

Posted on 2008-11-01
5
216 Views
Last Modified: 2013-11-25
I am getting calendars shared to me and copying them to my inbox.
Now I need to export them to an excel file using a macro so I can set it up to auto run monthly.
Any Ideas would be appreciated
0
Comment
Question by:Mswetsky
  • 3
  • 2
5 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 22861298
Hi, Mswetsky.

Are all the calendars exported to a single file, or do they go in separate files?  What fields of each appointment do you need to have exported?
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 22868122
I followed the steps to manually export the calendar and it exported a lot of fields. I need to track events and durations for a group of 8-10 people but am not sure exactly which fields I will be using yet.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 250 total points
ID: 22872948
Ok.  This is a script I wrote for another question that should work for what you've described.  You can edit the script to export whatever fields you ultimately decide you need.

Usage will be something like this

    ExportCalendar Session.GetDefaultFolder(olFolderCalendar).Folders("John's Calendar"), "C:\MyCalendar.csv", #10,1/2008#, #10/31/2008#

This would export all appointments in October 2008 from a calendar named "John's Calendar" that's under your calendar to a comma separated values file named MyCalendar.csv
Sub ExportCalendar(olkFolder As Outlook.MAPIFolder, strFileName As String, datStart As Date, datEnd As Date)

    Dim objFSO As Object, _

        objFile As Object, _

        olkItems As Outlook.Items, _

        olkItem As Outlook.AppointmentItem, _

        strBuffer As String, _

        datMyStart As Date, _

        datMyEnd As Date, _

        qt As String

    qt = Chr(34)

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set objFile = objFSO.CreateTextFile(strFileName, True)

    Set olkItems = olkFolder.Items

    olkItems.Sort "[Start]"

    olkItems.IncludeRecurrences = True

    datMyStart = VBA.Format(datStart, "Short Date")

    datMyEnd = VBA.Format(datEnd, "Short Date")

    Set olkItem = olkItems.Find("[Start] >= """ & datMyStart & """ and [Start] <= """ & datMyEnd & """")

    'Modify the header line to reflect all the values being written

    objFile.WriteLine "Subject,Start,End"

    While TypeName(olkItem) <> "Nothing"

        With olkItem

            'The first value has to be written with a command like this

            strBuffer = qt & .Subject & qt & ","

            'Each subsequent value has to be written with a line like this

            strBuffer = strBuffer & qt & .Start & qt & ","

            'The final value has to be written with a line like this

            strBuffer = strBuffer & qt & .End & qt

        End With

        objFile.WriteLine strBuffer

        strBuffer = ""

        Set olkItem = olkItems.FindNext

    Wend

    objFile.Close

    Set objFile = Nothing

    Set objFSO = Nothing

    Set olkItem = Nothing

    Set olkProp = Nothing

End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 31512402
This is just what the dr ordered ,
Thank you
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22877192
You're welcome.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to compare 2 PST files? 3 57
Outlook 2010 Calendar 5 41
Add a SafeSender to Exchange 6 24
VB6 and Outlook 2010 4 8
Resolve DNS query failed errors for Exchange
Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

758 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

17 Experts available now in Live!

Get 1:1 Help Now