How To Send Email With Access And Outlook VBA

There are 3 things you probably want to send from your Access application. You probably want to send an Access report or send an attachment(s).

1. Email An Access Report (SendObject)
2. Email An Access Report With Attachments (Outlook Automation)
3. Email With Attachment(s) (Outlook Automation)

First, if you are in the database, and want to send an item in the database (like a report), you can do this:

1. Email Access Report With VBA

Sub SendAccessReport()
    'access report
    AccessOutlookEmail "Access Report"
End Sub

Public Sub AccessOutlookEmail(ReportName As String)
    Dim strSubject As String
    Dim strAddresses As String
    Dim strAddressCC As String
    Dim strAddressBCC As String
    Dim strBody As String
    Dim blnDisplayMsg As Boolean
    
    On Error GoTo errhandler

    strSubject = "Test Subject"
    strAddresses = "erik.j.loble@nasa.gov"
    strAddressCC = "cc@test.gov"
    strAddressBCC = "bcc@test.gov"
    
    blnDisplayMsg = "True" 'Gives the ability to preview the message before sending.
    'blnDisplayMsg = "False" 'Automatically sends the message without previewing it.
    
    
    strBody = "This is the body of the email"

    DoCmd.SendObject acSendReport, ReportName, acFormatPDF, strAddresses, strAddressCC, strAddressBCC, strSubject, strBody, blnDisplayMsg
    
    Exit Sub
errhandler:
    MsgBox "Email Not Sent", vbInformation, "Not Sent"
    
    Exit Sub
End Sub

2. Email An Access Report With Attachments (Outlook Automation)

In my opinion, only the paths to the files should be stored in the database, even though Access now offers the cool little “Attachment” data type.

I don’t use it. Store the path to the file in the table cell and then enter the ParentID of which other record it belongs to.

If you want to send the Access report with some attachments, do the following:

1. When you click the “Email” button, export the report as a pdf to the folder that has all the files.
2. Use the next email function to put all the contents in the email “box” then you can send it.

That’s all.

3. Send An Attachment With VBA (with or without attachments using Outlook automation)

This way would work without “SendObject”. First you would have to output your report in PDF format, and then just add it in as an attachment.

Sub SendThis()
    Dim intResult As Integer
    
    'attachment
    SendAttachmentEmail Array("C:\Interface Files\_LOCAL\pdffile.pdf", "C:\Interface Files\_LOCAL\aoc shot.png")
        
    'Sends Outlook Email Without Attachments (not using SendObject)
    'SendAttachmentEmail
    
End Sub

Function SendAttachmentEmail(Optional AttachmentPath As Variant)
    'idea modified from https://www.devhut.net/2010/09/03/vba-outlook-automation/

    'Send Email using late binding to avoid reference issues
    Dim strTo As String
    Dim strBCC As String
    Dim strSubject As String
    Dim strBody As String
    Dim blnEdit As Boolean
    
    Dim objOutlook As Object
    Dim objOutlookMsg As Object
    Dim objOutlookRecip As Object
    Dim objOutlookAttach As Object
    Dim i As Integer
    Const olMailItem = 0
     
    strTo = "erik.j.loble@nasa.gov"
    strBCC = "bcc@domain.com"
    strSubject = "test subject"
    strBody = "test body"
    blnEdit = True
    
    On Error GoTo ErrorMsgs
    
    Set objOutlook = CreateObject("Outlook.Application")
    
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
        Set objOutlookRecip = .Recipients.add(strTo)
        objOutlookRecip.Type = 1
    
        If Not IsMissing(strBCC) Then
            Set objOutlookRecip = .Recipients.add(strBCC)
            objOutlookRecip.Type = 3
        End If
    
        .Subject = strSubject
        .Body = strBody
        .Importance = 2  'Importance Level  0=Low,1=Normal,2=High
        
        ' Add attachments to the message if there are some.
        
        'First check if there are attachments to add (passed into the function):
        If Not IsMissing(AttachmentPath) Then
            'are there multiple attachments?
            If IsArray(AttachmentPath) Then
               For i = LBound(AttachmentPath) To UBound(AttachmentPath) '- 1
                  If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
                    Set objOutlookAttach = .Attachments.add(AttachmentPath(i))
                  End If
               Next i
            Else
                'is there only 1 attachment?
                If AttachmentPath <> "" Then
                    Set objOutlookAttach = .Attachments.add(AttachmentPath)
                End If
            End If
        End If
        
        For Each objOutlookRecip In .Recipients
            If Not objOutlookRecip.Resolve Then
                objOutlookMsg.Display
            End If
        Next
        
        If blnEdit Then
            'edit mode first then send
            .Display
        Else
            'transparent/silent send and
            .Send
        End If
    End With
        
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    Set objOutlookRecip = Nothing
    Set objOutlookAttach = Nothing
    
ErrorMsgs:
   If Err.Number = "287" Then
      MsgBox "You clicked No to the Outlook security warning. " & _
      "Rerun the procedure and click Yes to access e-mail " & _
      "addresses to send your message. For more information, " & _
      "see the document at http://www.microsoft.com/office" & _
      "/previous/outlook/downloads/security.asp."
      Exit Function
   ElseIf Err.Number <> 0 Then
      MsgBox Err.Number & " - " & Err.Description
      Exit Function
   End If
End Function

If you have other ideas, please share them in the comments below.

By the way, if you got or are getting value from the VBA information, please click the "Donate" button to give me a small token of your appreciation, thanks!