在Excel VBA中捕获Outlook电子邮件发送时间

本文介绍了在Excel VBA中捕获Outlook电子邮件发送时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

每当我在Excel中执行VBA代码时，都会生成Outlook电子邮件.它不会自动发送，也不希望它发送.电子邮件由一个范围内的单元格值填充(基于ActiveCell)，并且当电子邮件手动发送到ActiveCell.Offset(0，13)中时，我想以编程方式捕获，最好使用我当前的Excel程序中的VBA.

An Outlook email is generated whenever I execute a VBA code in Excel. It does not automatically send, nor do I want it to. The email is populated by cell values in a range (which are based off of the ActiveCell) and I want to programmatically capture when the email is manually sent into ActiveCell.Offset(0, 13), preferably with VBA in my current Excel program.

这是显示电子邮件的代码:

This is the code by which I display the email:

'Send Stock Request: Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .BodyFormat = olFormatHTML .HTMLBody = "My eMail's HTML Body" .To = "myrecipients@theiremails" .CC = "" .BCC = "" .Subject = "Stock Request" .Display End With Set OutMail = Nothing Set OutApp = Nothing

推荐答案

可以通过VBA完成，但是下面的代码必须粘贴在Outlook模块而不是Excel的Outlook => ThisOutlookSession模块中.另外，请确保您允许在Outlook中使用宏.

It can be done through VBA, but code below must be pasted in Outlook module instead of Excel, in Outlook=>ThisOutlookSession module. Also, make sure you allow macros in Outlook.

Private Sub Application_ItemSend(ByVal olItem As Object, Cancel As Boolean) Dim Xl As Object ' Excel.Application Dim Wb As Object ' Excel.Workbook Set Xl = GetObject(, "excel.application") Set Wb = Xl.Workbooks("NameOfYourOpenedWorkbook.xlsb") Wb.Activate Xl.activecell.Offset(0, 13).Value = Date & " " & Time End Sub

因此，现在当您手动发送自动创建的电子邮件时，您将在打开的工作簿中的ActiveCell.Offset(0, 13)单元中获取捕获的日期和时间.

So now when you send your automatically created email manually, you will get date and time captured in your opened Workbook in ActiveCell.Offset(0, 13) cell.