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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
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


Need more help? Click here for another example.

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

Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather
Tags: , , ,