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

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
LVL 1
Mitch SwetskyBusiness AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
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
Mitch SwetskyBusiness AnalystAuthor Commented:
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
David LeeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mitch SwetskyBusiness AnalystAuthor Commented:
This is just what the dr ordered ,
Thank you
0
David LeeCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

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.