More on creating mass emails with report attachments.
Access Archon #135
When the database is opened, the CreateSnapshots procedure is run to automatically create report snapshots for the four reports in the database, in the DocumentsAccess Merge folder (this folder will be created if it doesn’t already exist, using methods of the FileSystemObject). The CreatePDFs procedure is also run, with a message box that lets you opt out if you don’t have Adobe Acrobat. This is done so that the snapshots and PDFs are just created once, and are available to attach to emails. These procedures are listed below.
Public Function CreateSnapshots()
On Error GoTo ErrorHandler
Dim strSnapshot As String
If CheckDocsDir = False Then
GoTo ErrorHandlerExit
End If
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tlkpReports”)
Set fso = CreateObject(“Scripting.FileSystemObject”)
strFilePath = GetDocsDir()
strExtension = “.snp”
Do While Not rst.EOF
strReport = rst![ObjectName]
strFileName = rst![DisplayName]
strSnapshot = strFilePath & strFileName & strExtension
‘Skip creating snapshot file, if it already exists
If fso.FileExists(strSnapshot) = True Then
GoTo NextSnapshot
Else
‘Create new snapshot file in DocumentsAccess Merge folder
DoCmd.OutputTo objecttype:=acOutputReport, _
ObjectName:=strReport, _
outputformat:=acFormatSNP, _
outputfile:=strSnapshot, _
autostart:=False
End If
TryAgain:
‘Test for existence of specified report file, with loop
‘to prevent premature cancellation
Set fso = CreateObject(“Scripting.FileSystemObject”)
If fso.FileExists(strSnapshot) = False Then
GoTo TryAgain
End If
NextSnapshot:
rst.MoveNext
Loop
rst.Close
MsgBox “Snapshots created”, vbInformation + vbOKOnly
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Function
Public Function CreatePDFs()
On Error GoTo ErrorHandler
Dim strPDF As String
Dim prt As Access.Printer
Dim intReturn As Integer
Dim strPrompt As String
strPrompt = “Do you have Adobe Acrobat installed?”
intReturn = MsgBox(strPrompt, vbQuestion + vbYesNo, “Adobe”)
If intReturn = vbNo Then
strPrompt = “You can’t create Adobe PDF files; canceling”
MsgBox strPrompt, vbCritical + vbOKOnly
GoTo ErrorHandlerExit
End If
If CheckDocsDir = False Then
GoTo ErrorHandlerExit
End If
‘Save current default printer
Set prt = Application.Printer
‘Select fax printer as new default printer
Application.Printer = Printers(“Adobe PDF”)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tlkpReports”)
Set fso = CreateObject(“Scripting.FileSystemObject”)
strFilePath = GetDocsDir()
Debug.Print “File path for saved reports: ” & strFilePath
strExtension = “.pdf”
Do While Not rst.EOF
strReport = rst![ObjectName]
strFileName = rst![DisplayName]
strPDF = strFilePath & strFileName & strExtension
‘Skip creating PDF file, if it already exists
If fso.FileExists(strPDF) = True Then
GoTo NextPDF
Else
‘Print to Adobe printer to create PDF file
DoCmd.OpenReport ReportName:=strReport, View:=acViewNormal
End If
TryAgain:
‘Test for existence of specified report file, with loop
‘to prevent premature cancellation
Set fso = CreateObject(“Scripting.FileSystemObject”)
If fso.FileExists(strPDF) = False Then
GoTo TryAgain
End If
NextPDF:
rst.MoveNext
Loop
rst.Close
MsgBox “PDFs created”, vbInformation + vbOKOnly
ErrorHandlerExit:
‘Return to default printer
Application.Printer = prt
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit
End Function
Note on Creating PDFs
If there is a way to automatically feed the file path along with the file name to the Adobe PDF creation dialog, I haven’t figured it out (SendKeys does not work), so when the CreatePDFs procedure is run from the AutoExec macro, for each report, the Save PDF File As dialog opens, as shown in Figure C, and you may need to navigate to the DocumentsAccess Merge folder before saving the PDF. I have found that after navigating to this folder to save the first report PDF, the folder path is retained when creating the other reports.
Figure C. Saving an Access report as an Adobe PDF file
References
The code in the sample database needs the following references (in addition to the default references):
Microsoft DAO 3.6 Object Library
Microsoft Scripting Runtime
Microsoft Word 11.0 Object Library
Microsoft Outlook 11.0 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 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 |
