passing parameters from outlook vba to macro

Question

how to pass some argumemnts to macro in excel called from outllook vba

Answer 1

By using Application.Run:

objExcel.Run "MacroName", param1, param2

Answer 2

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