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.

score 2 · Accepted Answer · answered Mar 09 '16 at 18:14

2

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.

answered Mar 09 '16 at 18:14

OpiesDad

3,385
2
16
31

I couldn't remember off the top of my head if WithEvents applied to other applications (and was too busy to test) so was happy to see your answer. That's a pretty amazing capability - makes me want to come up with a reason to need it :-) – Doug Glancy Mar 10 '16 at 00:43
@DougGlancy Yeah, it's pretty neat. Needs to be used in a class module or form though....can't be used in a regular module. – OpiesDad Mar 10 '16 at 16:22
Item.subject prints subject, but Item.Sender doesn't allow me to get the senders email address - which is another exchange user. Is this something to do with propertyAccessor? – Al Grant Nov 30 '20 at 17:51
@Al Grant You have the wrong property. When the item is an email, it is of type `MailItem` If you want the email address of the sender you want `Item.SenderEmailAddress` or if just the name `Item.SenderName`. The `Sender` property is not a String, it's an AddressEntry object. See: https://learn.microsoft.com/en-us/office/vba/api/outlook.mailitem.sender Note that I do not know enough about email to determine whether the items I'm suggesting can be faked or not, so hopefully your usage does not need any sort of security. – OpiesDad Dec 02 '20 at 17:04

Utilizing Outlook Events From Excel

1 Answers1

Linked