如何从Excel应用程序中捕获Outlook事件 [英] How to Trap Outlook Events from Excel Application

问题描述

我有一本工作簿，至少有15位使用者定期使用和更新，内容包含客户信息，内容为H3：H1500。使用Worksheet_FollowHyperlink事件，我们可以通过预先写入的Outlook帐户发送电子邮件，并依赖于一周中的某个星期请求订单（M-F，星期六和星期日），并且代码可以正常生成消息。我的主要问题是跟踪客户端的响应。每当H列中的超链接被选中时，我尝试使用一个记录日期的日期（NOW函数）和Environ（username），但是因为我有电子邮件子设置为.Display（所以人们可以进行任何最后一分钟的调整，如果需要），它只记录谁选择了超链接（当事件从来没有真正发送时，这显然发生了很多事故）。我在这个论坛和其他人中发现了几个线程，引用了创建一个Class模块，并且实现了一个用于查看它是否可以在我的代码中使用的线程，但是通过添加它，整个电子邮件子被渲染无效，所以我回到旧的形式。由于我在VBA方面经验不足（我得到了这么多的帮助和尝试和错误），我意识到我的一些代码选择可能看起来很愚蠢，如果有更好的方法来做到这一点，我可以开放它 - 我只是知道，这张表现在主要是，我希望如果可能，它可以改进。

我目前的电子邮件是：

  Private Sub Worksheet_FollowHyperlink（ByVal目标为超链接）
 Dim Body1，Body2，Body3 As String 
 Dim olApp As Outlook.Application 
 Dim OlMail As Outlook.MailItem 
 
 On Error Resume Next 
 Application.EnableEvents = False 
 
设置olApp = GetObject（，Outlook.Application）
 
尽管olApp.Inspectors.Count = 0 
 DoEvents 
 
循环
 
设置olMail = olApp.Inspectors.Item（1）.CurrentItem 
 
使用olMail 
 
 Body1 =这是我的工作日文本
 Body2 =这是我的星期六文本
 Body3 =这是我的星期天文本
 
 .Subject =主题
 .Attachemnts.AddC：\Path
 .CC = Target.Range.Offset（0,4）.Text 
 .BCC =
 
如果Target。 Range.Offset（0,5）.Text =否然后
 .Body1 
如果Target.Range.Offset（0,5）.Text =是然后
 .Body2 
如果Target.Range.Offset（0,5）.Text =Sunday然后
 .Body3 
 
 .Displa y 
结束
 
转发：
 Application.EnableEvents = True 
退出子
停止：
 MsgBox Err.Description 
继续转发
 End Sub 
  

[以上代码在Excel VBE中，以下代码在Outlook VBE，我应该包括在开始之前 - 它现在正在为我工​​作正常，所以我不知道为什么它不编译...]

 函数GetCurrentItem（）As Object 
 Dim objApp As Application 
 
设置objApp = CreateObject（Outlook.Application）
打开错误Resume Next 
选择案例TypeName（objApp.ActiveWindow）
案例资源管理器
设置GetCurrentItem = objApp.ActiveExplorer.Selection.Item（1）
案例检查器
设置GetCurrentItem = objApp.ActiveInspector.CurrentItem 
结束选择
设置objApp = Nothing 
结束函数
  

任何帮助不胜感激！

解决方案

你正在努力工作甚至在outlook中，从Excel线程，真的很有趣的Q，我不知道是否可能。我想这将让你开始。

我希望能够跟踪用户和访问该邮件超链接的人的日期，实际上发了

PROBLEM：超链接正在打开另一个应用程序（Outlook）控制。至少从VBA方面来看，您无法控制Outlook事件。

我以为可能会有一个更简单的方法来解决一个解决方案，但这是一个死胡同，你暗示了类对象，所以我想我有一个想法可能会工作，从来没有这样做，所以这是一个正在进行的工作。

为了解决这个问题，我解决了一个方法： strong>

1. 杀死超链接，使其不会自动启动Outlook


2. 使用 SelectionChange 事件通过VBA发送邮件，而不是 FollowHyperlink 事件


