Passing parameters from Outlook to Excel

How to pass argumen开发者_C百科ts to Excel VBA code called from Outlook?

By using Application.Run:

objExcel.Run "MacroName", param1, param2

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