Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outlook Calendar Export

    I currently have an Excel macro that takes an Outlook calander export file previously created and creates a report from it. I would like to automate things a little more by executing the Outlook export steps from within my Excel macro code. I tried recording my actions within Outlook, but the Macro came up pretty much empty as most of the activity is menu related. I poured over the Outlook VBA help file for hours with only little insight. The instruction sequence below is currently supply to the macro users. I would like to automate things up to step 18. Am I barking up the wrong tree? Should I be going after the appointments themselves by collecting property values (start time , end time, duration, etc) for each appointment and then incrementing an index to the next until the date range input is met?

    1.Open the MS Outlook application and select the calendar function from the folder list or Outlook bar.
    2.Select

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Outlook Calendar Export

    I don't use the calendar, but presumably some of these functions are in the Outlook object model. To have the "intellisense" for Outlook within an Excel procedure, add a reference (Tools|References) to the Microsoft Outlook 9.0 Object Library in your project. Hope this helps.

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Altoona, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Calendar Export

    For anyone interested, this little code snippet was the solution

    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNameSpace("MAPI")
    PeriodStart = Format(StartDate, "Short Date") & " 12:00 AM"
    PeriodEnd = Format(EndDate, "Short Date") & " 11:59 PM"
    Set myAppointments = myNameSpace.GetDefaultFolder(olFolderCalendar).Ite ms
    Set currentAppointment = myAppointments.Find("[Start] >= """ & PeriodStart & """ and [Start] <= """ & PeriodEnd & """")
    ' Set Timesheet_Lastrow index value to open row under header
    Timesheet_Lastrow = 4
    ' Loop through all appointment data gathered within the date range specified.
    While TypeName(currentAppointment) <> "Nothing"
    ApptAccount = Left(currentAppointment.Subject, 8)
    ' Extract the account number.
    ' Extract the subject without account number.
    ApptSubject = currentAppointment.Subject
    ApptSubjectLen = Len(ApptSubject) - 9
    ' Extract the Start
    ApptStartDate = currentAppointment.Start
    ' Extract the End Time.
    ApptEndTime = currentAppointment.End)' Increment row index.
    Timesheet_Lastrow = Timesheet_Lastrow + 1
    ' Increment Appointment
    Set currentAppointment = myAppointments.FindNext
    Wend

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •