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

Answer 3

Option 1

The simplest option in your specific case would be to build the save and quit commands into the Excel macro rather than the Outlook one.

That is, you could amend your Outlook code to:

 Sub AskMeAlerts()
 Dim appExcel As Excel.Application 
 Dim wkb As Excel.Workbook 'Is this declaration necessary for some code elsewhere? You do not use this variable and I would recommend removing the declaration.
      Set appExcel = CreateObject("Excel.Application")
      With appExcel 
           .Workbooks.Open ("C:\Ask me question workflow.xlsm") 
           .Visible = True 
           .Run "'Ask me question workflow.xlsm'!AskMeFlow" 
      'No need to explicitly set alert values or save workbook as Excel macro will handle this.
      End With 
      Set appExcel = Nothing 
      Set wkb = Nothing 'Again, is this necessary?
 End Sub

You could then add the following to the end of the "Ask me question workflow.xlsm" file:

 Application.DisplayAlerts = False
 ThisWorkbook.Close SaveChanges:=True
 Application.Quit

Note: if you will also be running the macro manually or in other use cases where you do not want the workbook to save, close, and quit, you could consider adding an input variable to the AskMeFlow macro that defaults to False but is set to True by Outlook. I think this is slightly outside the scope of this answer, so I will not elaborate further, but let me know if you are interested in this option.

Option 2

Redacted. See Uri's solution; the improvements I suggested do not fundamentally alter that solution.

Option 3

Depending on the nature of the Excel code, you could turn it into a function and capture the output variable. Something like the below:

Sub AskMeAlerts()
 Dim appExcel As Excel.Application 
 Dim wkb As Excel.Workbook 
 Dim StrOutput as string
 StrOutput = "Excel macro did not complete."
 Set appExcel = CreateObject("Excel.Application") 
 appExcel.Workbooks.Open ("C:\Ask me question workflow.xlsm") 
 appExcel.Visible = True 
 StrOutput = appExcel.Run "'Ask me question workflow.xlsm'!AskMeFlow" 
 MsgBox StrOutput
 appExcel.DisplayAlerts = False 
 appExcel.ActiveWorkbook.Save 
 appExcel.Quit Set appExcel = Nothing 
 Set wkb = Nothing 
End Sub

You would then change AskMeFlow to a function and add the following code:

Function AskMeFlow() as String
AskMeFlow = "Uncaught error executing Excel code."
'Your code here
AskMeFlow = "Excel code completed successfully!"
End Function

Answer 4

If Sub AskMeFlow do calculations without user intervention, I suppose you can just simply track Excel's CalculationState.

Sub AskMeAlerts()
    With CreateObject("Excel.Application")
        .Workbooks.Open ("C:\Ask me question workflow.xlsm")
        .Visible = True

        ' Ensure Autocalculation is on
        .Calculation = -4105 ' xlCalculationAutomatic

        .DisplayAlerts = False
        .Run "'Ask me question workflow.xlsm'!AskMeFlow"

        ' Wait until calculation is done
        Do Until .CalculationState = 0   ' xlDone
            DoEvents
        Loop

        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
        .Quit
    End With
End Sub

It would be even better if the AskMeFlow is auto executed in Workbook_Open event (within "ThisWorkbook" module).

Answer 5

The Excel macro should finish by closing all workbooks, and Outlook would wait while there are still workbooks open.

In Excel:

// do work
Application.ActiveWorkbook.Save
Application.DisplayAlerts = False
For Each wrkbk In Application.Workbooks
    If wrkbk.Name <> ThisWorkbook.Name Then wrkbk.Close
Next
ThisWorkbook.Save
ThisWorkbook.Close

The Outlook macro can pool until Workbooks.Count = zero

While appExcel.Workbooks.Count > 0 :DoEvents:Wend
appExcel.DisplayAlerts = False 
appExcel.Quit 
Set appExcel = Nothing 

Answer 6

End your code with the following:

    On Error Resume Next
    On Error GoTo 0

ExitFunction:
    Set objShell = Nothing

End Function