Utilizing Outlook Events From Excel

Question

I'm attempting to utilize the "ItemAdd" Event MSDN Link Here from the Outlook Object Model in a Excel macro; however, I haven't been able to find a way to utilize events from outside of Excel.

My intended use case is to monitor items received in a shared mailbox and log their reception. I am currently running a loop on a timer that checks for new emails, however I would prefer to simply log each time an event fires if possible.

Unfortunately I am not able to run any macros from Outlook directly so I am restricted to accessing the Outlook Object Model from other Office applications.

OpiesDad OpiesDad · Accepted Answer · 2016-03-09T18:14:23

You can do this, but you need to use a Class module in Excel to accomplish it.

Class Module - "myOutlook" or call it whatever you want.

 Private WithEvents myItems As Outlook.Items

 Private Sub Class_Initialize()

     Dim oNS As Namespace
     Dim myOL As Outlook.Application

     Set myOL = New Outlook.Application
     Set oNS = myOL.GetNamespace("MAPI")
     Set myItems = oNS.GetDefaultFolder(olFolderInbox).Items
     'Set this equal to the folder you wish to use this on

 End Sub

 Private Sub myItems_ItemAdd(ByVal Item As Object)

      Debug.Print "Got_EMAIL!!!"

 End Sub

Then, in a regular module do this:

 Dim myOutlook As myOutlook

 Sub TestSub()

      Set myOutlook = New myOutlook

  End Sub

Once you initialize the instance of your user defined class, the events will be caught by it.

Obviously, you will need to set the "myItems" object to be linked to the correct inbox. For mine, it's just linked to my default mailbox which was easiest for testing.

Utilizing Outlook Events From Excel

1 Answers