Trigger code to record if email sent manually

Question

I send email using Excel VBA. I want to record whether the message has been sent.

I have taken some code from another post.

I created the class as described, and put in a few extra bits to see if it is working.
It initializes, but then nothing else happens. After the mail is sent, the class remains open in the background somehow, so I have to stop it in the VBE.

Here is the calling code:

Sub SendProc2(add As String)

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next

    With OutMail
        .To = add
        .CC = ""
        .BCC = ""
        .Subject = ThisWorkbook.Name
        .Body = Application.WorksheetFunction.VLookup(Worksheets("Data").Range("B135"), Range("formversion"), 2, False) _
        & " Attached:" & vbCrLf & vbCrLf & ThisWorkbook.Name
        .Attachments.add ActiveWorkbook.FullName
        .Display   'or use .Send
    End With

    Dim CurrWatcher As EmailWatcher
    Set CurrWatcher = New EmailWatcher
    Set CurrWatcher.TheMail = OutMail

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Unload UserForm4

End Sub

The Class Module Code which is called EmailWatcher:

Option Explicit
Public WithEvents TheMail As Outlook.MailItem

Private Sub Class_Terminate()

    Debug.Print "Terminate " & Now()

End Sub

Private Sub TheMail_Send(Cancel As Boolean)

    Debug.Print "Send " & Now()
    'enter code here
    
End Sub

Private Sub Class_Initialize()

    Debug.Print "Initialize " & Now()

End Sub

It never seems to register the _Send, which I think might be something to do with the class object not being defined or something else. Sometimes I get warnings, at the moment it is initializing, then terminating immediately without waiting for the _Send.

Using Excel 2007, on Windows 7, over a local authority network that I have no control over.

Do you still have the enter code here in there? I think you are setting the mail to be your sunk class after it's gone. Outmail should be the class object I believe http://www.wiseowl.co.uk/blog/s242/event-sink.htm — Nathan_Sav, Jun 22 '16 at 08:52

score 0 · Answer 1 · answered Jun 22 '16 at 08:59

Class

Private WithEvents EM As Outlook.MailItem

Public Sub INIT(x As Outlook.MailItem)
    Set EM = x
End Sub

Private Sub EM_Send(Cancel As Boolean)

End Sub

Module

Public WATCHER As clsEmailWatch

Sub EMAIL()

Dim o As Outlook.Application
Dim m As Outlook.MailItem

Set o = New Outlook.Application
Set m = o.CreateItem(olMailItem)

Set WATCHER = New clsEmailWatch
WATCHER.INIT m

m.To = "xyz@abc.com"

m.Send

End Sub

Hope this helps

score 0 · Answer 2 · edited Feb 20 '19 at 10:04

This looks like it has something todo with displaying mails while a userform is running.

I had the same problem that, while a userform exists, the Outlook events didn't register. To fix the problem I implemented kind of a hack:

You need a boolean property in your class or userform module:

Private someBool as Boolean

And you need to subscribe to the MailItem.Close Event and set the new boolean:

Private Sub TheMail_Close(Cancel As Boolean)
    someBool = True
End Sub

This event is raised when the displayed E-Mail is closed, sent or saved.

Then you obviously need a Property Get method:

Public Property Get MailClosed() As Boolean
    MailClosed = someBool
End Property

And now, to handle all the Events, you need a Loop in the module where you Display your mail from:

[...]
Dim CurrWatcher As EmailWatcher
Set CurrWatcher = New EmailWatcher
Set CurrWatcher.TheMail = OutMail

Do Until CurrWatcher.MailClosed
    DoEvents
Loop

[...]

I am not certain why DoEvents works, if someone could shed some light on it I'll add it to my answer.

Trigger code to record if email sent manually

2 Answers2

Linked