VBA to open excel attachment in current Outlook email

[Mylarbi]

Hi helpers,
Currently I have a macro that works okay on Excel file attachments I manually open them in Outlook.
I want to add a new starting point to this macro such that
1. it opens the attached Excel file in the current Outlook email
2. it continues the remaining steps which work okay as mentioned.
Thanks

 

[igold]

Did you ever send someone an email with an attachment only to realize that after you hit the send button you had forgotten to add the attachment. That is the feeling I get when I read this post...

 

[Mylarbi]

Hi @igold,
To begin with, I have opened an Outlook email with an attached Excel file (not opened).
Then I open Excel workbook (say Workbook M) with the macro.
On execution, I need the macro to automatically go to Outlook and open the email attachment of the current email. This will become a second workbook (say ThisWorkbook).

 

[igold]

Currently I have a macro that works okay on Excel file attachments I manually open them in Outlook.
I want to add a new starting point to this macro

I don't see a macro....

 

[Mylarbi]

Okay the macro is

Sub PVTOPTS()
    Dim wb1, wb2 As Workbook
    Dim wsORIG, wsPVTS As Worksheet
' Settings
    Set wb1 = ActiveWorkbook
    Set wb2 = ThisWorkbook
    Set wsORIG = wb1.Worksheets("DATA")
    Set wsPVTS = wb2.Worksheets("PVTS")
' Get report
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    wsORIG.Cells.UnMerge
' New: delete top deck
    wsORIG.Range("F1:F40000").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Select
    Selection.Delete
    wsORIG.Range("A2").CurrentRegion.Offset(1) _
    .Resize(Selection.CurrentRegion.Rows.Count - 2).Copy wb2.Sheets("DATA").Range("B3")
'
    Application.CutCopyMode = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = True
' Refresh all
    wb2.Sheets("DATA").Calculate
    wb2.RefreshAll

 

[igold]

Have you tried searching this forum for an answer... Perhaps there is something here...

VBA Open Excel Sheet in email attachment daily

Hey! There seem to be quite a few topics on this, but I don't know why I cant get it working, I don't seem to understand something. https://www.mrexcel.com/board/threads/open-only-excel-files-from-outlook-folder.1194964/#post-5829313...

www.mrexcel.com

 

[igold]

Also, as a heads up, in your code in Post #5. Your variable declaration of this:

Dim wb1, wb2 As Workbook
Dim wsORIG, wsPVTS As Worksheet

is the same as declaring them like this:

Dim wb1 As Variant
Dim wb2 As Workbook
Dim wsORIG As Variant
Dim wsPVTS As Worksheet

it should read like this:

Dim wb1 As Workbook, wb2 As Workbook
Dim wsORIG As Worksheet, wsPVTS As Worksheet

Your code may work as you want, but that does not negate the fact the variables are not declared correctly.

 

[Mylarbi]

Have you tried searching this forum for an answer... Perhaps there is something here...

VBA Open Excel Sheet in email attachment daily

Hey! There seem to be quite a few topics on this, but I don't know why I cant get it working, I don't seem to understand something. https://www.mrexcel.com/board/threads/open-only-excel-files-from-outlook-folder.1194964/#post-5829313...

www.mrexcel.com

Yes I have searched but have not found one that meets my need. Some I found talk of saving the file, others execute the macro at the time the email is received. In my case, multiple variants of the same report type are received at midnight. So it will be impracticable to run all at the same time. That is why I want to activate the required email first before running the macro.

 

[Mylarbi]

it should read like this:

Dim wb1 As Workbook, wb2 As Workbook
Dim wsORIG As Worksheet, wsPVTS As Worksheet

Your code may work as you want, but that does not negate the fact the variables are not declared correctly.

Thanks for the correction. I'm a novice in vba.

 

[Mylarbi]

Meanwhile, @igold, my question has not been answered nor attempted. I thought you asked for the code I have in order to help?