Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to send an email with a pdf attachment in Microsoft Access 2007

Posted on 2010-08-20
Medium Priority
Last Modified: 2013-11-28
How can I send a report as an pdf attachment to multiple recipients. I'm using a table as my source data and then using an Access report.
Question by:Invent121
  • 3
  • 2
  • 2
  • +3

Expert Comment

ID: 33483958
I use the

   DoCmd.OutputTo acOutputReport, "MyReport", acFormatPDF, FileName, False


There is a slight disadvantage with this function, as you can not pass any parameters, which I frequently use with DoComd.OpenReport. To get around this, I use a ccouple of global parameters that I reference in the report.

Once the file is saved on your disk, you can use your preferred email function to send the PDF as an attachment. Since we use Outlook, I use the Outlook Redemption library (www.dimastr.com/redemption/), which is free for most uses. It gives you very good control over the sending process and you end up with the email in your sent items.


Felix Burkhard


Author Comment

ID: 33484180
I think you may need to expand on this for me as my VB skills are basic.

What code would I use and how to I include the parameters in my report?

Can I also used this CMD

DoCmd.SendObject acSendReport,

If so how to I set the parameters?

Expert Comment

ID: 33484611
I don't use the SendObject / Sendreport command, so can't comment on this.

With regards to the parameter to pass (as the OutputTo does not include an ption to pass a where statement or OpenArgs), I typically create a invisable form field on the form where I invoke my report from. In the report I reference the form field with SQL code such as

SELECT <fieldlist> FROM <table> WHERE (criteria = forms![myform]![myhiddenfield])

You can reference any field that you have in a form for that purpose.

Off to bed in AUS... Good luck

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 85
ID: 33484642
First, in order to save as a PDF, you'll need this addin:


You may already have this, but if not download and install this.

You can then do this:

DoCmd.SendObject acSendReport, "MyReportName", acFormatPDF, "someone@somewhere.com", , , "My Subject", "My Body", True

Obviously, you'd need to change the tokens in that string to valid ones.
Regarding Redemption:
<Since we use Outlook, I use the Outlook Redemption library (www.dimastr.com/redemption/), which is free for most uses.>

Redemption is definitely not "free for most uses". In fact, it is ONLY free for development purposes. If you deploy that application to ANYONE - even users in your own organization - you must purchase a deployment license. See this direct quote from the website:

"If you plan to include Redemption with a commercial product, you must purchase a distributable version which you can then use for any and all of your products. The cost of the distributable version is $199.99, there are no royalties of any kind. You only need to buy a single license, no matter how many of your products use Redemption or how many copies of your application you distribute. Note that the definition of commercial software also includes internal applications whether they are available to the public or not."
The emphasis on that is mine, but please read that carefully. The way I read it, and the way Dimitri has described this in other venues, is that you can install the Redepmtion product ON YOUR WORKSTATION only. If you wish for other endusers to be able to utilize the code you create, then you must purchase a distribution license.

FWIW, I use vbMAPI from www.everythingaccess.com. It requires no additional deployment (everything is included in your Access file), and has been very reliable for me. It does all that Redemption can do at less than half the cost.

Author Comment

ID: 33484927
DoCmd.SendObject acSendReport, "MyReportName", acFormatPDF, "someone@somewhere.com", , , "My Subject", "My Body", True

This report sends the whole report to one user. I need to split my report to different email addresses and split by DocumentNo.

LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 33486163
Try this:


You will have to study and test it carefully, and adapt it to your database
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 500 total points
ID: 33491860
I like to use Automation code, since it is more flexible that the SendObject method.  Here are some code samples.  The first sends a single report; the second creates a filtered report for each record in a query, and sends the filtered report to the appropriate email address:
strReport = "rptCurrentData"
strSelectedPath = SelectFolder()
strReportFile = strSelectedPath & "Current Data.pdf"

