2

I have a function in the outlook VBA that I want to call when a certain excel workbook is closed. Is there a way to do this with the BeforeClose event of excel? I know how to write functions for this event, but I am not sure how to link them to the current outlook session to get to the function.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
loveforvdubs
  • 337
  • 4
  • 6
  • 9

2 Answers2

4

If you wish to get hold of a reference to an instance of Outlook that is already running, you will need to use:

Set myOutlookApp = GetObject(,"Outlook.Application")

which will give you access to the Outlook application object so you can call your desired VBA function in Outlook:

myOutlookApp.MyFunctionToExecute()

You'll probably need to make the function Public otherwise Excel's VBA might not be able to see it.

i_saw_drones
  • 3,486
  • 1
  • 31
  • 50
  • 2
    I believe the function has to be public and it must be in ThisOutlookSession for this method to work. – JimmyPena Nov 09 '11 at 20:34
  • Appears `myOutlookApp.MyFunctionToExecute()` is not valid. Bring the code into Excel. https://stackoverflow.com/questions/51893262/vba-call-outlook-macro-from-excel-object-does-not-support-property-or-method and https://stackoverflow.com/questions/73121335/run-a-macro-in-outlook-from-excel – niton Jul 27 '22 at 01:27
3

MS Office applications can interact with each other by this method (this is based on Office 2007, but others will be similar):

Add a reference to the app into Excel

In Excel VBA, from the Tools\References menu select Microsoft Outlook 12.0 Object Library

In your BeforeClose Event include

Dim olApp As Outlook.Application
Set olApp = New Outlook.Application

You can now access Outlook through the olApp object. I don't know much about the Outlook object model, so others may be able help more from here on...

chris neilsen
  • 52,446
  • 10
  • 84
  • 123