Access Archon #170
Before we finish this article a reminder that mass emailing should always comply with all local laws. More generally, you should ensure that all mailings are to people who have authorized the use of their email address for that purpose. Our purpose is to help you efficiently send emails from Access – it’s up to you to do that legally, responsibly and politely.
The remaining VBA procedures are listed below:
frmEMailCustomReport Form Module
Private Sub cmdSendEMail_Click()
On Error GoTo ErrorHandler
Dim msg As Outlook.MailItem
Dim varItem As Variant
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strQuery As String
Dim strSQL As String
Dim strReportName As String
Dim strEmployeeName As String
Dim strReportFile As String
Dim lngID As Long
Dim strEMailRecipient As String
Dim strHTML As String
Dim intLinkType As Integer
‘Test for required fields
strSubject = Me![txtMessageSubject].Value
If strSubject = “” Then
strTitle = “No subject entered”
strPrompt = “Please enter a subject”
MsgBox prompt:=strPrompt, _
buttons:=vbExclamation + vbOKOnly, _
Title:=strTitle
Me![txtMessageSubject].SetFocus
GoTo ErrorHandlerExit
End If
strBody = Me![txtMessageBody].Value & vbCrLf & vbCrLf
If strBody = “” Then
strTitle = “No message body entered”
strPrompt = “Please enter message body text”
MsgBox prompt:=strPrompt, _
buttons:=vbExclamation + vbOKOnly, _
Title:=strTitle
Me![txtMessageBody].SetFocus
GoTo ErrorHandlerExit
End If
‘Send an email with a customized report to each
‘selected employee
Set lst = Me![lstSelectEmployees]
‘Check that at least one employee has been selected
If lst.ItemsSelected.Count = 0 Then
strTitle = “No employees selected”
strPrompt = “Please select at least one employee”
MsgBox prompt:=strPrompt, _
buttons:=vbExclamation + vbOKOnly, _
Title:=strTitle
lst.SetFocus
GoTo ErrorHandlerExit
Else
For Each varItem In lst.ItemsSelected
‘Clear old values
strEmployeeName = “”
strEMailRecipient = “”
‘Check for email address
strEMailRecipient = Nz(lst.Column(1, varItem))
If strEMailRecipient = “” Then
GoTo NextEmployee
Else
strEmployeeName = Nz(lst.Column(0, varItem))
lngID = Nz(lst.Column(2, varItem))
End If
‘Create file name for saving report as a snapshot
strReportName = “rptEmployeeInvoices”
strReportFile = GetReportsPath & “Employee Invoices” _
& ” for ” & strEmployeeName & “.snp”
‘Recreate query used as record source of filtered report
strQuery = “qryEmployeeInvoices”
Set dbs = CurrentDb
strSQL = “SELECT * FROM qryInvoices WHERE [EmployeeID] = ” _
& lngID & “;”
Debug.Print “SQL for ” & strQuery & “: ” & strSQL
lngCount = CreateAndTestQuery(strQuery, strSQL)
Debug.Print “No. of items found: ” & lngCount
If lngCount = 0 Then
strPrompt = “No records found; can’t create report”
strTitle = “Canceling”
MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
GoTo ErrorHandlerExit
End If
‘Save report snapshot file
DoCmd.OutputTo objecttype:=acOutputReport, _
ObjectName:=strReportName, _
outputformat:=acFormatSNP, _
outputfile:=strReportFile, _
autostart:=False
Debug.Print strReportFile & ” created”
‘Create HTML string
strEmployeeName = Nz(lst.Column(3, varItem)) _
& “%20” & Nz(lst.Column(4, varItem))
intLinkType = Nz(Me![fraLinkType].Value, 1)
If intLinkType = 1 Then
strReportFile = GetReportsPath & “Employee%20Invoices” _
& “%20for%20” & strEmployeeName & “.snp” & Chr(34)
strHTML = “
file:///” & strReportFile _
& “>Download Report from a network drive
“
ElseIf intLinkType = 2 Then
strReportFile = “Employee%20Invoices” & “%20for%20” _
& strEmployeeName & “.snp” & Chr(34)
‘Replace my Web site with yours
strHTML = “
& “http://www.helenfeddema.com/Files/” _
& strReportFile _
& “>Download Report from the Internet
“
& strReportFile & “>Download Report from the Internet
“
End If
Debug.Print “HTML: ” & strHTML
‘Create new mail message and send to employee
‘(each employee gets a personalized report)
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = strEMailRecipient
.Subject = strSubject
.HTMLBody = strBody & strHTML
.Display
‘Remove the single quote from the line below to send
‘the email automatically
‘.Send
End With
NextEmployee:
Next varItem
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
Access 2007 Database
The forms in the Access 2007 database use a variation of the new Office theme, for a new look, but their functionality is the same as in the Access 2002-2003 database. Figure D shows the main menu:
Figure D. The main menu of the Access 2007 sample database
To create PDF files from Access 2007, you need to download and install the free Save As PDF add-in from the Microsoft Web site. After doing this, you will see a PDF selection in the Export group of the Database Tools Ribbon, as shown in Figure E:
Figure E. The PDF Export selection on the Access 2007 Ribbon
VBA Code
You can export to a PDF file in code, too, using the new acFormatPDF named argument for the OutputTo method, as in the code segment below:
DoCmd.OutputTo objecttype:=acOutputReport, _
ObjectName:=strReportName, _
outputformat:=acFormatPDF, _
outputfile:=strReportFile, _
autostart:=False
The code in the Access 2007 database differs from the Access 2002-2003 code only in substituting PDF for Snapshot in creating the files for the links.
Notes
For an explanation of the folder selection code on the main menu, see Chapter 9 of my new book, Access™ 2007 VBA Bible For Data-Centric Microsoft Applications, which deals with the FileSystemObject.
To create the HTML strings needed for the local file links, I started by manually creating hyperlinks in FrontPage (any application that creates HTML code would do). I clicked the Insert Hyperlink button, selected the Existing File or Web Page button, and browsed for the file, as shown in Figure F:
Figure F. Browsing for a file to create a hyperlink.
The HTML for the file link is:
file:///G:/Documents/Reports/Contacts%20by%20Name.snp“>Test Link
The VBA code combines HTML syntax with the file name to create the HTML string for the link, substituting %20 for spaces in the file name (see the code listings above). For the Web site downloads, the syntax is a little different (this is for my Web site; yours may need a different syntax):
http://www.helenfeddema.com/Files/Contacts%20by%20Name.snp“>Test Link
References
The code in the sample 2002-2003 database needs the following references (in addition to the default references):
Microsoft DAO 3.6 Object Library
Microsoft Scripting Runtime
Microsoft Office 11.0 Object Library
Microsoft Outlook 11.0 Object Library
Microsoft Word 11.0 Object Library
For the Access 2007 database, the Office references are 12.0, and the DAO 3.6 reference is not needed, since there is a built-in reference to the new incarnation of DAO, awkwardly named the Microsoft Office 12.0 Access database engine Object Library.
If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch170.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Mass Report Link Emailing (AA 170).mdb |
Access 2002-2003 database | |
|
Mass Report Link Emailing (AA 170).accdb |
Access 2007 database |
Wherever you want |