DoCmd.OutputTo objecttype:=acOutputReport, _
   objectname:=strReport, _
   outputformat:=acFormatPDF, _

(the autostart option opens the file in Acrobat)
Public Function SelectFolder() As String
'Requires Office XP (2002) or higher
'Requires a reference to the Microsoft Office Object Library
'Created by Helen Feddema 3-Aug-2009
'Last modified 3-Aug-2009

On Error GoTo ErrorHandler

   Dim strFolderPath As String
   Dim fd As Office.FileDialog
   Dim strPath As String

   'Create a FileDialog object as a Folder Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)

   'Set strPath to the folder you want to open initially
   strPath = "G:\Data"

   With fd
      .Title = "Browse for folder where _________ are located"
      .ButtonName = "Select"
      .InitialView = msoFileDialogViewDetails
      '.InitialFileName = strPath
      If .Show = -1 Then
         strFolderPath = CStr(fd.SelectedItems.Item(1)) & "\"
         Debug.Print "User pressed Cancel"
         strFolderPath = ""
      End If
   End With

   SelectFolder = strFolderPath
   Exit Function

   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Function
Option Compare Database
Option Explicit

Private dbs As DAO.Database
Private qdf As DAO.QueryDef
Private rst As DAO.Recordset

Public Sub SendInterventionEmails()
'Created by Helen Feddema 10-Jan-2010
'Last modified by Helen Feddema 10-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim rstIntervention As DAO.Recordset
   Dim lngCount As Long
   Dim lngID As Long
   Dim rpt As Access.Report
   Dim strFileName As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strTitle As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   Dim strEmailSource As String
   strEmailSource = "qryInterventionEmail"
   strRecordSource = "qryMissingAssignments"
   strQuery = "qryMissingAssignmentsSingleStudent"
   Set dbs = CurrentDb
   Set rstIntervention = dbs.OpenRecordset(strEmailSource)
   strCurrentPath = Application.CurrentProject.Path & "\"
   'Use path selected with SelectFolder procedure
   'strCurrentPath = SelectFolder()

   With rstIntervention
      Do While Not .EOF
         lngID = ![StID]
         Debug.Print "Processing Student ID " & lngID
         strFileName = "Intervention Report for " & ![StFirst] _
            & " " & ![StLast] & ".pdf"
         strFileNameAndPath = strCurrentPath & strFileName
         'Create filtered query
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[StID] = " & Chr(39) & lngID & Chr(39) & ";"
         Debug.Print "SQL for " & strQuery & ": " & strSQL
         lngCount = CreateAndTestQuery(strQuery, strSQL)
         Debug.Print "No. of items found: " & lngCount
         If lngCount = 0 Then
            GoTo NextStudent
         End If
         'Open report with filtered query record source
         strReport = "rptMissingAssignmentsNew"
         DoCmd.OpenReport ReportName:=strReport, _
            View:=acViewPreview, _
         Set rpt = Reports(strReport)
         DoCmd.OutputTo objecttype:=acOutputReport, _
            objectname:=strReport, _
            outputformat:=acFormatPDF, _
         'Create email
         Set itm = appOutlook.CreateItem(olMailItem)
         itm.Subject = "MISSING WORK"
         itm.Body = "The attached file lists your missing assignments"
         itm.To = ![Email]
         itm.Attachments.Add Source:=strFileNameAndPath, _
         'For editing before sending
         'For sending automatically
         DoCmd.Close objecttype:=acReport, _
            objectname:=strReport, _

   End With
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in SendInterventionEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified by Helen Feddema 10-Jan-2010

On Error Resume Next
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      CreateAndTestQuery = .RecordCount
   End With
   Exit Function

   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
End Function

Open in new window

LVL 31

Expert Comment

by:Helen Feddema
ID: 33491861
If you use the single report example, after outputting it to a PDF, you would then email it as in the second example.

Author Comment

ID: 33715657
Sorry will test solution  in due course and apply points
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34118941
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question