How to run Excel Macro when a specific email arrives in Outlook...

scottleger4

New Member
Joined
Oct 3, 2016
Messages
39
Hello all,

I'm trying to do something that in my mind is relatively simple, but I'm not sure how to go about it and my googling hasn't helped much, other than it sounds like this needs to originate in Outlook. Each day I get a specific email with a specific subject that a data file has been updated. I'm looking for a way to open a specific excel file once that email hits my inbox. Once I can get the Excel file open, I think I can handle the VBA in Excel from there for the next steps. Something like this in outlook....

Public Sub UpdateDOS

'when email arrives with subject "DOS Pivot Updated"

Workbooks.Open Filename:= _
"C:\My DOS file.xlsm"

End Sub


Thanks in advance!
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

scottleger4

New Member
Joined
Oct 3, 2016
Messages
39
UPDATE: After a bit more research I found this bit of code...

Public Sub OpenMyExcelFile()
Dim File$
Dim Xl As Object ' Excel.Application
Dim Wb As Object ' Excel.Workbook
Dim Ws As Object ' Excel.Worksheet
Dim Rn As Object ' Excel.Range

File = "C:\My File.xlsm"

On Error Resume Next
Set Xl = GetObject(, "excel.application")
On Error GoTo 0
If Xl Is Nothing Then Set Xl = New Excel.Application
Set Wb = Xl.Workbooks.Open(File)
Set Ws = Wb.Sheets(1)
Ws.Activate
Set Rn = Ws.Range("a1")
Rn.Activate
Xl.Visible = True
End Sub

...but when I try to create a rule to run this when a specific email hits my inbox, it doesn't appear in the "run a script" list. Stealing from one of my other scripts, I modified the first line to...

Public Sub OpenMyExcelFile(MItem As Outlook.MailItem)

...and now it shows up in the box to select it, but it gets "deactivated" when I try to finish and apply with the error...

"One or more rules cannot be uploaded to Microsoft Exchange and has been deactivated. This could be because some of the parameters are not supported, or there is insufficient space to store all of your rules."

I'm sure its something simple, but my coding skills are pretty weak. Thanks again in advance!
 

scottleger4

New Member
Joined
Oct 3, 2016
Messages
39
After playing with this for several days I am still unable to get this to work. Any ideas?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,823
Messages
5,544,537
Members
410,618
Latest member
bigz
Top