Passing parameters from Outlook to Excel

Question

How to pass arguments to Excel VBA code called from Outlook?

Answer 1

You can execute a macro via the Application.Run method. This method takes the macro name as the first argument and then up to 30 parameters that are passed as arguments to the macro.

In Outlook use the following code:

Public Sub RunExcelMacro()

  Dim excelApp As Object

  Set excelApp = CreateObject("Excel.Application")
  excelApp.Visible = True

  ' open the workbook that contains the macro
  ' or place the macro in a workbook in your XLSTARTUP folder
  excelApp.Workbooks.Open "C:\tmp\book.xls"

  ' run the macro
  excelApp.Run "ThisWorkbook.SayHello", "Hello World!"

  excelApp.Quit

  Set excelApp = Nothing      

End Sub

In Excel, add the following method to the ThisWorkbook element of a spreadsheet document:

Option Explicit

Public Sub SayHello(message As String)

    MsgBox message

End Sub

Answer 2

By using Application.Run:

objExcel.Run "MacroName", param1, param2