每当我在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.
更多推荐
在Excel VBA中捕获Outlook电子邮件发送时间
发布评论