Yes. A simple case is shown below. This is bare bones demonstrating the actions you requested.
Public variable, 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.
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:
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 = "[email protected]"
.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:UsersUserDesktopDelete.xlsb" 'Put your file path.
Set sourceWB = xlApp.Workbooks.Open(strFile, , False, , , , , , , True)
sourceWB.Activate
sourceWB.Worksheets(1).Range("A1") = addressSent
End Function
Then in a class module called 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
References:
- Check to see if an Outlook Email was sent from Excel VBA
- How can I use Outlook to send email to multiple recipients in Excel VBA
- How to open an excel file in Outlook vba code
- Create a new Outlook message using VBA
- Run code after item sent
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…