Call Excel Macro from Outlook

Is it possible to call/start an Excel Macro from an Outlook Macro? I have an Outlook Macro that runs - I want it to start an Excel Macro in a Sheet that is already open.

If this is, any example code? Thanks!
thelarsterAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
thelarster said:
>>Yes it is open but I still get the error below even with the new code provided....

1) You're sure there is a module named PCMaker?

2) You're sure the sub AAA_PCMaker is not declared as private?

3) You're sure your module does not include Option Private Module in its declarations?
0
 
David LeeCommented:
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
0
 
Patrick MatthewsCommented:
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
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
thelarsterAuthor Commented:
I tried this and get an error:

User-Defined Type Not Defined for line:
Dim excApp As Excel.Application
0
 
thelarsterAuthor Commented:
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?
0
 
thelarsterAuthor Commented:
Is this just a syntex error? I've tried a few different variations after searching.
0
 
David LeeCommented:
For the first error, change Excel.Application to Object
For the second, leave off the workbook name.
0
 
thelarsterAuthor Commented:
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
   
0
 
Patrick MatthewsCommented:
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
0
 
Patrick MatthewsCommented:
David,

If you have time, could you take a peek at:
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_22724058.html?

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

Regards,

Patrick
0
 
thelarsterAuthor Commented:
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.
0
 
thelarsterAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.