VBA Outlook Mail。显示，录制如果手动发送 [英] VBA Outlook Mail .display, recording if sent manually

问题描述

我正在使用VBA从Excel发送电子邮件，并且很难尝试记录邮件是否已发送。我从另一篇文章中获取了一些代码：

链接

我已经按照描述完成了类，放入几个额外的位来看它是否正常工作，它初始化，但是没有别的事情发生 - 即使邮件发送后，类仍然在后台打开，所以我必须在VBE中停止它。 p>

这是调用代码：

  Sub SendProc2（add As String） 
 
设置OutApp = CreateObject（Outlook.Application）
设置OutMail = OutApp.CreateItem（0）
 
关于错误简历Next 
 
 With OutMail 
 .To = add 
 .CC =
 .BCC =
 .Subject = ThisWorkbook.Name 
 .Body = Application.WorksheetFunction .VLookup（Worksheets（Data）。Range（B135），Range（formversion），2，False）_ 
 &安培; 附：& vbCrLf& vbCrLf& ThisWorkbook.Name 
 .Attachments.add ActiveWorkbook.FullName 
 .Display'或使用.Send 
结束
 
 Dim CurrWatcher As EmailWatcher 
 Set CurrWatcher =新的EmailWatcher 
设置CurrWatcher.Mail = OutMail 
 
错误GoTo 0 
 
设置OutMail =没有
设置OutApp =没有
 
卸载UserForm4 
 
 End Sub 
  

这里是类模块代码这被称为EmailWatcher：

  Option Explicit 
 Public WithEvents TheMail As Outlook.MailItem 
 
 Private Sub Class_Terminate（）
 
 Debug.PrintTerminate&现在（）
 
 End Sub 
 
 Private Sub TheMail_Send（Cancel As Boolean）
 
 Debug.PrintSend&现在（）
'在这里输入代码
 
 End Sub 
 
 Private Sub Class_Initialize（）
 
 Debug.PrintInitialize&现在（）
 
 End Sub 
  

从来没有注册过_Send，我认为可能与类对象没有定义或其他东西有关，我有这么多的麻烦，有时我得到警告，在它的初始化，然后立即终止，而不等待_Send，帮助将不胜感激，如果需要更多的信息，请告诉我。

使用Excel 2007，在Windows 7上，我有一个疯狂的本地权威网络

我也不是VBA新手，但我从来没有上过课，做了很多标准模块等。

谢谢，

Dan

解决方案

p>类

 私有WithEvents EM作为Outlook.MailItem 
 
公共子INIT（x作为Outlook） MailItem）
设置EM = x 
 End Sub 
 
私有子EM_Send（取消为布尔值）
 
结束S ub 
  

模块

 code>公共WATCHER作为clsEmailWatch 
 
 Sub EMAIL（）
 
 Dim o As Outlook.Application 
 Dim m As Outlook.MailItem 
 
设置o =新建Outlook.Application 
设置m = o.CreateItem（olMailItem）
 
设置WATCHER =新建clsEmailWatch 
 WATCHER.INIT m 
 
 m.To =[e-mail]
 
 m.Send 
 
 End Sub 
  

希望这有助于

I'm sending emails from Excel using VBA, and having a very hard time of attempting to record whether the message has been sent or not. I have taken some code from another post on here:

Link

I have created the class exactly as described, and put in a few extra bits to see if it is working, it initializes, but then nothing else happens - even after the mail is sent, the class remains open in the background somehow, so I have to stop it in the VBE.

Here is the calling code:

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

and here is 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

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, I've had loads of trouble with this, sometimes I get warnings, at the moment it is Initializing, then Terminating immediately without waiting for the _Send, Help would be appreciated, and also if any more information is needed then let me know.

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

I'm also not new to VBA, but I've never done classes before, done lots of standard modules etc.

Thanks,

Dan

解决方案

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

Module

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 = "[e-mail]"

m.Send

End Sub

Hope this helps

这篇关于VBA Outlook Mail。显示，录制如果手动发送的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！