Running an Excel macro from Outlook

Question

I am trying to set up an automated database in Excel which sends an email when a user has equipment signed out past the expected due date.

I also want to implement a feature where the user can reply to the email with a keyword 'extend' to extend their sign out date by 7 days.

I have the email sending correctly. I want to create a script in Outlook that links their reply to the Excel worksheet that is open. Both Outlook and Excel will be open at the same time on a dedicated PC. I don't want to open a new Excel file every time the Outlook macro runs.

Here is the Outlook macro:

Sub Exc_macro(Item As Outlook.MailItem)
Dim ExApp As Workbook
Dim gageID As String
Dim cap As String

If Left(Item.Body, 6) = "extend" Then   'Check for keyword in body
    gageID = Mid(Item.Subject, 23)  'Get equipment ID number
    Set ExApp = Excel.ActiveWorkbook
    Call ExApp.Application.Run("Module2.increase", gageID)
End If

End Sub

I want to pass the gageID argument to the Excel macro here:

Sub increase(gageID As String)
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))     
For Each cell In Rng
    If cell.Value = gageID Then
        cell.Offset(0, 9).Value = cell.Offset(0, 9).Value + 7
    End If
Next

End Sub

How do I reference the open Workbook in Outlook and subsequently run the Excel macro?

Tim Williams Tim Williams · Accepted Answer · 2019-10-01T21:34:22

Like this:

Sub Exc_macro(Item As Outlook.MailItem)

    Dim ExApp As Object
    Dim gageID As String
    Dim cap As String

    If Left(Item.Body, 6) = "extend" Then   'Check for keyword in body
        gageID = Mid(Item.Subject, 23)  'Get equipment ID number
        Set ExApp = GetObject(,"Excel.Application")
        ExApp.Run "'my ExcelWB.xlsm'!increase", gageID
    End If

End Sub

Make your increase Sub is in a regular code module

http://www.rondebruin.nl/win/s9/win001.htm

Running an Excel macro from Outlook

1 Answers