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 = "someone@email.com"
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屋!