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




Syntaxexpression.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 (https://docs.microsoft.com/en-us/office/vba/api/excel.application(object)) object.
Parameters
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 (https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)) 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?