Solved

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

Posted on 2010-08-20
11
1,173 Views
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.
0
Comment
Question by:Invent121
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 9

Expert Comment

by:borki
ID: 33483958
I use the

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

function.

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.

HTH

Felix Burkhard

0
 

Author Comment

by:Invent121
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?
0
 
LVL 9

Expert Comment

by:borki
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



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

http://www.microsoft.com/downloads/details.aspx?FamilyID=f1fc413c-6d89-4f15-991b-63b07ba5f2e5&displaylang=en

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.
0
 

Author Comment

by:Invent121
ID: 33484927
Using
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.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 125 total points
ID: 33486163
Try this:

http://filedb.experts-exchange.com/incoming/2010/08_w33/336567/Access--EmailIndividualCustomerR.accdb

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

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 125 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, _

   outputfile:=strReportFile_

   autostart:=True



(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)) & "\"

      Else

         Debug.Print "User pressed Cancel"

         strFolderPath = ""

      End If

   End With



   SelectFolder = strFolderPath

   

ErrorHandlerExit:

   Exit Function



ErrorHandler:

   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, _

            windowmode:=acWindowNormal

         Set rpt = Reports(strReport)

         DoCmd.OutputTo objecttype:=acOutputReport, _

            objectname:=strReport, _

            outputformat:=acFormatPDF, _

            outputfile:=strFileNameAndPath

         

         '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, _

            Type:=olByValue

         

         'For editing before sending

         itm.Display

         

         'For sending automatically

         'itm.Send

         DoCmd.Close objecttype:=acReport, _

            objectname:=strReport, _

            Save:=acSaveNo



NextStudent:

         .MoveNext

      Loop

   End With

   

ErrorHandlerExit:

   Exit Sub



ErrorHandler:

   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

      .MoveFirst

      .MoveLast

      CreateAndTestQuery = .RecordCount

   End With

   

ErrorHandlerExit:

   Exit Function



ErrorHandler:

   If Err.Number = 3021 Then

      CreateAndTestQuery = 0

      Resume ErrorHandlerExit

   Else

   MsgBox "Error No: " & Err.Number _

      & " in CreateAndTestQuery procedure; " _

      & "Description: " & Err.Description

   End If

   

End Function

Open in new window

0
 
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.
0
 

Author Comment

by:Invent121
ID: 33715657
Sorry will test solution  in due course and apply points
0
 
LVL 92

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.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now