EXCEL VBA，手册Outlook电子邮件发件人，类模块问题 [英] EXCEL VBA, Manual Outlook email sender, Class module Issue

问题描述

我仍在处理我在中描述的问题关于这个话题的第一个问题。为了简短刷新，它是一个excel文件，其中包含电子邮件模板和附件的列表，对于每个列表单元，我添加了打开发给单元模板的按钮进行一些更改，然后附加文件并显示邮件用户。用户可以根据需要修改邮件，然后发送或不发送邮件。我已经尝试过几种方法，如下所述。
不幸的是，我现在处于类模块的问题上，即将描述此处。我已经创建了一个类模块，例如EmailWatcher，甚至使用这里：

I am still working on the problem that I have described in my 1st question on this topic. For short refresh, it is an excel file which contains the list of email templates and attachments, to each list unit I have add the button which opens the template of the giving unit make there some changes, then attaches files and display the mail to the User. User can amend mail if necessary and then send or not to send mail. I have tried several approaches described below. Unfortunately, I am stalled now on the issue with class module, that shortly described here. I do have created a class module, such as 'EmailWatcher' and even make a small combination with method described here:

Option Explicit
Public WithEvents TheMail As Outlook.MailItem

Private Sub Class_Terminate()
Debug.Print "Terminate " & Now()  
End Sub

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

Private Sub x_Send(Cancel As Boolean)
Debug.Print "Send " & Now()
ThisWorkbook.Worksheets(1).Range("J5") = Now()
'enter code here
End Sub

Private Sub Class_Initialize()
Debug.Print "Initialize " & Now()    
End Sub

对以下表单的更改不会发生任何更改： / p>

The change to following form does not make any change:

Option Explicit
Public WithEvents TheMail As Outlook.MailItem

    Private Sub Class_Terminate()
    Debug.Print "Terminate " & Now()  
    End Sub

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

    Private Sub TheMail_Send(Cancel As Boolean)
    Debug.Print "Send " & Now()
    ThisWorkbook.Worksheets(1).Range("J5") = Now()
    'enter code here
    End Sub

    Private Sub Class_Initialize()
    Debug.Print "Initialize " & Now()    
    End Sub

模块代码如下：

Public Sub SendTo()
    Dim r, c As Integer
    Dim b As Object
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        r = .Row
        c = .Column
    End With

    Dim filename As String, subject1 As String, path1, path2, wb As String
    Dim wbk As Workbook
    filename = ThisWorkbook.Worksheets(1).Cells(r, c + 5)
    path1 = Application.ThisWorkbook.Path & 
    ThisWorkbook.Worksheets(1).Range("F4")
    path2 = Application.ThisWorkbook.Path & 
    ThisWorkbook.Worksheets(1).Range("F6")
    wb = ThisWorkbook.Worksheets(1).Cells(r, c + 8)

    Dim outapp As Outlook.Application
    Dim oMail As Outlook.MailItem
    Set outapp = New Outlook.Application
    Set oMail = outapp.CreateItemFromTemplate(path1 & filename)

    subject1 = oMail.subject
    subject1 = Left(subject1, Len(subject1) - 10) & 
    Format(ThisWorkbook.Worksheets(1).Range("D7"), "DD/MM/YYYY")
    oMail.Display
    Dim CurrWatcher As EmailWatcher
    Set CurrWatcher = New EmailWatcher
    CurrWatcher.INIT oMail
    Set CurrWatcher.TheMail = oMail

    Set wbk = Workbooks.Open(filename:=path2 & wb)

    wbk.Worksheets(1).Range("I4") = 
    ThisWorkbook.Worksheets(1).Range("D7").Value
    wbk.Close True
    ThisWorkbook.Worksheets(1).Cells(r, c + 4) = subject1
    With oMail
        .subject = subject1
        .Attachments.Add (path2 & wb)
    End With
    With ThisWorkbook.Worksheets(1).Cells(r, c - 2)
        .Value = Now
        .Font.Color = vbWhite
    End With
    With ThisWorkbook.Worksheets(1).Cells(r, c - 1)
        .Value = "Was opened"
        .Select
    End With       
End Sub

最后我做了一个正在工作的课，我有放一些控件来检查它，你可以从类模块代码中看到。但问题是，它没有抓住发送事件。该类终止于子结尾。将电子邮件完全留给用户。问题是：错误在哪里？或者如何将课程模块放在所谓的等待模式中，还是其他任何建议？
我也考虑在发件箱中搜索邮件的方式，但Send事件的方法更加有利。

Finally I have made a class which is working and I have put some controls to check it as you can see from class module code. But the problem is, it does not catch the Send event. The class is terminating at the end of the sub. Leaving the email fully to User. The question is: where is mistake? Or how to leave the class module in so called "waiting mode", or maybe any other suggestions? I so also consider the way to search for mails in the 'outbox' but the approach with Send event is much more in favour.

推荐答案

我回答了一个类似的问题这里，看看，我认为当你在正确的轨道上，你有一些错误的你的实现。尝试这样做：

I answered a similar question here and looking over that, I think that while you're on the right track, you've got a few things wrong with your implementation. Try this instead:

执行类模块，摆脱不必要的 INIT 过程，并使用 Class_Initialize 程序来创建 Mailitem 。

Do the Class module as so, get rid of the unnecessary INIT procedure and use the Class_Initialize procedure to create the Mailitem.

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()
    ThisWorkbook.Worksheets(1).Range("J5") = Now()
    'enter code here
    End Sub
    Private Sub Class_Initialize()
    Debug.Print "Initialize " & Now()
    'Have Outlook create a new mailitem and get a handle on this class events
    Set TheMail = olApp.CreateItem(0)
    End Sub

在正常模块中使用的示例，已测试&确认这是正常工作，并将处理多个电子邮件（我以前的答案没有完成）。

Example for use in normal module, tested & confirmed this is working and will handle multiple emails (which my previous answer didn't accomplish).

Option Explicit
Public olApp As Outlook.Application
Public WatchEmails As New Collection

Sub SendEmail()
If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application")
Dim thisMail As New EmailWatcher
WatchEmails.Add thisMail
thisMail.TheMail.Display
thisMail.TheMail.To = "[e-mail]"
thisMail.TheMail.Subject = "test"
thisMail.TheMail.Display
End Sub

它是如何工作的？首先，我们确保我们有一个 Outlook.Application 实例来处理。这将在模块中作为 Public 的范围，因此它将可用于其他程序&课程。

How's it work? First, we make sure we have an Outlook.Application instance to work with. This will be scoped as a Public in module so it will be available to other procedures & classes.

然后，我们创建一个新的 EmailWatcher 类的实例，它引发了 Class_Initialize 事件。我们利用此事件，已经处理的 Outlook.Application 实例创建&分配 TheMail 对象事件处理程序。

Then, we create a new instance of our EmailWatcher class, which raises the Class_Initialize event. We leverage this event, and the already handled instance of Outlook.Application to create & assign the TheMail object event handler.

我们将它们存储在 Public 集合，以便即使在 SendMail 过程运行时间结束后，它们也保留在范围内。这样你可以创建几个电子邮件，他们都会监控他们的事件。

We store these in a Public collection so that they remain in scope even after the SendMail procedure runtime is over. This way you can create several emails and they will all have their events monitored.

从那时起， thisMail.Mail 表示在Excel下监视其事件的 MailItem ，并调用此对象上的 .Send 方法（通过VBA）或手动发送电子邮件应该提出 TheMail_Send 事件过程。

From that point on, thisMail.TheMail represents the MailItem whose events are being monitored under Excel, and invoking the .Send method on this object (via VBA) or manually sending the email should raise the TheMail_Send event procedure.

这篇关于EXCEL VBA，手册Outlook电子邮件发件人，类模块问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！