excel - Retroactive link between outlook and vba

Question

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!

Answer 1

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:

1. Check to see if an Outlook Email was sent from Excel VBA
2. How can I use Outlook to send email to multiple recipients in Excel VBA
3. How to open an excel file in Outlook vba code
4. Create a new Outlook message using VBA
5. Run code after item sent