How to run an Excel macro after a new mail is received in Outlook?

Question

I want to run an Excel macro when email with "subject" is sent to my inbox. I set up run a script in Manage Rules & Alerts in Outlook. When I receive a mail with "subject" nothing happens to the macro.

Sub Test(mail As MailItem)

    Dim ExApp As Excel.Application
    On Error Resume Next
    Set ExApp = GetObject(, "Excel.Application")
    If Not ExApp Is Nothing Then
        ExApp.Run "'C:\Users\Desktop\Production v2.7.1.xlsm'!Main_function_Auto"
    End If
End Sub

On Error Resume Next bypasses errors and some think it means there are no errors. There must be an On Error GoTo 0 as soon as the reason for bypassing errors is over. Place it just after Set ExApp = GetObject(, "Excel.Application"). If there are errors you can see them. — niton

0m3r 0m3r · Accepted Answer · 2017-03-10T04:46:29

When your calling Excel sub procedure from Outlook, make sure to include the module name -

Example

Option Explicit
Public Sub Example(Item As Outlook.MailItem)
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook

    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open(Environ( _
                        "USERPROFILE") & "\Desktop\Production.xlsm")
    xlApp.Visible = True

'   // Run Macro in file
    xlBook.Application.Run "Module1.Main_function_Auto"

    Set xlApp = Nothing
    Set xlBook = Nothing
End Sub

How to run an Excel macro after a new mail is received in Outlook?

1 Answers