VBA Outlook calling Excel Macro and Wait till Macro is Done

excel, outlook, vba

Solution

You could either

- Port your Excel macro into Outlook and run it directly

- Use a flag to capture code completion

The code below uses a marker in A1 of the first sheet to catch the code being run (in the Excel portion). I have also tided your code (it was a mix of early and later binding)

outlook code

 Sub AskMeAlerts()
 Dim appExcel As Excel.Application
 Set appExcel = New Excel.Application
 With appExcel
     .DisplayAlerts = False
     .Workbooks.Open ("C:\TEMP\Ask me question workflow.xlsm")
     .Run "'Ask me question workflow.xlsm'!AskMeFlow"
     If .activeworkbook.sheets(1).[a1].Value = "Complete" Then
         MsgBox "Code has run"
         .activeworkbook.sheets(1).[a1].Value = vbNullString
         .activeworkbook.Save
        .DisplayAlerts = True
         .activeworkbook.Close
         appExcel.Quit
        Set appExcel = Nothing
     End If
 End With
End Sub

excel code

Sub AskMeFloW()
'do stuff
ThisWorkbook.Sheets(1).[a1] = "Complete"
End Sub

Problem

I am calling an Excel macro from an Outlook rule script. The process is: Get mail, run an Outlook rule which runs an Outlook script, open Excel from that script, run the Excel macro, close Excel. How can I validate in the Outlook rule script that the Excel macro is done, to save and close the application? ``` Sub AskMeAlerts() Dim appExcel As Excel.Application Dim wkb As Excel.Workbook Set appExcel = CreateObject("Excel.Application") appExcel.Workbooks.Open ("C:\Ask me question workflow.xlsm") appExcel.Visible = True appExcel.Run "'Ask me question workflow.xlsm'!AskMeFlow" appExcel.DisplayAlerts = False appExcel.ActiveWorkbook.Save appExcel.Quit Set appExcel = Nothing Set wkb = Nothing End Sub ```

Original source