USING EXCEL APPLICATION.RUN IN OUTLOOK
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
Code:
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
Code:
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
