Expert guide

Send Email from Microsoft Access with Outlook VBA

HTML emails built from your records, with PDF reports attached and every send logged

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: for simple cases, DoCmd.SendObject emails a report through the default mail client. For control, automate classic Outlook: CreateObject("Outlook.Application"), CreateItem(0), set To, Subject and HTMLBody from your record, attach a PDF exported with DoCmd.OutputTo, then .Display, .Save or .Send, and log each message in a table. The new Outlook for Windows does not support this automation; use SMTP or Microsoft Graph there. We build email workflows as part of Access VBA development.

Invoices, statements, appointment reminders, job completion notices, overdue chasers: much of the email an operations team sends is built from data already in Access. Copying it into Outlook by hand is slow and error-prone. A small VBA module turns each record into a properly formatted email, with the right attachment, in seconds.

Option 1: DoCmd.SendObject

DoCmd.SendObject acSendReport, "rptStatement", acFormatPDF, _
    To:="client@example.com", Subject:="Your statement", _
    MessageText:="Please find your statement attached.", EditMessage:=True

It is quick, but it sends the whole report (not one customer's page) unless the report filters itself, gives no HTML formatting, and depends on the default mail client. Fine for internal one-offs; limited for customer emails.

Option 2: Outlook Automation

Public Function SendRecordEmail(ByVal toAddr As String, ByVal subject As String, _
                                ByVal htmlBody As String, _
                                Optional ByVal attachPath As String = "", _
                                Optional ByVal mode As String = "Display") As Boolean
    On Error GoTo Fail
    Dim ol As Object, mail As Object
    Set ol = CreateObject("Outlook.Application")      ' requires classic Outlook
    Set mail = ol.CreateItem(0)                       ' 0 = olMailItem
    With mail
        .To = toAddr
        .Subject = subject
        .HTMLBody = htmlBody
        If Len(attachPath) > 0 Then .Attachments.Add attachPath
        Select Case mode
            Case "Send": .Send
            Case "Draft": .Save
            Case Else: .Display
        End Select
    End With
    SendRecordEmail = True
    Exit Function
Fail:
    Debug.Print "Email failed to " & toAddr & ": " & Err.Description
End Function

Building the HTML Body From a Record

Private Function HtmlEsc(ByVal s As Variant) As String
    s = Nz(s, "")
    s = Replace(s, "&", "&amp;"): s = Replace(s, "<", "&lt;"): s = Replace(s, ">", "&gt;")
    HtmlEsc = s
End Function

Public Function InvoiceEmailBody(ByVal invoiceID As Long) As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT i.InvoiceNo, i.DueDate, i.Total, c.ContactName " & _
        "FROM tblInvoices AS i INNER JOIN tblCustomers AS c ON i.CustomerID = c.CustomerID " & _
        "WHERE i.InvoiceID = " & invoiceID)
    InvoiceEmailBody = _
        "<p>Hi " & HtmlEsc(rs!ContactName) & ",</p>" & _
        "<p>Please find invoice <b>" & HtmlEsc(rs!InvoiceNo) & "</b> attached, for " & _
        Format(rs!Total, "$#,##0.00") & ", due " & Format(rs!DueDate, "d mmmm yyyy") & ".</p>" & _
        "<p>Thank you for your business.</p>" & _
        "<p>Accounts Team<br>BrightCare Equipment</p>"
    rs.Close
End Function

Escaping values matters: a company name like "Smith & Sons" breaks HTML if inserted raw. Keep the wording in a templates table if non-developers need to change it.

Attaching One Record's Report as PDF

Public Function ExportInvoicePdf(ByVal invoiceID As Long, ByVal invoiceNo As String) As String
    Dim path As String
    path = Environ("TEMP") & "\Invoice_" & invoiceNo & ".pdf"
    DoCmd.OpenReport "rptInvoice", acViewPreview, , "InvoiceID=" & invoiceID, acHidden
    DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, path, False
    DoCmd.Close acReport, "rptInvoice", acSaveNo
    ExportInvoicePdf = path
