Hi Diavolo:
I have a cornocopia of files on my computer, with some tips that I file away
for a "rainy day". Here is a tip that I got from a "Helen's website"... how
to send emails in 3 different ways. Note that at one point you can put in
the multiple attachments via the ".Attachments.Add strSnapshotFile" method:
============beginning of the message that I filed away========
Access Archon Column #101
More Things You Can Do Three Ways in Access, Part 2
By Helen Feddema - the Access Archon
Access versions: 97, 2000 & 2002
Level: Intermediate
Visit Helen's web site.
Check out Helen's NEW Access BOOK - click here.
HINT: click on any WAW image to see a full size version.
Send an Access Report by EMail
The ReportMethod* procedures are located in the basTestCode module in the
sample database.
Method 1
Use the OutputTo method to output the report as a snapshot (.snp) file, and
send it as an attachment to an e-mail message created using Automation code,
as in the following procedure:
Sub SendReportMethod1()
Dim strReport As String
Dim strSnapshotFile As String
Dim appOutlook As New Outlook.Application
Dim itm As Outlook.MailItem
Dim strEMailRecipient As String
Dim strDataSource As String
Dim strFilePath As String
strReport = "rptProducts"
strEMailRecipient = "Steve Alboucq"
strFilePath = Application.CurrentProject.path & "\"
strSnapshotFile = strFilePath & "Products.snp"
DoCmd.OutputTo objecttype:=acOutputReport, _
objectname:=strReport, _
outputformat:=acFormatSNP, _
outputfile:=strSnapshotFile
'Create new mail message and attach snapshot file to it
Set itm = appOutlook.CreateItem(olMailItem)
With itm
.To = strEMailRecipient
.Subject = "Products report you requested"
.Body = "This file was exported from " & strReport & _
" on " & Date & "." & vbCrLf & vbCrLf
.Attachments.Add strSnapshotFile
.Display
End With
End Sub
The resulting mail message with the snapshot attachment is shown in Figure
A.
Figure A. A mail message with a snapshot attachment exported from an Access
report
Note: The recipient must have either Access or the Snapshot Viewer to view
the snapshot file.
Method 2
Use the SendObject method of the DoCmd object to send the report to an email
recipient, as in the following code, which sends the report as a Rich Text
document:
Sub SendReportMethod2()
Dim strEMailRecipient As String
Dim strReport As String
Dim strSubject As String
Dim strMessage As String
strEMailRecipient = "Woody Leonhard"
strReport = "rptProducts"
strSubject = "Products report"
strMessage = "This file was exported from " & strReport & _
" on " & Date & "." & vbCrLf & vbCrLf
DoCmd.SendObject objecttype:=acSendReport, _
objectname:=strReport, _
outputformat:=acFormatRTF, _
To:=strEMailRecipient, _
Subject:=strSubject, _
messagetext:=strMessage
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Method 3
Use the TransferText method of the DoCmd object to export the report's data
source query or SQL statement to a comma-delimited text file (the most
widely used file format), and send it as an attachment to an e-mail message,
as in the following code:
Sub SendReportMethod3()
On Error GoTo ErrorHandler
Dim appOutlook As New Outlook.Application
Dim itm As Outlook.MailItem
Dim strEMailRecipient As String
Dim strDataSource As String
Dim strFilePath As String
Dim strFileName As String
Dim strSpec As String
strEMailRecipient = "Steve Alboucq"
strDataSource = "qryProducts"
strSpec = "Products Export Specification"
strFilePath = Application.CurrentProject.path & "\"
strFileName = strFilePath & "Products.csv"
'Save qryProducts (data source of rptProducts) as a text file
'in the same folder as database
DoCmd.TransferText transfertype:=acExportDelim, _
specificationname:=strSpec, _
tablename:=strDataSource, _
fileName:=strFileName, _
hasfieldnames:=True
'Create new mail message and attach text file to it
Set itm = appOutlook.CreateItem(olMailItem)
With itm
.To = strEMailRecipient
.Subject = "Products report you requested"
.Body = "This file was exported from " & strDataSource & _
" on " & Date & "." & vbCrLf & vbCrLf
.Attachments.Add strFileName
.Display
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
This method is best if you just need to send the report's data, and don't
care about preserving the report's formatting. An export specification is
required, which must be prepared in advance, by exporting the query in the
interface. If there already is a file with the same name in the current
database folder, it will be overwritten by a newly exported file without a
confirmation message.
Method 4
Save the report as a PDF file, by printing it to the Acrobat Distiller if
you have Adobe Acrobat, or using a third-party utility otherwise (see WAW
4.01 and WAW 4.03 for details), and send it as an attachment to an e-mail
message. In Access 2002, you can use the new Printer object to select a
printer (see the "Using the Printer Object in Code" section of Chapter 20 of
my new book, Access Inside-Out, for details and sample code for using the
Printer object in Access 2002). In the interests of compatibility, however,
the code sample below assumes that you have already printed the report to a
PDF file, either using the Printer object, or manually printing to the Adobe
Acrobat printer, or using a third-party utility.
Sub SendReportMethod4()
On Error GoTo ErrorHandler
Dim appOutlook As New Outlook.Application
Dim itm As Outlook.MailItem
Dim strEMailRecipient As String
Dim strDataSource As String
Dim strFilePath As String
Dim strFileName As String
Dim strReport As String
Dim strTestFile As String
strEMailRecipient = "Steve Alboucq"
strReport = "rptProducts"
strFilePath = Application.CurrentProject.path & "\"
strFileName = strFilePath & "Products.pdf"
'Test for existence of PDF file in current folder
strTestFile = Nz(Dir(strFileName))
Debug.Print "Test file: " & strTestFile
If strTestFile = "" Then
MsgBox strFileName & " PDF file not found; canceling"
GoTo ErrorHandlerExit
End If
'Create new mail message and attach PDF file to it
'This code assumes that you have printed the report to a
'PDF file in some method available to you
Set itm = appOutlook.CreateItem(olMailItem)
With itm
.To = strEMailRecipient
.Subject = "Products report you requested"
.Body = "This file was exported from " & strReport & _
" on " & Date & "." & vbCrLf & vbCrLf
.Attachments.Add strFileName
.Display
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
I like this method best for reports where I want to preserve the report
formatting, because it preserves the appearance of the report perfectly, and
the PDF format is much more likely to be available on the recipient's
computer than the Snapshot viewer. If the user doesn't have Adobe Reader
installed, it can easily be downloaded from any number of Web sites,
including Adobe's own site, unlike the Snapshot Viewer, which is difficult
to find on Microsoft's site, and may not be available for the Access version
you are using.
============end==============
I apologize for the length of the tip article, but I thought it might help
to get the complete text message...
Regards,
Al
