how to send emails using access vba

DoCmd.SendObject will show a warning pop-up from outlook even if you use EditMessage := False. So you can apply workaround to avoid it. First save the query to you disk and add that file as attachment. This work around can be done programmatically. Try below codes to send mail without any warning pop-up but you must set Programmatic Access to Never warn me about suspicious activity. See this post from Microsoft Answer.

Private Sub CmdSendMail_Click()
Dim strTo As String
Dim strMessage As String
Dim strSubject As String
Dim attch As String

strTo = "[email protected]"
attch = "D:\MyFile.xlsx"
strSubject = "New Lab Charge Codes"
strMessage = "Attached are the New Lab Charge Codes"

    ' Save file to disk.
    DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLSX, attch, False, , , acExportQualityPrint

    Call SendEmailWithOutlook(strTo, strSubject, strMessage, attch)
End Sub

'======= Function to send email =======
Public Function SendEmailWithOutlook( _
    MessageTo As String, _
    Subject As String, _
    MessageBody As String, strAttachment As String)

    ' Define app variable and get Outlook using the "New" keyword
    Dim OutApp As Object
    Dim OutMail As Object  ' An Outlook Mail item
    ' Create a new email object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)

    ' Add the To/Subject/Body to the message and display the message
    With OutMail
        .To = MessageTo
        .Attachments.Add strAttachment
        .Subject = Subject
        .Body = MessageBody
        .Send       ' Send the message immediately
    End With

    ' Release all object variables
    Set OutApp = Nothing
    Set OutMail = Nothing

End Function

To set Programmatic Access you must open outlook as Administrator. Then follow the screenshot below.

enter image description here

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top