Call Excel Macro from Outlook

Hi, thelarster.

This should do it.  

Sub RunExcelMacro()
    Dim excApp As Excel.Application
    Set excApp = GetObject(, "Excel.Application")
    'Change the sheet and macro names on the following line
    excApp.Run "Sheet1.SheetMacro"
    Set excApp = Nothing
End Sub

Hello thelarster,

David has you on the right track.  It is a good idea to qualify it with the workbook name:

    excApp.Run "'Name of the workbook.xls'!Sheet1.SheetMacro"

Also, make sure that the sub is declared as Public, and that the module is not using
Option Private Module in its top-level declarations.

Regards,

Patrick

I tried this and get an error:

User-Defined Type Not Defined for line:
Dim excApp As Excel.Application

Thanks for the quick reply... now I get "Identfer not recognized" for

excApp.Run "PCMaker.xls!PCMaker.AAA_PCMaker"

PCMaker.xls is the workbook, PCMaker is the Sheet and AAA_PCMaker is the macro.

Any ideas?

Is this just a syntex error? I've tried a few different variations after searching.

For the first error, change Excel.Application to Object
For the second, leave off the workbook name.

I think we are closer.... now I get the macro cannot be found.

    Dim excApp As Object
    Set excApp = GetObject(, "Excel.Application")
    'Change the sheet and macro names on the following line
    excApp.Run "PCMaker.AAA_PCMaker"
    Set excApp = Nothing
    Set Msg = Nothing
   

thelarster,

Are you quite sure that that workbook is open in that instance of Excel?  Try changing
to this to test it:

    Dim excApp As Object
    Set excApp = GetObject(, "Excel.Application")
    On Error Resume Next
    Debug.Print excApp.Workbooks("PCMaker.xls").Name
    If Err <> 0 Then
        Err.Clear
        Debug.Print "PCMaker.xls was not open in this instance"
    End If
    On Error GoTo 0
    'Change the sheet and macro names on the following line
    excApp.Run "PCMaker.AAA_PCMaker"
    Set excApp = Nothing
    Set Msg = Nothing


Regards,

Patrick

David,

If you have time, could you take a peek at:
https://www.experts-exchange.com/questions/22724058/Automatic-CC-On-Outgoing.html?

The code I posted there works for me, but the Asker appears to be having trouble with it...

Regards,

Patrick

Yes it is open but I still get the error below even with the new code provided....

Run-Time Error '1004':
The Macro 'PCMaker.AAA_PCMaker' cannot be found.

Patrick,

Thanks!! After rechecking my code I wasn't calling to the module name but the sheet name. Once changed that it works like a charm.

This site is very nice - every one seems knowledgable and helpful.