automatically Export todays outlook appointments to excel every day

Question

As the title suggests, is it possible to have a script pickup all todays outlook calendar appointments and exports them into excel to a specific location

And this would happen every day automatically

score 1 · Answer 1 · answered May 28 '21 at 12:53

This Macro gets all appoitments for the current day and writes it to an excel File: Needs to be run from Outlook-VBA.

Sub FindAppointments()
    Dim myNameSpace As Outlook.NameSpace
    Dim tdystart As Date
    Dim tdyend As Date
    Dim myAppointments As Outlook.Items
    Dim currentAppointment As Outlook.AppointmentItem
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlWorksheet As Object
    Dim i As Long
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.Workbooks.Add
    Set xlWorksheet = xlWorkbook.Worksheets(1)

    With xlWorksheet
        .Cells(1, 1).Value = "Subject"
        .Cells(1, 2).Value = "Body"
        .Cells(1, 3).Value = "Start"
        .Cells(1, 4).Value = "End"
    End With
    
    Set myNameSpace = Application.GetNamespace("MAPI")
    
    tdystart = VBA.Format(Now, "Short Date")
    tdyend = VBA.Format(Now + 1, "Short Date")
 
    Set myAppointments = myNameSpace.GetDefaultFolder(olFolderCalendar).Items
 
    myAppointments.Sort "[Start]"
 
    myAppointments.IncludeRecurrences = True
 
    Set currentAppointment = myAppointments.Find("[Start] >= """ & tdystart & """ and [Start] <= """ & tdyend & """")
    i = 2
    While TypeName(currentAppointment) <> "Nothing"
        Debug.Print currentAppointment.Subject
        xlWorksheet.Cells(i, 1).Value = currentAppointment.Subject
        xlWorksheet.Cells(i, 2).Value = currentAppointment.Body
        xlWorksheet.Cells(i, 3).Value = currentAppointment.Start
        xlWorksheet.Cells(i, 4).Value = currentAppointment.End
        i = i + 1

        Set currentAppointment = myAppointments.FindNext
    Wend
    xlWorksheet.Columns("A:D").EntireColumn.AutoFit
    xlWorkbook.SaveAs "C:\temp\test.xlsx" ' <------- Change this Path to the location you want to save the file to
    xlWorkbook.Close

End Sub

Works fine with Office Professional 2019 and VBA 7.1 – Helmut Darmüntzel Jun 26 '23 at 12:33 — Helmut Darmüntzel, Jun 26 '23 at 12:33

score 1 · Answer 2 · answered May 28 '21 at 12:54

Yes, it is possible to develop a VBA macro where you can get all appointment for a specific date and export them to an Excel spreadsheet. But Outlook doesn't provide anything for running your script on a daily basis, the code can be run only when Outlook is launched. So, you can run your code when Outlook is launched and create a timer for any further launches. By the timer tick event you may check whether the current day data was already exported or not. You may find the Outlook VBA - Run a code every half an hour thread helpful.

To get the appointment for a specific date you need to use the Find/FindNext or Restrict methods of the Items class. You can read more about these methods in the following articles:

automatically Export todays outlook appointments to excel every day

2 Answers2