3. 创建一个用于Outlook MailItem的自定义事件处理程序类对象，它将捕获 _Send 事件，然后您可以使用该事件来记录发送的详细信息。

以下是代码/说明：

创建一个名为 cMailItem 并将此代码放在其中：

  Option Explicit 
'MailItem事件处理程序c lass 
 Public WithEvents m As Outlook.MailItem 
 
 Public Sub Class_initialize（）
 
设置m = olApp.CreateItem（0）
 
 End Sub 
 
 Private Sub m_Send（Cancel As Boolean）
 
 Debug.Print项目由& Environ（用户名）& at&现在（）
调用ReleaseTrap 
 
 End Sub 
  

标准代码模块（我称之为 HelperFunctions ，但名称无关紧要）放这个代码，这将为我们的 cMailItem 事件处理程序类，并且还包含返回Outlook应用程序实例的函数。

  Option Explicit 
'################ 
'注意：初始化窗体时应调用TrapEvents 
'注意：当表单被关闭时，应该调用ReleaseTrap 
公共olApp作为Outlook.Application 
公共cMail作为新的cMailItem 
公共TrapFlag作为布尔
 
 Sub TrapEvents（）
如果不是TrapFlag然后
设置olApp = GetApplication（Outlook.Application）
 TrapFlag = True 
如果
结束Sub 
 
 Sub ReleaseTrap（）
如果TrapFlag = True然后
设置olApp = Nothing 
设置cMail = Nothing 
 TrapFlag = False 
 End If 
 End Sub 
 
函数GetApplication（Class As String）As Object 
'处理创建/获取应用程序类的实例
 Dim ret As Object 
 
 On Error Resume Next 
 
 Set ret = GetObject（，Class）
如果Err.Number<> 0然后
设置ret = CreateObject（Class）
如果
 
设置GetApplication = ret 
 
错误GoTo 0 
 
结束功能
  

现在，问题的一部分是超链接跟随的方式优先于其他事件。为了避免这种情况，我使用一些代码来杀死超链接。他们将链接到它们所在的单元格，但它们仍然包含电子邮件地址的文本。

而不是使用 FollowHyperlink 事件，我使用 SelectionChange 事件调用另一个发送邮件的过程。

在您的WORKSHEET模块中，放置以下事件处理程序和 SendMail 过程：

  Option Explicit 
 
 Private Sub Worksheet_Activate（）
'转换Mailto超链接，以便它们不会
'自动打开Outlook MailItem 
 
 Dim h作为超链接
 
对于每个h在ActiveSheet.Hyperlinks 
如果h.Address喜欢mailto：*然后
 h.ScreenTip = h.Address 
 h。地址=
 h.SubAddress = h.Range.Address 
如果
 
下一个
 
 End Sub 
 
 Private Sub Worksheet_SelectionChange（ByVal Target As Range）
'禁用Excel事件s 
 Application.EnableEvents = False 
 
如果Target.Cells.Count<> 1然后GoTo EarlyExit 
如果Target.Hyperlinks.Count<> 1然后GoTo EarlyExit 
 
'发送邮件到指定的收件人/ etc。 
调用SendMail（目标）
 
 EarlyExit：
'重新启用事件：
 Application.EnableEvents = True 
 
 End Sub 
 Private Sub SendMail（Target As Range）
 
 Dim Body1 $，Body2 $，Body3 $ 
 Dim OlMail As Outlook.MailItem 
 Const OLMAILITEM As Long = 0 
 
'设置我们的Outlook事件陷阱
调用TrapEvents 
 
'创建mailitem 
设置OlMail = cMail.m 
 
使用OlMail 
 
 Body1 =这是我的工作日文本
 Body2 =这是我的星期六文本
 Body3 =这是我的星期天文本
 
 .To = Target.Text 
 .Subject =Subject
'.Attachemnts.AddC：\Path
 .CC = Target.Offset（0，4）.Text 
 .BCC =
 
。显示
结束
 
 
 End Sub 
  / pre> 
 
 
 修改后的答案 
 
 
 
