Call outlook VBA from Excel

Question

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.

Answer 1

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...

Answer 2

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.