触发代码以记录是否手动发送电子邮件 [英] Trigger code to record if email sent manually

问题描述

我使用Excel VBA发送电子邮件.我想记录邮件是否已发送.

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

我从我按照所述创建了该类，并添加了一些额外的位以查看其是否有效.
它初始化，但随后没有其他任何反应.发送邮件后，该类仍会在后台保持打开状态，因此我必须在VBE中将其停止.

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.

这是呼叫代码:

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的类模块代码:

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

似乎从未注册过 _Send ，我认为这可能与未定义类对象或其他原因有关.有时我会在初始化时收到警告，然后立即终止而不必等待 _Send .

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.

在Windows 7上，在无法控制的本地权限网络上使用Excel 2007.

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

推荐答案

这似乎与在运行用户窗体时显示邮件有关.

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

我遇到的一个问题是，尽管存在用户窗体，但Outlook事件没有注册.为了解决这个问题，我实施了一种技巧:

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

并且您需要订阅 MailItem.Close事件并设置新的布尔值:

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.

那么您显然需要一个Property Get方法:

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.

这篇关于触发代码以记录是否手动发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！