End Function

Opening the report hidden with a WhereCondition before OutputTo makes the PDF contain only that invoice. More on this in exporting Access reports to PDF with VBA.

Batch Sending With a Log

Public Sub EmailUnsentInvoices()
    Dim rs As DAO.Recordset, pdf As String, ok As Boolean
    Set rs = CurrentDb.OpenRecordset( _
        "SELECT i.InvoiceID, i.InvoiceNo, c.Email FROM tblInvoices AS i " & _
        "INNER JOIN tblCustomers AS c ON i.CustomerID = c.CustomerID " & _
        "WHERE i.EmailedOn Is Null AND c.Email Is Not Null", dbOpenDynaset)
    Do While Not rs.EOF
        pdf = ExportInvoicePdf(rs!InvoiceID, rs!InvoiceNo)
        ok = SendRecordEmail(rs!Email, "Invoice " & rs!InvoiceNo, _
                             InvoiceEmailBody(rs!InvoiceID), pdf, "Draft")
        CurrentDb.Execute "INSERT INTO tblEmailLog (InvoiceID, SentTo, SentOn, Result) VALUES (" & _
            rs!InvoiceID & ", '" & Replace(rs!Email, "'", "''") & "', Now(), '" & _
            IIf(ok, "OK", "FAILED") & "')", dbFailOnError
        If ok Then CurrentDb.Execute "UPDATE tblInvoices SET EmailedOn = Now() " & _
                                     "WHERE InvoiceID = " & rs!InvoiceID, dbFailOnError
        rs.MoveNext
        DoEvents
    Loop
    rs.Close
End Sub

Start with "Draft" so messages land in Outlook's Drafts folder for review, then switch to "Send" once you trust the output. The log and EmailedOn field stop duplicate sends.

Choosing a Sending Method

MethodProsCons
DoCmd.SendObjectOne line, no referencesLittle control, whole objects, plain text
Classic Outlook automationFull control, uses the user's mailbox and signatureNeeds classic Outlook; security prompts on some setups
SMTP (CDO or a library)No Outlook needed; works on serversCredentials and modern auth handling; not in Sent Items
Microsoft Graph APIModern, works with new Outlook and Microsoft 365App registration and OAuth setup

Troubleshooting

  • ActiveX component can't create object: classic Outlook is not installed, or new Outlook is the only client.

  • Security prompt 'A program is trying to send an email': check antivirus status or the programmatic access policy.

  • Attachment missing: the PDF path is wrong, or the export happened after the email was created.

  • Emails stuck in Outbox: Outlook must be open and online to send; with .Send, Outlook may close before sending if it was started by the code.

AI can help draft the wording of reminders and follow-ups from record facts, as shown in personalized emails with AI. To process incoming mail into Access rather than send it, see classifying emails into Access with AI.

FAQ

Email from Access FAQ

Outlook automation, attachments and alternatives

Two common ways: DoCmd.SendObject, which sends an object such as a report as an attachment through the default mail client with little control, and Outlook automation from VBA, which creates a MailItem with full control over recipients, subject, HTML body and attachments, and can display or send it.

No. The new Outlook for Windows does not support COM automation or VBA, so code using CreateObject("Outlook.Application") needs classic Outlook installed. Alternatives are sending through SMTP or through Microsoft Graph with a registered application.

Outlook's programmatic access protection warns when an external program tries to send mail or read addresses, typically when antivirus status is not reported as current. With up-to-date antivirus recognised by Windows, the prompt normally does not appear. Administrators can control the setting through policy.

Export the report first with DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, path, then add that file with MailItem.Attachments.Add path. Filter the report to one record by opening it hidden with a WhereCondition before exporting.

Use .Display while testing and for messages a person should check, such as customer quotes. Use .Send for routine, well-tested notifications. Many teams save as drafts with .Save so a person can review a batch before sending.