How to open an Outlook excel attachment using Excel VBA, sent in a particular time range to a specific Outlook folder?

Question

This is my first ever question. I want to major in computer science but in the meantime, I kindly ask you for some assistance using VBA Excel.

I receive Outlook emails with the same filename daily (weekdays); I want to program VBA Excel to open the Outlook attachments that I receive during the night before.

So, really two things:

Program VBA Excel to open attachment from specific folder
Delete the same email so that the next day, I have the same process (all files are sent using the same file name, so I don't want to confuse myself or copy and paste wrong day's data).

I have written the below code but need to open excel workbook from Outlook, not C drive :

Sub Test()

    Dim WB As Workbook      'designate variables
    Dim sSaveFileName As String


    sSaveFileName = Format(Now() - 1, "MM-DD-YYYY")  'save as received date, 
    yesterday

    ' not needed >>>>> 'Set WB = 
    Workbooks.Open("C:\Users\nader\OneDrive\Documents\openthisexcel.xlsx")


    Windows("openthisexcel.xlsx").Activate  'activate opened excel attachment
    Sheets("Sheet1").Select
    Range("A1:A50").Select
    Selection.Copy                          'select range and copy

    Windows("macroExcel.xlsm").Activate    'activate xlsm template (another 
    workbook)
    Sheets("Sheet1").Select
    Range("A1:A50").Select
    Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False                          'select range 
    and paste from one excel to another

    WB.SaveAs Filename:= _
        "C:\Users\nader\OneDrive\Documents\openthisexcel.xlsx" & 
    sSaveFileName & ".xlsx"        'save received excel using filename and 
    the date it was received in Outlook


    WB.Close  'close the excel attachment


End Sub

Please provide some assistance to open Excel attachment from folder and how to delete the email (including Excel attachment) in Outlook using VBA.

I read some Stack Overflow posts , but couldn't understand them (just a beginner here).

If I understand correctly, you received data in an Excel file attached to an Outlook email every day, and you want to automatically copy that data into some other known Excel file. Is that it? — RobertBaron
That's correct Robert! I receive data each day from a report with the same filename; just want to copy that data into new workbook; save as the filename and yesterday's date (or received date); the code works but just want to connect to Outlook folder to open file (everything else should be good). Oh I also want to delete email from folder once I finish so that next day I start fresh — Nader

RobertBaron RobertBaron · Accepted Answer · 2019-05-20T00:12:42

Look at Download attachment from Outlook and Open in Excel. This does what you want from Excel.

However, you have another option, that is, to write an Outlook macro instead. See Running Outlook Macros on a Schedule. With this, you would be able to run your macro automatically daily in Outlook (as long as Outlook is opened) to extract your data. The content of the macro would be essentially the same as the one in the first link.

How to open an Outlook excel attachment using Excel VBA, sent in a particular time range to a specific Outlook folder?

1 Answers