vba - 如果手动发送电子邮件，则触发代码以记录

Question

我使用 Excel VBA 发送电子邮件。我想记录消息是否已发送。
我从 another post 获取了一些代码.
我按照描述创建了该类，并添加了一些额外的位以查看它是否有效。
它初始化，但没有其他任何事情发生。发送邮件后，该类(class)不知何故在后台保持打开状态，因此我必须在 VBE 中将其停止。
这是调用代码:

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

称为 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

它似乎从未注册 _Send ，我认为这可能与未定义的类对象或其他有关。有时我会收到警告，此时它正在初始化，然后立即终止而无需等待 _Send .
在 Windows 7 上，通过我无法控制的本地授权网络使用 Excel 2007。

Answer 1

类(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

模块

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

希望这可以帮助