CODEWITHSUNDEEP
ms-accessoutlookvbawarnings
Isaac Reefman
Isaac Reefman

Reputation: 597

Get rid of warning message for VBA-sent email

I have an alert sending aid built into an Access database that sends emails to all technicians with an expired/ing induction, all in one go. Every email requires permission to send.

Similar to this question and this question but I need to send multiple emails automatically all at once, without holding up other processes.
The code in the questions seems to send a single email, and the only accepted answer involves waiting five seconds up to three times to check that the actual message had been displayed so that SendKeys would work on that window.

If I use SendKeys with a 5 second pause built in to wait for confirmation as Graham Anderson suggests, this won't speed up the process, only avoid some clicking. The progress bar shown on the warning (below) takes roughly that time to fill, so I'm guessing that's the system building the email, which is what the wait is for.

The same problem applies to Julia's answer to the other question - the warning box won't let you click "Allow" until the progress bar is full, so code that auto clicks this button will have to wait to complete at best.

I tried DoCmd.SetWarnings (False) (and then DoCmd.SetWarnings (True) after sending) but that doesn't stop this particular one. This may be because the warning comes from Outlook rather than Access.

The coding I have:

Private Sub SendAlerts_Click()
    Dim db As Database, rs As Recordset
    Dim Subject As String, Body As String, EmailAddress As String
    Dim Recipient As String, Induction As String, Expiry As String
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryExpiryAlert", dbOpenDynaset)

    Do Until rs.EOF
        EmailAddress = rs!Email
        Recipient = rs!FullName
        Induction = rs!Induction
        Expiry = rs!ExpiryDate
        Subject = "Inductions need renewal"
        Body = "Hi " & Recipient & ", the expiry for your " & Induction & _
            " certificate is " & Expiry & ". Please send a current certificate asap."

        DoCmd.SendObject , , , EmailAddress, , , Subject, Body, False
        
        rs.MoveNext
    Loop
    
    DoCmd.Close
    
End Sub

Here is the warning:
enter image description here

Upvotes: 0

Views: 4199

Answers (1)

kagard
kagard

Reputation: 38

I wrote and compiled an Autohotkey script that looks for the Outlook confirmation dialog and automatically clicks Allow. (I call this from Access VBA and can set how long it run for before shutting itself off.) As you point out, though, there is still a substantial delay waiting for the Allow button to be enabled each time.

Ultimately, our company ended up purchasing Outlook Security Manager. As usual, you have to add an Outlook Security Manager Reference. reference to your project.

It basically has ON and OFF settings, so it's easy to use. If wrote a Sub in a standard module to simplify:

Public Sub SetSecurityManager(State As String)
    ' Turns the Security Manager on or off.
    ' The Security Manager allows the application to
    ' send emails through Outlook without having to
    ' manually confirm each outgoing email.

    Select Case State
        Case "On"
            ' Temporarily turn off Outlook security so we can send email
            Set SecurityManager = CreateObject("AddInExpress.OutlookSecurityManager")
            SecurityManager.DisableOOMWarnings = True
            SecurityManager.DisableSMAPIWarnings = True
        Case "Off"
            'Restore Outlook security to normal
            SecurityManager.DisableSMAPIWarnings = False
            SecurityManager.DisableOOMWarnings = False
            Set SecurityManager = Nothing
        Case Else
    End Select

End Sub

Whenever I need it, I add this line to my code:

SetSecurityManager "On"

It's frustrating to have to pay for a solution to a security problem created by software we purchased, but it's the best answer we've found. I ship between 150 and 225 reports a day and have no problem with OSM.

Thanks,

Keith

Upvotes: 1

Related Questions