Consulting

Results 1 to 7 of 7

Thread: USING EXCEL APPLICATION.RUN IN OUTLOOK

  1. #1
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    4
    Location

    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
    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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,737
    Location
    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.
    Arg1Arg30 Optional Variant An argument that should be passed to the function.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    4
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,737
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    4
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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?

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,737
    Location
    Sorry

    S/B probably something like

    AppExcelRun.Workbooks.Open Filename:=".........."
    The .Open method is in the Workbooks collection methods
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Newbie
    Joined
    Jan 2021
    Posts
    4
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •