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

问题描述

我仍在解决我在中描述的问题关于这个主题的第一个问题.对于简短的刷新，它是一个 excel 文件，其中包含电子邮件模板和附件的列表，我向每个列表单元添加了打开给定单元模板的按钮进行一些更改，然后附加文件并将邮件显示到用户.用户可以根据需要修改邮件，然后发送或不发送邮件.我尝试了下面描述的几种方法.不幸的是，我现在在 class 模块的问题上停滞不前，它简短地描述了 这里.我确实创建了一个类模块，例如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

对以下表格的更改不会产生任何变化:

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 事件.该课程将在 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:

像这样执行Class模块，去掉不必要的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.TheMail 代表 MailItem 其事件正在 Excel 下被监视，并在上调用 .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屋！