Call outlook VBA from Excel

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 e开发者_如何学编程vent 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.

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.

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