Everyone knows that the quickest and easiest method for sending an e-mail in MS Access is to use the SendObject method
DoCmd.SendObject acSendReport, "YourReportName", , "Someone@somewhere.com", _ , , "Subject", "E-mail Body" |
This is all find an dandy, but there is one flaw, it uses the name of your MS Access report: “rpt_SalesForecast” or “rpt_SalesByRegion” access as the attachment name. This simply is not acceptable for most purposes. We want to have a name that reflects better what the report is about: “Sales forecast 2012″, “Average Sale for Montreal in July”.
I recently was in a discussion and theDBguy was kind enough to share a little trick to do exactly this without requiring a whole lot of extra coding and automation. I thought I’d share it for everyone to benefit from. Below is the code in question:
Dim sExistingReportName As String Dim sAttachmentName As String 'Input variables sExistingReportName = "YourMSAccessReportName" 'Name of the Access report Object to send sAttachmentName = "AttachmentNameToBeUsedInTheEmail" 'Name to be used for the attachment in the e-mail 'The code to make it happen DoCmd.OpenReport sExistingReportName, acViewPreview, , , acHidden Reports(sExistingReportName).Caption = sAttachmentName 'by changing the report caption 'you effectively change the name 'used for the attachment in the '.SendObject method DoCmd.SendObject acSendReport, sExistingReportName, , "Someone@somewhere.com", _ , , "Subject", "E-mail Body" DoCmd.Close acReport, sExistingReportName |
Short, sweet and simple! Thank you theDBguy!
