Create Excel File From Outlook VBA

CreateExcelFileFromOutlookVBA.vba

Sub CreateExcelFileFromOutlookVBA()
    
    ' Excel Application, workbook, and sheet object
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Object
    
    ' Filename
    Dim fileDoesExist As Boolean
    Dim FileName As String
    
    ' Create Excel Application
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    
    FileName = "AppendingExcel.xlsx"
    fileDoesExist = Dir("C:\Users\mvieth\Desktop\" & FileName) > ""
    
    ' Check for existing file
    If fileDoesExist Then
        ' Open Excel file
        Set xlBook = xlApp.Workbooks.Open("C:\Users\mvieth\Desktop\" & FileName)
        Set xlSheet = xlBook.Sheets(1)
    Else
        ' Add Excel file
        Set xlBook = xlApp.Workbooks.Add
        With xlBook
            .title = "All Sales"
            .Subject = "Sales"
            .SaveAs FileName:="C:\Users\mvieth\Desktop\" & FileName
        End With
        Set xlSheet = xlBook.Sheets(1)
    End If
    
    ' Do stuff with Excel workbook
    With xlApp
        With xlBook
            
            ' Add Excel VBA code to update workbook here
            
        End With
    End With
End Sub

To make sure the excel application was available from within outlook I had to go into the menus and make sure the reference was available.
tools>references>'Microsoft Excel 16.0 Obecjt Library' (or similar)

The following two line addition will close the workbook and excel application off at the end of the subroutine.

    ...
    End With
    xlBook.Close SaveChanges:=True
    xlApp.Quit
End Sub