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
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

734 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