CODEWITHSUNDEEP
excelvbaoutlook
ACCtionMan
ACCtionMan

Reputation: 511

Run Excel Macro from Outlook and Return a Value to Outlook

I have the following code I am running from Outlook to run a macro in Excel. I know I can trap an error if the macro itself fails to run, but I'd like to know what the result was when the macro successfully executes. In my mock example Excel function, I would like to return the value "Yes" to Outlook.

Outlook part

Dim excelWorkbook As Excel.workbook
Set excelWorkbook = GetObject(workbookPath & workbookName) ' variables contain full path and file name

' This works
excelWorkbook.Application.Run "functions.didItWork"

Dim testVal As Variant

' This gives me an error "Compile error: Expected: end of statement"
testVal = excelWorkbook.Application.Run "functions.didItWork"

Excel part - obviously not my final code :)

Function didItWork()

didItWork = "Yes"

End Function

Upvotes: 0

Views: 244

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

Almost there: you need to use parentheses when calling a function and using the return value

testVal = excelWorkbook.Application.Run("functions.didItWork")
MsgBox testVal

Upvotes: 1

Related Questions