CODEWITHSUNDEEP
excelvbaoutlookexcel-2007outlook-2007
loveforvdubs
loveforvdubs

Reputation: 347

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

Upvotes: 2

Views: 19501

Answers (2)

i_saw_drones
i_saw_drones

Reputation: 3506

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.

Upvotes: 5

chris neilsen
chris neilsen

Reputation: 53136

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

Upvotes: 3

Related Questions