Access/Outlook integration... RRS feed

  • Question

  • Hi Everyone,

    I have a question, but first I will lay out the groundwork:

    1. MS Access application running in RDP session (Application.Version=14.0);
    2. MS Outlook in RDP session (Application.Version=14.0) has custom code in VBAProject.OTM to trap Application.ItemSend event when user clicks the SEND button in Outlook to call a SQL stored proc via ADO

    When Access starts, it sets 2 registry keys to tell Outlook to allow it to run macros:

            gblOutlook_OfficeRoot = "HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeVersion]\Outlook"

            strOfficeRoot = Replace(gblOutlook_OfficeRoot & "\LoadMacroProviderOnBoot", "[OfficeVersion]", Application.Version)
            Registry_KeySave strOfficeRoot, "1", "REG_DWORD"
            'set security level to 1 to allow macros
            strOfficeRoot = Replace(gblOutlook_OfficeRoot & "\Security\Level", "[OfficeVersion]", Application.Version)
            Registry_KeySave strOfficeRoot, "1", "REG_DWORD"

    MS Access code:

        Set OutApp = CreateObject("Outlook.Application")

        Set olItem = OutApp.CreateItem(olMailItem)
        With olItem
            .SentOnBehalfOfName = strFrom
            .To = strTo
            .cc = strCC
            .BCC = strBCC
            .Subject = strSubject
            .Body = strBody
            .Send 'does this fire the code behind the outlook email [Send] button??

        End With

    Scenario 1: MS Access is running and MS Outlook is open
    Access code runs, and Application.ItemSend is fired which runs ADO code to call stored proc and then sends the email. This is the desired result.

    Scenario 2: MS Access is running, and MS Outlook is NOT open
    Access code runs but Application.ItemSend is not fired so ADO code is ignored but the email is sent which is NOT the desired result.

    Although both scenarios used to work, Scenario 2 does not work any more.
    As I do not want to open Outlook before starting the access application, is there something else I am missing here to create the desired affect (Scenario 1)

    Yossi Dukes

    Wednesday, January 9, 2019 4:25 PM