How to Trap Outlook Events from Excel Application

excel, outlook, vba

Solution

You are trying to work with events in outlook, from an Excel thread, really really interesting Q and I didn't know if it would be possible. I think this will get you started.

I am hoping to be able to track the user and date of who accessed the email hyperlink and actually sent it.

PROBLEM: The hyperlink is opening another application (Outlook), over which you don't have full control. And at least from the VBA side, you do NOT have control over the Outlook events.

I thought there may be an easier way to hack around a solution but that was a dead end, you had hinted at class object, so I figured I had an idea that might work... never done this before though, so it's a work in progress.

To solve this, I settle on an approach that does:

- Kills the hyperlinks so that they don't automatically launch Outlook

- Use the `SelectionChange` event to send the mail via VBA rather than the `FollowHyperlink` event

- Create a custom event handler class object for an Outlook MailItem which will trap the `_Send` event, which you can then use to log the details of the send.

Here are the codes/instructions:

Create a class object called `cMailItem` and put this code inside it:

Option Explicit
'MailItem event handler class
Public WithEvents m As Outlook.MailItem

Public Sub Class_initialize()

    Set m = olApp.CreateItem(0)

End Sub

Private Sub m_Send(Cancel As Boolean)

        Debug.Print "Item was sent by " & Environ("Username") & " at " & Now()
        Call ReleaseTrap

End Sub

In a STANDARD code module (I call this one `HelperFunctions` but the name doesn't matter) put this code, which will set a flag for our `cMailItem` Event Handler class and also contains the function which returns the instance of Outlook Application.

Option Explicit
'#################
'NOTE: The TrapEvents should be called when the Forms are initialized
'NOTE: The ReleaseTrap should be called when the Forms are closed
Public olApp As Outlook.Application
Public cMail As New cMailItem
Public TrapFlag As Boolean

Sub TrapEvents()
If Not TrapFlag Then
   Set olApp = GetApplication("Outlook.Application")
   TrapFlag = True
End If
End Sub

Sub ReleaseTrap()
If TrapFlag = True Then
   Set olApp = Nothing
   Set cMail = Nothing
   TrapFlag = False
End If
End Sub

Function GetApplication(Class As String) As Object
'Handles creating/getting the instance of an application class
Dim ret As Object

On Error Resume Next

Set ret = GetObject(, Class)
If Err.Number <> 0 Then
    Set ret = CreateObject(Class)
End If

Set GetApplication = ret

On Error GoTo 0

End Function

Now, part of the problem is the way that the hyperlink follow takes precedence over other events. To avoid that, I use some code to "kill" the hyperlinks. They will "link" only to the cell wherein they reside, but they will still contain the text for the email address.

Instead of using the `FollowHyperlink` event, I use the `SelectionChange` event to call another procedure which sends the mail.

In your WORKSHEET module, put the following event handlers AND the `SendMail` procedure:

Option Explicit

Private Sub Worksheet_Activate()
'Converts Mailto hyperlinks so that they do NOT
' automatically open Outlook MailItem

    Dim h As Hyperlink

    For Each h In ActiveSheet.Hyperlinks
        If h.Address Like "mailto:*" Then
            h.ScreenTip = h.Address
            h.Address = ""
            h.SubAddress = h.Range.Address
        End If

    Next

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Disable Excel events
Application.EnableEvents = False

    If Target.Cells.Count <> 1 Then GoTo EarlyExit
    If Target.Hyperlinks.Count <> 1 Then GoTo EarlyExit

    'Send mail to the specified recipient/etc.
    Call SendMail(Target)

EarlyExit:
'Re-enable events:
Application.EnableEvents = True

End Sub
Private Sub SendMail(Target As Range)

Dim Body1$, Body2$, Body3$
Dim OlMail As Outlook.MailItem
Const OLMAILITEM As Long = 0

'Set our Outlook event trap
Call TrapEvents

'CREATE the mailitem
Set OlMail = cMail.m 

With OlMail

    Body1 = "This is my weekday text"
    Body2 = "This is my Saturday text"
    Body3 = "This is my Sunday text"

    .To = Target.Text
    .Subject = "Subject"
    '.Attachemnts.Add "C:\Path"
    .CC = Target.Offset(0, 4).Text
    .BCC = ""

    .Display
End With


End Sub

NOTE ON REVISED ANSWER

I revised this from the original solution which used an Outlook Application event handler class, which was limited by the fact that it would trap ANY item_send event, this was problematic because multi-tasking users would send false positives. The revised solution uses an event handler for the `MailItem` object which is created at run-time, and should avoid that pitfall.

THERE MAY BE OTHER LIMITATIONS

For example, this method does not really handle "multiple" emails, so if the user clicks one link, and then another, there will only be ONE email that exists and can be tracked. If you need to handle multiple emails, use a public `Collection` of this class object, which I did for this similar question.

As I said, this is the first time I've ever attempted to use a `WithEvents` handler between two applications. I've used theme in single-application Add-Ins, etc., but never binding two applications in this manner, so it's uncharted territory for me.

Problem

Original source