CODEWITHSUNDEEP
excelvbaoutlook
Nader
Nader

Reputation: 67

Open an Outlook Excel attachment, sent in a particular time range to a specific Outlook folder, using Excel VBA

I receive Outlook emails with the same filename daily (weekdays). I want to process the Outlook attachments that I received during the night, using VBA Excel.

I want two things:

  1. Open attachment from specific folder
  2. Delete the email (including Excel attachment) 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 copy wrong day's data).

I have written the below code. The code works but need to open the Excel workbook in the Outlook mail, 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

Upvotes: 2

Views: 10081

Answers (1)

RobertBaron
RobertBaron

Reputation: 2854

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.

Upvotes: 0

Related Questions