Outlook VBA to download CSV attachment as Excel

losamfr17

Board Regular
Joined
Jun 10, 2016
Messages
149
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try this - note the trailing back slash I've added at the end of your folder path.

Code:
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.
 
Upvote 0
Amazing! It worked like a charm. Thank you!
Quick question: what's this part of the code doing?

Code:
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
 
Upvote 0
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.
 
Upvote 0
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:
Code:
ExcelWb.Worksheets(1).Range("F2").Value = "File Date"
ExcelWb.Worksheets(1).Range("F3").Value = Date
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top