locked
How to fire Outlook macro through Excel VBA? RRS feed

  • Question

  • Hi,

    Could you tell me if it's possible to fire Outlook macro through Excel VBA?

    For example, here is my macro under Outlook:

    Sub toto()
    MsgBox "Hello world !!"
    End Sub

    Here is my macro under Excel :

    Sub test()

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Call OutApp.toto (Or OutApp.run toto)

    end Sub

    But, it doesn't work. Thank you very much for your help.

     

     


    Best Regards Jun LOU
    • Edited by Jun LOU Tuesday, December 27, 2011 4:16 PM
    Tuesday, December 27, 2011 4:15 PM

Answers

  • As far as I can tell, it's not possible to run macros that are stored in Outlook from other applications. Outlook lacks the Application.Run method that is available in Word and Excel.

    In this specific example, there's no Outlook-specific code involved: the HelloWorld macro merely displays a generic message box. But more realistic examples will perform Outlook-specific actions such as creating an appointment or sending an e-mail. Once again, I don't think you can call such an Outlook macro from Excel, but you can incorporate the Outlook code into an Excel macro.

    Here is a very short example. It also shows how to use values from your Excel worksheet in the Outlook code.

    Sub SendAnEmail()
        Dim objOL As Object
        Dim objMI As Object
        Dim Recip As Object
        ' Outlook application object
        Set objOL = CreateObject("Outlook.Application")
        ' Create e-mail message
        Set objMI = objOL.CreateItem(0) ' 0 = olMailItem
        ' Set some properties and send the message
        With objMI
            .To = Range("B1")
            .Subject = Range("B2")
            .Body = Range("B3")
            .Send
        End With
        ' Quit Outlook
        objOL.Quit
    End Sub
    

    Lots of examples can be found on Ron de Bruin's website: http://www.rondebruin.nl/sendmail.htm.


    Regards, Hans Vogelaar
    • Proposed as answer by VBAToolsMVP, Editor Wednesday, December 28, 2011 2:50 PM
    • Unproposed as answer by Jun LOU Thursday, December 29, 2011 8:35 AM
    • Marked as answer by Jun LOU Thursday, December 29, 2011 8:35 AM
    Wednesday, December 28, 2011 2:19 PM
  • Jun:

    Sorry about that!!  I actually made it work yesterday (somehow), but today it's not working.  I was not able to create a working version today by calling the Outlook VBA module from Excel, even when including the Outlook library in Excel.  I would go with the advice given above to incorporate the code in your Excel.  Hans is usually right on in his solutions.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com


    • Edited by RichLocus Thursday, December 29, 2011 12:32 AM
    • Marked as answer by Jun LOU Thursday, December 29, 2011 8:37 AM
    Wednesday, December 28, 2011 5:27 PM