我从原始解决方案它使用了一个Outlook应用程序事件处理程序类，这是一个限制因为它会陷入任何 item_send事件，这是有问题的，因为多任务用户会发送假阳性。修改后的解决方案使用在运行时创建的 MailItem 对象的事件处理程序，并应避免陷入困境。
 
 
 
 可能是其他限制 
 
 
 
例如，此方法不会真正处理多个电子邮件，因此，如果用户单击一个链接，然后另一个，只有一个电子邮件存在和可以跟踪。如果您需要处理多个电子邮件，请使用此类对象的public  Collection ，这是我为这个类似的问题。
 
 
 
正如我所说，这是我第一次尝试使用 WithEvents 两个应用程序之间的处理程序。我在单应用程序加载项等中使用了主题，但是从来没有以这种方式绑定两个应用程序，所以这是我未知的领域。
 
I have a workbook that at least 15 people use and update periodically that contains client information with emails within column H3:H1500.  Using the Worksheet_FollowHyperlink event, we can send emails through our Outlook accounts that are pre-written and dependent upon what day of the week an order is requested (M-F, Saturday and Sunday) and the code works just fine to generate messages.  My main problem is in tracking responses to clients.  I tried having a sub that recorded date (NOW function) and Environ("username") whenever the hyperlink within column H was selected, but as I have the e-mail sub set to .Display (so people can make any last minute adjustments, if needed) it only records who selected the hyperlink (which, apparently happens a lot on accident when the message is never actually sent).  I had found several threads throughout this forum and others that reference creating a Class module and I implemented one that was used to see if it would work in my code, but by adding it, the entire email sub was rendered useless so I reverted back to the old form. As I am not extremely experienced in VBA (I have gotten this far due to help and trial and error), I realize that some of my choices of code may seem silly, and if there are better ways to do this, I am open to it - I just know that, this sheet works mostly for now and I hope it can be improved, if possible.   

My current email sub is:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim Body1, Body2, Body3 As String
Dim olApp As Outlook.Application
Dim OlMail As Outlook.MailItem

On Error Resume Next
Application.EnableEvents = False

Set olApp = GetObject(,"Outlook.Application")

Do While olApp.Inspectors.Count = 0
DoEvents

Loop

Set olMail = olApp.Inspectors.Item(1).CurrentItem

With olMail

Body1 = "This is my weekday text"
Body2 = "This is my Saturday text"
Body3 = "This is my Sunday text"

.Subject = "Subject"
.Attachemnts.Add "C:\Path"
.CC = Target.Range.Offset(0,4).Text
.BCC = ""

If Target.Range.Offset(0,5).Text = "No" Then
.Body1
If Target.Range.Offset(0,5).Text = "Yes" Then
.Body2
If Target.Range.Offset(0,5).Text = "Sunday" Then
.Body3

.Display
End With

forward:
Application.EnableEvents = True
Exit Sub
halt:
MsgBox Err.Description
Resume forward
End Sub
[The above code is in the Excel VBE, the following code is in the Outlook VBE, I should have included that before starting - it is working fine for me right now, so I am not sure why it is not compiling...]
Function GetCurrentItem() As Object
Dim objApp As Application

Set objApp = CreateObject("Outlook.Application")
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select
Set objApp = Nothing
End Function
Any help is appreciated!
解决方案
You are trying to work with events in outlook, from an Excel thread, really really interesting Q and I didn't know if it would be possible.  I think this will get you started.

  
I am hoping to be able to track the user and date of who accessed the email hyperlink and actually sent it. 
PROBLEM: The hyperlink is opening another application (Outlook), over which you don't have full control. And at least from the VBA side, you do NOT have control over the Outlook events. 

I thought there may be an easier way to hack around a solution but that was a dead end, you had hinted at class object, so I figured I had an idea that might work... never done this before though, so it's a work in progress.

To solve this, I settle on an approach that does:

Kills the hyperlinks so that they don't automatically launch Outlook
Use the SelectionChange event to send the mail via VBA rather than the FollowHyperlink event
Create a custom event handler class object for an Outlook MailItem which will trap the _Send event, which you can then use to log the details of the send.
Here are the codes/instructions:

