VBA Outlook calling Excel Macro and Wait till Macro is Done

Question

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

Answer 1

You could either

1. Port your Excel macro into Outlook and run it directly
2. 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

Answer 2

A really simple way, is by implementing a lock.

This code is a quick and dirty solution, by checking the existence of a file, in a predefined place.

in C:\Ask me question workflow.xlsm add this sub:

Sub WrapAskMeFlow()
   Dim tmpFile As String
   tmpFile = "C:\AskMeFlow.tmp"
   Open tmpFile for Output as #1
   Close #1
   AskMeFlow
   Kill tmpFile
End Sub

In your outlook macro add:

 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'!WrapAskMeFlow" 
 appExcel.DisplayAlerts = False 
 While Dir("C:\AskMeFlow.tmp")="":DoEvents:Wend
 While Dir("C:\AskMeFlow.tmp")<>"":DoEvents:Wend
 appExcel.ActiveWorkbook.Save 
 appExcel.Quit Set appExcel = Nothing 
 Set wkb = Nothing 
 End Sub