All replies

  • Hello:

    I was able to make this work as follows:

    (1) In Outlook, you must define the hello world procedure as PUBLIC in the "ThisOutLook" session area, not the general modules area.

    Here's my code that worked:

    Outlook:

    Option Explicit
    Public Sub HelloWorld()
    MsgBox "Hello world !!"
    End Sub
    
    


    Here's the Excel Code:

    Option Explicit
    
    Sub test()
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    OutApp.HelloWorld
    
    End Sub
    
    
    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Wednesday, December 28, 2011 1:03 AM
  • Thank you very much, RichLocus

    I've tried your code on my pc, but I got a error message when it arrives the line "OutApp.HelloWorld" under Excel.

     I'm using Office2010. Do you have any idea?

    Many thanks for your help


    Best Regards Jun LOU
    • Edited by Jun LOU Wednesday, December 28, 2011 10:13 AM
    Wednesday, December 28, 2011 10:12 AM
  • Hi RichLocus,

    Here's the Outlook and Excel library on my pc.

    Thanks

     


    Best Regards Jun LOU
    Wednesday, December 28, 2011 10:19 AM
  • This code is late binding method (As Object)

    So you do not have to use references.

    Anyway Rich make mistake with

    OutApp.HelloWorld

    because no object HelloWord in Outlook.

    Should be or Msgbox "Hello Word" or Call HelloWord


    Oskar Shon, Office System MVP

    Press if Helpful

    Wednesday, December 28, 2011 10:59 AM
    Answerer
  • Hi VBATools,

    Thank you for your help.

    Unfortunately, it still doesn't work.


    Best Regards Jun LOU
    Wednesday, December 28, 2011 12:20 PM
  • Now I understud that, You do not want start procedure For Outlook in Excel,

    but You want to run in Excel, procedure saved IN Outlook.

    Rich can be right, but OTM should be saved and close first.

    I do not do that before, and I try it without good result ;/

         Application.Run HelloWord 'do not work - but no error message

    Interesting - This will be a mystery to me


    Oskar Shon, Office System MVP

    Press if Helpful

    Wednesday, December 28, 2011 1:41 PM
    Answerer
  • You should incorporate the Outlook code into your Excel macro, instead of trying to call a macro stored in Outlook.

    You can use the OutApp object to execute Outlook VBA.


    Regards, Hans Vogelaar
    Wednesday, December 28, 2011 1:48 PM
  • Hi, Hans

    Thank you in advanced.

    Here's my code that doesn't work:

    Outlook:

    Option Explicit
    Public Sub HelloWorld()
    MsgBox "Hello world !!"
    End Sub
    
    

    Here's the Excel Code:

    Option Explicit
    
    Sub test()
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    
    ' I've tried both, but non of them works
    OutApp.HelloWorld (Or OutApp.Run HelloWorld)
      
    End Sub


    Best Regards Jun LOU

    • Edited by Jun LOU Wednesday, December 28, 2011 2:12 PM
    Wednesday, December 28, 2011 2:01 PM
  • VBATools,

    Thank you for tring it for me.

    I think it's simpler if I fire Excel macro from Outlook.

    That's I wanted to do this morning.

    Thank you again

     

     

     

     


    Best Regards Jun LOU
    Wednesday, December 28, 2011 2:10 PM
  • As far as I can tell, it's not possible to run macros that are stored in Outlook from other applications. Outlook lacks the Application.Run method that is available in Word and Excel.

    In this specific example, there's no Outlook-specific code involved: the HelloWorld macro merely displays a generic message box. But more realistic examples will perform Outlook-specific actions such as creating an appointment or sending an e-mail. Once again, I don't think you can call such an Outlook macro from Excel, but you can incorporate the Outlook code into an Excel macro.

    Here is a very short example. It also shows how to use values from your Excel worksheet in the Outlook code.

    Sub SendAnEmail()
        Dim objOL As Object
        Dim objMI As Object
        Dim Recip As Object
        ' Outlook application object
        Set objOL = CreateObject("Outlook.Application")
        ' Create e-mail message
        Set objMI = objOL.CreateItem(0) ' 0 = olMailItem
        ' Set some properties and send the message
        With objMI
            .To = Range("B1")
            .Subject = Range("B2")
            .Body = Range("B3")
            .Send
        End With
        ' Quit Outlook
        objOL.Quit
    End Sub
    

    Lots of examples can be found on Ron de Bruin's website: http://www.rondebruin.nl/sendmail.htm.


    Regards, Hans Vogelaar
    • Proposed as answer by VBAToolsMVP, Editor Wednesday, December 28, 2011 2:50 PM
    • Unproposed as answer by Jun LOU Thursday, December 29, 2011 8:35 AM
    • Marked as answer by Jun LOU Thursday, December 29, 2011 8:35 AM
    Wednesday, December 28, 2011 2:19 PM
  • Thank you for all, Hans

    I wanted to achieve it by another way.

    Only, RichLocus's answer gave me the false hope.

    Have a good day

     

     

     


    Best Regards Jun LOU
    Wednesday, December 28, 2011 2:29 PM
  • That is right, I always run my code from one repository.

    I was so embarrassed - but I accepted this as a curiosity

    Regards.


    Oskar Shon, Office System MVP

    Press if Helpful

    Wednesday, December 28, 2011 2:50 PM
    Answerer
  • Jun:

    Sorry about that!!  I actually made it work yesterday (somehow), but today it's not working.  I was not able to create a working version today by calling the Outlook VBA module from Excel, even when including the Outlook library in Excel.  I would go with the advice given above to incorporate the code in your Excel.  Hans is usually right on in his solutions.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com


    • Edited by RichLocus Thursday, December 29, 2011 12:32 AM
    • Marked as answer by Jun LOU Thursday, December 29, 2011 8:37 AM
    Wednesday, December 28, 2011 5:27 PM
  • Don't worry, RichLocus

    Finally, I decide to create 2 buttons, one for Excel, another for Outlook.

    Meantimes, I've tried to fire Excel Macro from Outlook, it didn't work.

    But I could fire Word Macro from Outlook two weeks ago.

    In any way, thank you very much for all of the suggestion you provided.

    Have a nice day

     

     

     

     

     


    Best Regards Jun LOU
    Thursday, December 29, 2011 8:32 AM
  • Don't feel embarrassed, VBATools

    You are very good at VBA if you believe in you.

    Thank you for your help

     

     

     


    Best Regards Jun LOU
    Thursday, December 29, 2011 8:36 AM