Create a class object called cMailItem and put this code inside it:
Option Explicit
'MailItem event handler class
Public WithEvents m As Outlook.MailItem

Public Sub Class_initialize()

    Set m = olApp.CreateItem(0)

End Sub

Private Sub m_Send(Cancel As Boolean)

        Debug.Print "Item was sent by " & Environ("Username") & " at " & Now()
        Call ReleaseTrap

End Sub
In a STANDARD code module (I call this one HelperFunctions but the name doesn't matter) put this code, which will set a flag for our cMailItem Event Handler class and also contains the function which returns the instance of Outlook Application.
Option Explicit
'#################
'NOTE: The TrapEvents should be called when the Forms are initialized
'NOTE: The ReleaseTrap should be called when the Forms are closed
Public olApp As Outlook.Application
Public cMail As New cMailItem
Public TrapFlag As Boolean

Sub TrapEvents()
If Not TrapFlag Then
   Set olApp = GetApplication("Outlook.Application")
   TrapFlag = True
End If
End Sub

Sub ReleaseTrap()
If TrapFlag = True Then
   Set olApp = Nothing
   Set cMail = Nothing
   TrapFlag = False
End If
End Sub

Function GetApplication(Class As String) As Object
'Handles creating/getting the instance of an application class
Dim ret As Object

On Error Resume Next

Set ret = GetObject(, Class)
If Err.Number <> 0 Then
    Set ret = CreateObject(Class)
End If

Set GetApplication = ret

On Error GoTo 0

End Function
Now, part of the problem is the way that the hyperlink follow takes precedence over other events. To avoid that, I use some code to "kill" the hyperlinks. They will "link" only to the cell wherein they reside, but they will still contain the text for the email address.

Instead of using the FollowHyperlink event, I use the SelectionChange event to call another procedure which sends the mail.

In your WORKSHEET module, put the following event handlers AND the SendMail procedure:
Option Explicit

Private Sub Worksheet_Activate()
'Converts Mailto hyperlinks so that they do NOT
' automatically open Outlook MailItem

    Dim h As Hyperlink

    For Each h In ActiveSheet.Hyperlinks
        If h.Address Like "mailto:*" Then
            h.ScreenTip = h.Address
            h.Address = ""
            h.SubAddress = h.Range.Address
        End If

    Next

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Disable Excel events
Application.EnableEvents = False

    If Target.Cells.Count <> 1 Then GoTo EarlyExit
    If Target.Hyperlinks.Count <> 1 Then GoTo EarlyExit

    'Send mail to the specified recipient/etc.
    Call SendMail(Target)

EarlyExit:
'Re-enable events:
Application.EnableEvents = True

End Sub
Private Sub SendMail(Target As Range)

Dim Body1$, Body2$, Body3$
Dim OlMail As Outlook.MailItem
Const OLMAILITEM As Long = 0

'Set our Outlook event trap
Call TrapEvents

'CREATE the mailitem
Set OlMail = cMail.m 

With OlMail

    Body1 = "This is my weekday text"
    Body2 = "This is my Saturday text"
    Body3 = "This is my Sunday text"

    .To = Target.Text
    .Subject = "Subject"
    '.Attachemnts.Add "C:\Path"
    .CC = Target.Offset(0, 4).Text
    .BCC = ""

    .Display
End With


End Sub
NOTE ON REVISED ANSWER

I revised this from the original solution which used an Outlook Application event handler class, which was limited by the fact that it would trap ANY item_send event, this was problematic because multi-tasking users would send false positives.  The revised solution uses an event handler for the MailItem object which is created at run-time, and should avoid that pitfall.

THERE MAY BE OTHER LIMITATIONS

For example, this method does not really handle "multiple" emails, so if the user clicks one link, and then another, there will only be ONE email that exists and can be tracked. If you need to handle multiple emails, use a public Collection of this class object, which I did for this similar question.

As I said, this is the first time I've ever attempted to use a WithEvents handler between two applications. I've used theme in single-application Add-Ins, etc., but never binding two applications in this manner, so it's uncharted territory for me.

                        
这篇关于如何从Excel应用程序中捕获Outlook事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！