I regularly export an outlook calendar from a shared account to a CSV file which is incorporated into an excel dashboard. I want to set up a macro that automatically carries out this export on a daily or weekly basis, so I started by trying to just get a code that carries out the export. I managed to get a VBA code working with the help of MS copilot which exports a CSV file, however it only seems to fully populate the calendar from 2012 for a couple of years, there are inputs from the last couple of years, but not the full daily calendar as it usually exports through the import/ export wizard. I notice that when it is done manually, you are prompted about date ranges and recurring instances being incompatible with CSV, is this something that cannot be done without the wizard compiling it?

I did edit it to only export the current year from the calendar, however the CSV file only had 5 rows populated, which when carried out through the export wizard has the full daily calendar and a subject on each day. Any help or guidance would be appreciated.

The code I currently have working is below:

Sub ExportSpecificCalendarToCSV()

    Dim olApp As Outlook.Application
    
    Dim olNamespace As Outlook.NameSpace
    
    Dim olFolder As Outlook.MAPIFolder
    
    Dim olItems As Outlook.Items
    
    Dim olItem As Object
    
    Dim csvFile As String
    
    Dim output As String
    
    Dim calendarName As String
    
    Dim emailAddress As String
    
    Dim currentYear As Integer
    
    
    
    ' Initialize Outlook application
    
    Set olApp = New Outlook.Application
    
    Set olNamespace = olApp.GetNamespace("MAPI")
    
    
    
    ' Specify the calendar name and email address
    
    calendarName = "On Call Engineer" ' Change this to the name of your calendar
    
    emailAddress = "*** Email address is removed for privacy ***,uk" ' Change this to your email address
    
    
    
    ' Get the specific calendar folder
    
    On Error Resume Next
    
    Set olFolder = olNamespace.Folders(emailAddress).Folders("Calendar").Folders(calendarName)
    
    On Error GoTo 0
    
    
    
    ' Check if the folder exists
    
    If olFolder Is Nothing Then
    
        MsgBox "Calendar not found!", vbExclamation, "Error"
    
        Exit Sub
    
    End If
    
    
    
    Set olItems = olFolder.Items
    
    
    
    ' Sort items by start date
    
    olItems.Sort "[Start]"
    
    
    
    ' Set the CSV file path
    
    csvFile = Environ("USERPROFILE") & "DesktopExam dates on shift OUTLOOK calendar.csv"
    
    
    
    ' Create CSV header
    
    output = "Subject,Start Date,Start Time,End Date,End Time,Location,Body" & vbCrLf
    
    
    
    ' Get the current year
    
    currentYear = Year(Date)
    
    
    
    ' Loop through calendar items
    
    For Each olItem In olItems
    
        If TypeName(olItem) = "AppointmentItem" Then
    
            If Year(olItem.Start) = currentYear Then
    
                output = output & Chr(34) & olItem.Subject & Chr(34) & "," & _
    
                         Format(olItem.Start, "yyyy-mm-dd") & "," & _
    
                         Format(olItem.Start, "hh:mm AM/PM") & "," & _
    
                         Format(olItem.End, "yyyy-mm-dd") & "," & _
    
                         Format(olItem.End, "hh:mm AM/PM") & "," & _
    
                         Chr(34) & olItem.Location & Chr(34) & "," & _
    
                         Chr(34) & Replace(olItem.Body, vbCrLf, " ") & Chr(34) & vbCrLf
    
            End If
    
        End If
    
    Next olItem
    
    
    
    ' Write to CSV file
    
    Dim fileNum As Integer
    
    fileNum = FreeFile
    
    Open csvFile For Output As #fileNum
    
    Print #fileNum, output
    
    Close #fileNum
    
    
    
    ' Notify user
    
    MsgBox "Calendar exported to " & csvFile, vbInformation, "Export Complete"

End Sub