How to create emails for multiple recipients using data stored in an Access table, and attach general reports.
Access Archon #135
Introduction
This article combines techniques from some previous Access Archon columns to create emails to multiple recipients using data stored in an Access table, and attaching either a saved report snapshot or a report saved as an Adobe PDF file to the outgoing email. The reports in this sample database are general reports; a future article will deal with creating a custom report for each contact.
The frmEMailMerge form has a multi-select listbox to select contacts; after selecting one or more contacts, when you click the Create Recipient String button, the selected contacts’ email addresses are concatenated into a string for use in the email’s To field. The cboSelectReport combo box lets you select a report to send as an attachment to the email, and the fraReportFormat option group gives you a choice of Snapshot or PDF as the report format.
The Snapshot format is an Access snapshot (.snp file), which can be viewed by recipients who have Access, or if they don’t have Access, have the Snapshot Viewer. The Adobe PDF format requires only the Adobe Reader, which almost everybody has, or if not, can download from the AdobeWeb site. Both formats preserve the appearance of the report perfectly, but each has its advantages and disadvantages. The snapshots can be created using the OutputTo method, to a specified folder, and they are created quickly and automatically. However, if you send one to a recipient who doesn’t have either Access or the Snapshot Viewer, they won’t be viewable. Adobe PDF files are more universally viewable, but they require some user input during creation, and of course you need either Adobe Acrobat or a 3rd-party utility to create them.
The sample database, Mass Report Emailing.mdb, has one form, frmEMailMerge. The database has an AutoExec macro that runs two procedures: CreateSnapshots and CreatePDFs. These procedurescreate snapshots and (if you have Adobe Acrobat) PDFs for all the reports in the database, which are then available for emailing from the form.
Other than the lstSelectContacts listbox, the controls on the form are bound to fields in tblInfo, so the values are preserved between sessions. However, the txtRecipients textbox’s value is cleared from the form’s Load event, so when you open the form, it is blank.
Figure A shows the frmEMailMerge form after selecting five contacts and clicking the Create Recipient String button.
Figure A. A Recipients string created from selections in a multi-select listbox. Click on image to see full size image.
The txtDocsPath textbox stores your Documents path (the default varies, depending on your Windows version and setup; it is usually something like C:Documents and SettingsHelen FeddemaMy Documents for Windows XP. On my computer, I keep documents on the E: drive, so I changed the Documents folder to E:Documents. The snapshot and PDF files will be created in an Access Merge subfolder under your Documents folder.
After selecting the Snapshot option, and clicking the Send Email button, the email is created to the recipient list, with the report snapshot attached, as shown in Figure B. If you selected the PDF option, the email would have a PDF attachment instead.
Figure B. An email message with an attached report snapshot. Click on image to see full size image.
VBA Code
Private Sub cmdSendEMail_Click()
On Error GoTo ErrorHandler
Dim strReport As String
‘Test for required fields
strReport = Nz(Me![cboSelectReport].Column(1))
If strReport = “” Then
MsgBox “Please select a report”
Me![cboSelectReport].SetFocus
GoTo ErrorHandlerExit
Else
strReport = GetDocsDir & strReport & _
Switch(Me![fraReportFormat].Value = 1, “.snp”, _
Me![fraReportFormat].Value = 2, “.pdf”)
End If
strRecipients = Me![txtRecipients].Value
If strRecipients = “” Then
MsgBox “Please select recipient(s)”
Me![lstSelectContacts].SetFocus
GoTo ErrorHandlerExit
End If
strSubject = Me![txtMessageSubject].Value
If strSubject = “” Then
MsgBox “Please enter a subject”
Me![txtMessageSubject].SetFocus
GoTo ErrorHandlerExit
End If
strBody = Me![txtMessageBody].Value
If strBody = “” Then
MsgBox “Please enter a message body”
Me![txtMessageBody].SetFocus
GoTo ErrorHandlerExit
End If
‘Create new mail message and send to contacts
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = strRecipients
.Subject = strSubject
.Body = strBody
.Attachments.Add strReport
.Display
‘Remove the single quote from the line below to send the email automatically
‘.Send
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch135.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Mass Report EMailing.mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
