USING EXCEL APPLICATION.RUN IN OUTLOOK

[jbennett]

I receive many e-mails which I need to re-direct to many other departments around my company. Recently, the company expanded to several other states so now I need to direct e-mails to the same departments but for other states. For example one email address might be FLAutoRepair... but another might be NYAutoRepair... Further expansion is possible. Prior to the expansion I created a macro which allowed me to select the recipient department and then called the correct macro to make the e-mails, but that was only 8 possible choices. Now it has ballooned into about 70 possible choices and I wanted to create a way to ask a couple of questions (State and Recipient) and from that generate a macro name rather than have 70+ possible choices to choose from. However CALL does not work with variables (according to my research) so I have to use APPLICATION.RUN. However, .RUN is not a method of the Outlook Application object so I need to use Excel's Application object. However, that is generating this error: "Run-time error '1004': Cannot run the macro 'Forward_CC_Response_Auto'. The macro may not be available in this workbook or all macros may be disabled."

The code is below and below that is the code of the Form you see in the first block. I also need to pass arguments to the called macro.

CODE OF CALLING MACRO

Sub Choose_Forward_Respond_CC_MultiState()


    ' Macro to choose which Respond & Forward with CC request e-mail macro to use for multiple states.
    
    ' Declare variables
        Dim appExcelRun As Excel.Application
        Dim objMail As Outlook.MailItem
        Dim objMailCopy As Outlook.MailItem
        Dim myNameSpace As Outlook.NameSpace
        Dim ofInbox As Outlook.Folder
        Dim ofDestination As Outlook.Folder
        Dim objRespondForwardCC As Object
        Dim strState, strRecipient, strCombinedRecipient, strCallName As String
        
        ' Instantiate Objects
        Set objItem = GetCurrentItem()
        Set objMail = objItem
        Set myNameSpace = Application.GetNamespace("MAPI")
        Set ofInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
        Set appExcelRun = New Excel.Application
        
        ' Declare vairable of location form.
        Dim frmRespondForward As New frmRecipientChooser_V3
        
        ' Loading and running form.
        Load frmRespondForward
        frmRespondForward.Show
        strState = frmRespondForward.lstStateChoice.Value
        strRecipient = frmRespondForward.lstRecipientChoice.Value
        Unload frmRespondForward
        
        strCombinedRecipient = strState & "_" & strRecipient
        ' strCallName = "Forward_CC_" & strCombinedRecipient
        
        ' Execute e-mail macro.
        If strRecipient = "Auto" Then
            appExcelRun.Run "Forward_CC_Response_Auto"
        Else
            app.ExcelRun.Run "strCallName"
        End If
        
    ' Release objects.
    Set objItem = Nothing
    Set objMail = Nothing
    Set objMailCopy = Nothing
    Set ofInbox = Nothing
    Set ofDestination = Nothing


End Sub

Private Sub UserForm_Initialize()
    
    ' Initialize variables
    Dim varStateChoice As String
    
    ' Create list for State
    lstStateList = Array("BK", "FL", "GA", "KY", "MD", "NJ", "NY", "OH", "PA", "PR", "SC", "TX", "VA")
    With Me.lstStateChoice
        .List = lstStateList
        .ListIndex = 0
    End With
    
    ' Create list for recipients
    lstRecipientList = Array("Specify", "Auto")
    With Me.lstRecipientChoice
        .List = lstRecipientList
        .ListIndex = 1
    End With


End Sub

[Paul_Hossler]

I don't see where you're opening the Workbook containing the macro

Syntax

expression.Run (Macro, Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)
expression A variable that represents an Application object.
Parameters

Name	Required/Optional	Data type	Description
Macro	Optional	Variant	The macro to run. This can be either a string with the macro name, a Range object indicating where the function is, or a register ID for a registered DLL (XLL) function. If a string is used, the string will be evaluated in the context of the active sheet.
Arg1–Arg30	Optional	Variant	An argument that should be passed to the function.

[jbennett]

I don't see where you're opening the Workbook containing the macro

I'm not. I'm using application.run entirely in Outlook without any use of Excel, other than the Excel Application object. Are you suggesting that I have to use it within a Workbook? If so, that occurred to me but I hope that I'm wrong.

[Paul_Hossler]

Well, I'm guessing that based on this ...

"Run-time error '1004': Cannot run the macro 'Forward_CC_Response_Auto'. The macro may not be available in this workbook or all macros may be disabled."

and your macro, that all you have is an instance of Excel with no workbook containing your macro


I'd start with appExcelRun.Open (...) before trying to run the macro

[jbennett]

Well, I'm guessing that based on this ...

Run-time error '1004': Cannot run the macro 'Forward_CC_Response_Auto'. The macro may not be available in this workbook or all macros may be disabled."

and your macro, that all you have is an instance of Excel with no workbook containing your macro

Correct. This macro is only intended for Outlook and the only reason I'm brining in Excel objects is to get access to Application.Run (for some reason the Outlook Application object lacks that method).

I'd start with appExcelRun.Open (...) before trying to run the macro

OK, I tried appExcelRun.Open but .Open does not appear to be a valid method. At least it did not appear in the dropdown list. How would I use it?

[Paul_Hossler]

Sorry

S/B probably something like

AppExcelRun.Workbooks.Open Filename:=".........."

The .Open method is in the Workbooks collection methods

[jbennett]

Sorry

S/B probably something like

AppExcelRun.Workbooks.Open Filename:=".........."

The .Open method is in the Workbooks collection methods

Thank you. As I suspected, what I wanted to do simply won't work in Outlook.

Does anyone know a way to CALL work with variables?