• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1535
  • Last Modified:

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!
0
thelarster
Asked:
thelarster
  • 6
  • 4
  • 2
1 Solution
 
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
 
thelarsterAuthor Commented:
I tried this and get an error:

User-Defined Type Not Defined for line:
Dim excApp As Excel.Application
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
Patrick MatthewsCommented:
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now