Outlook和VBA之间的追溯链接 [英] Retroactive link between outlook and vba
问题描述
我目前正在使用Access Vba程序，以便自动向人们发送邮件.但是，我们还是选择在Outlook中手动按发送"(以防可能出现问题，因此我们可以事先控制邮件).
有没有一种方法可以建立另一个方向的链接，例如在Outlook中按发送"按钮时，使该人的电子邮件地址恢复为excel? (目标是制作一个历史记录"表，以便跟踪实际发送了哪些邮件以及向谁发送了邮件)
谢谢！
是.一个简单的情况如下所示.这是简单的骨头，展示了您请求的操作.
公共变量addressSent保留收件人"地址.对(由@Rory发送的)邮件进行布尔测试，测试已发送的邮件，并由@Dwipayan Das调用该函数，该函数将打开指定的Excel文件，并将addressSent写入工作表1中的单元格A1.
您可以对此进行修改以适合您的目的.例如.修改该函数以接受文件名作为参数.....
从@ashleedawg的书中记笔记:记住要包含xlApp.Quit行，以免Excel悬空.
我相信您的问题想从Outlook转到Excel，因此这是您将创建的需要关闭的应用程序.
因此在Outlook中运行以下代码:
将其放入标准模块中
Option Explicit
Public addressSent As String
Dim itmevt As New CMailItemEvents
Public Sub CreateNewMessage()
Dim objMsg As MailItem
Set objMsg = Application.CreateItem(olMailItem)
Set itmevt.itm = objMsg
With objMsg
.Display
.To = "somebody@mail.com"
.Subject = "Blah"
addressSent = .To
.Send
End With
End Sub
Public Function openExcel() As Boolean 'Adapted from @Dwipayan Das
Dim xlApp As Object
Dim sourceWB As Object
Dim sourceWS As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.EnableEvents = False
End With
Dim strFile As String
strFile = "C:\Users\User\Desktop\Delete.xlsb" 'Put your file path.
Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True)
sourceWB.Activate
sourceWB.Worksheets(1).Range("A1") = addressSent
End Function
然后在名为 参考文献: I’m currently working on an access Vba program in order to automatically write mails to people. However we chose to still press ‘Send’ in Outlook manually (in case there are possible issues, so we can control the mail beforehand). Is there a way to have a link in the other direction, as in, when pressing the Send button in Outlook, getting the email address of the person back in excel? (The goal would be to make a ‘history’ sheet in order to keep track of which mails were actually sent and to whom) Thank you! Yes. A simple case is shown below. This is bare bones demonstrating the actions you requested. Public variable, You can tinker with this to fit your purposes. E.g. Adapt the function to accept a file name as parameter..... Taking a note from @ashleedawg's book: remember to include a xlApp.Quit line so Excel is not left hanging. I believe your question wanted to go from Outlook to Excel so this is the application that you will have created that needs closing. So in Outlook goes the following code: Put this in a standard module: Then in a class module called References:
这篇关于Outlook和VBA之间的追溯链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！CMailItemEvents的类模块中，从addressSent, holds the To address. A boolean test on mail sent (by @Rory) tests for the mail item having been sent and calls a function, by @Dwipayan Das, that opens a specified Excel file, and writes the addressSent to cell A1 in sheet1.Option Explicit
Public addressSent As String
Dim itmevt As New CMailItemEvents
Public Sub CreateNewMessage()
Dim objMsg As MailItem
Set objMsg = Application.CreateItem(olMailItem)
Set itmevt.itm = objMsg
With objMsg
.Display
.To = "somebody@mail.com"
.Subject = "Blah"
addressSent = .To
.Send
End With
End Sub
Public Function openExcel() As Boolean 'Adapted from @Dwipayan Das
Dim xlApp As Object
Dim sourceWB As Object
Dim sourceWS As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.EnableEvents = False
End With
Dim strFile As String
strFile = "C:\Users\User\Desktop\Delete.xlsb" 'Put your file path.
Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True)
sourceWB.Activate
sourceWB.Worksheets(1).Range("A1") = addressSent
End Function
CMailItemEvents, code from @Rory, put the following:Option Explicit
Public WithEvents itm As Outlook.MailItem
Private Sub itm_Close(Cancel As Boolean)
Dim blnSent As Boolean
On Error Resume Next
blnSent = itm.Sent
If Err.Number = 0 Then
Debug.Print "not sent"
Else
openExcel
End If
End Sub
