Outlook VBA to download CSV attachment as Excel

[losamfr17]

Hi,

I am using Outlook to automatically download a particular CSV attachment I receive by using Outlook Rules and the code below. This process works fine. However, I would like to download the file as an Excel file instead of its default CSV format. Could you please help me make that happen? How can I build onto the following code to make that happen?

Code:
Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem)

Dim oAttachment As Outlook.Attachment
Dim sSaveFolder As String


sSaveFolder = "C:\Users\MTaieb\Desktop\VBA Training"


For Each oAttachment In MItem.Attachments
oAttachment.SaveAsFile sSaveFolder & oAttachment.DisplayName
Next


End Sub
Thank you!

 

[John_w]

Try this - note the trailing back slash I've added at the end of your folder path.

Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem)

    Dim oAttachment As Outlook.Attachment
    Dim sSaveFolder As String
    Dim ExcelApp As Object, ExcelWb As Object
 
    On Error Resume Next
    Set ExcelApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set ExcelApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    'ExcelApp.Visible = True
    ExcelApp.DisplayAlerts = False 'in case .xlsx files already exist
    
    sSaveFolder = "C:\Users\MTaieb\Desktop\VBA Training\"
    
    For Each oAttachment In MItem.Attachments
        oAttachment.SaveAsFile sSaveFolder & oAttachment.DisplayName
        
        Set ExcelWb = ExcelApp.Workbooks.Open(sSaveFolder & oAttachment.DisplayName)
        ExcelWb.SaveAs sSaveFolder & Replace(oAttachment.DisplayName, ".csv", ".xlsx"), FileFormat:=51 '51=xlOpenXMLWorkbook
        ExcelWb.Close False
        Kill sSaveFolder & oAttachment.DisplayName 'delete .csv file
    
    Next

    Set ExcelApp = Nothing
    
End Sub

Always post VBA code inside CODE tags please.

 

[losamfr17]

Amazing! It worked like a charm. Thank you!
Quick question: what's this part of the code doing?

On Error Resume Next
    Set ExcelApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set ExcelApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    'ExcelApp.Visible = True
    ExcelApp.DisplayAlerts = False 'in case .xlsx files already exist

 

[losamfr17]

Also, where within your code can I add a line to edit the Excel workbook before it gets saved, for example to add ActiveWorkbook.Range("F2").Value = "File Date" ? I tried but failed. I'll keep trying.

Thank you.

 

[John_w]

That block of code sees if Excel is already running and if not creates a new instance.

To work with the Excel workbook, using the ExcelWb variable instead of ActiveWorkbook, like this:

ExcelWb.Worksheets(1).Range("F2").Value = "File Date"
ExcelWb.Worksheets(1).Range("F3").Value = Date

 

[losamfr17]

That's really awesome, thank you so much! VBA is inspiring.