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

[scottleger4]

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!

 

[scottleger4]

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]

After playing with this for several days I am still unable to get this to work. Any ideas?