Access Report - Preview context menu - Send to Mail Recipient - How to catch this event?

Question

When you preview a report, right-click offers the option, Send to... -> Mail Recipient.

I need to catch this event and execute code the reads the recipient e-mail address as typed into Outlook when it appears. This code should be in a module in the Access database.

If possible, I would also like to read the Subject.

It's hard to find answers on the Web, and despite some experience with Access and VBA, I don't know how to even start.

Edit: From Dmitry's answer I found this loop through Inspectors,

Private Sub Form_Timer()
    Dim myInspectors As Outlook.Inspectors
    Dim x As Integer
    Dim iCount As Integer
    Set myInspectors = Application.Inspectors
    iCount = Application.Inspectors.Count
    If iCount > 0 Then
    For x = 1 To iCount
    MsgBox myInspectors.Item(x).Caption
    Next x
    Else
    MsgBox "No inspector windows are open."
    End If
End Sub

but it gives this compile error:

Edit 2:

I have moved the code into a function, and when Outlook is running, I get no errors from this GetObject call. But with this function on a 2s timer, objApp.Inspectors.Count remains 0 while I compose an email and send it.

Public Function checkInspectors() As Boolean
    Dim myInspectors As Outlook.Inspectors
    Dim OutLookWasNotRunning As Boolean
    Dim objApp As Object
    Set objApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then OutLookWasNotRunning = True
    Err.Clear    ' Clear Err object in case error occurred.

    If Not OutLookWasNotRunning Then
        Set myInspectors = objApp.Inspectors
        Dim x As Integer
        Dim iCount As Integer
        iCount = objApp.Inspectors.Count
        If iCount > 0 Then
            For x = 1 To iCount
                Debug.Print myInspectors.Item(x).Caption
            Next x
            Debug.Print "---"
        Else
            'MsgBox "No inspector windows are open."
        End If
    End If
End Function

I don't think that's one of the available events, meaning it's not possible (if you're not willing to do advanced WinAPI + callback hackery, that is). Instead, you can consider adjusting the menu to add your own buttons that executes VBA. But that's a nontrivial task as well. I do have code lying around to read out and adjust data from a newly created outlook mail item from VBA. — Erik A, Jun 14 '19 at 14:34

score 1 · Answer 1 · answered Jun 15 '19 at 00:47

1

Normally, Application.Inspectors.NewInspector event would fire, but Outlook disables that event for the messages opened through Simple MAPI. Your best bet is to scan the Application.Inspectors collection periodically (timer?) to check if there is a new inspector open.

Once you have an Inspector object, you can check the Inspector.CurrentItem.Recipients collection.

answered Jun 15 '19 at 00:47

Dmitry Streblechenko

62,942
4
53
78

Using this code, but getting the compile error "Method or data member not found" on Application.Inspectors. How do I declare and set a valid Application object in VBA ,or is Inspectors the problem and I need a Reference beyond Outlook 12.0? https://learn.microsoft.com/en-us/office/vba/api/outlook.application.inspectors – Henrik Erlandsson Jun 17 '19 at 09:32
Hm, not sure what you mean. The code that doesn't compile is the code that I link. Anyway, I've put the code and the error in my question! – Henrik Erlandsson Jun 18 '19 at 08:44
1

`Application.Inspectors` is a member of the `Outlook.Application` object, and your `Application` is an `Access.Application` object. As a second barrier, you would need to check if Outlook is open and then get the application object using `GetObject` (or something considerably more complicated if there may be multiple Outlook applications at the same time). That's why I didn't consider this to be a viable option in my answer (though it's arguably more simple than using WinAPI to intercept the button click). – Erik A Jun 18 '19 at 09:15
I modified the code to use GetObject and updated the question. What are your thoughts @ErikA? – Henrik Erlandsson Jun 18 '19 at 12:07
Check task manager, the processes pane on Win 7 or the details pane on win 10. Are there multiple open _outlook.exe_ applications? If so, we need to iterate them all. You can try adapting [this code](https://stackoverflow.com/a/35343847/7296893) to work with Outlook instead of Excel. Not an easy task, and it can be done a bit more simple for Access, but if you have trouble doing that, you can open a separate question and I'll try to answer. – Erik A Jun 18 '19 at 13:09

score 0 · Answer 2 · answered Jun 14 '19 at 14:49

Well, one of the possible solutions is to develop an Outlook add-in or VBA macro which may track outgoing emails. The ItemSend event of the Application class which is fired whenever a Microsoft Outlook item is sent, either by the user through an Inspector (before the inspector is closed, but after the user clicks the Send button) or when the Send method for an Outlook item, such as MailItem, is used in a program.

Access Report - Preview context menu - Send to Mail Recipient - How to catch this event?

2 Answers2