MS Access – VBA – Rename attachment in SendObect Method

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!

20 responses on “MS Access – VBA – Rename attachment in SendObect Method”

  1. Thomas

    Have been looking for this!

    It work in first take! Wow. Thank you so much for posting this.

    Regards
    Thomas

  4. Kevin Marksteiner

    Thanks heaps, this works like a charm. Wish others would answer so precisely as you Ted DB Guy.

  6. Caroline

    This works perfect for access reports but Ive been trying to get it to work with a query that I need to export as a xls file. Ive tried amending the code to that of a query but it seems that there is no caption to be rename in queries. Can anyone advise how I can dynamically rename the attachment of a query please.

    1. Daniel Pineault Post author

      If you want to e-mail an xls/xlsx (or other Excel format) you need to export the query in the format you wish (TransferSpreadsheet, my export procedure, or any other technique of your choosing) and then attach it to your e-mail. Sadly, SendObject does not support external attachments, so in such a case you will need to use Outlook automation, CDO mail, or some other email approach.

  7. John Turczak

    Very neat piece of coding. Just saved me having to write a whole custom piece of code to send emails via Outlook

  12. Matthew J from NZ

    Thanks so much, this is exactly what I was after.
    I’m fairly new to messing about with VBA – so what I’m about to say may be basic stuff for most people but I added a control name to the sAttachmentName line (see below) so that the attached report name comes up as ‘Invoice 422515’ for example.

    sAttachmentName = “Invoice ” & Me![InvNo] ‘Name to be used for the attachment in the e-mail

    The whole code that I use (see below) inserts the customer’s email address, the subject which includes the invoice number and my name, the required attachment name (now!), and some lines of text. Again, this may be basic to most people, but if anyone else at my level comes across this, it may be of some help.
    Thanks again.

    Private Sub Command41_Click()
    On Error GoTo Command41_Click_Err

    Dim sExistingReportName As String
    Dim sAttachmentName As String

    ‘Input variables
    sExistingReportName = “InvoiceCurrRpt” ‘Name of the Access report Object to send
    sAttachmentName = “Invoice ” & Me![InvNo] ‘Name to be used for the attachment in the e-mail eg Invoice 123456

    ‘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 acReport, sExistingReportName, “PDFFormat(*.pdf)”, Me![Email], “”, “”, “Invoice ” & Me![InvNo] & ” for Joe Bloggs”, “Hi there,” & vbCrLf & “Here is the invoice for the work carried out recently.” & vbCrLf & “Thanks” & vbCrLf & “- Joe”, True, “”
    DoCmd.Close acReport, sExistingReportName

    ‘The following sets controls on the billing form then opens the main database form and closes the billing form
    Forms!BillingF!InvSent = True
    Forms!BillingF!InvDate = Date
    DoCmd.OpenForm “MainF”, acNormal, “”, “”, , acNormal
    DoCmd.Close acForm, “BillingF”

    Command41_Click_Exit:
    Exit Sub

    Command41_Click_Err:
    MsgBox Error$
    Resume Command41_Click_Exit

    End Sub

  14. Emmanuel Stockman

    Thank you for this great piece of code. Just one question: is it possible to prevent the report from opening in Access?

    Thank you!

  17. Allen geeraert

    This is a very well crafted solution from an obviously talented individual. Many thanks.

    Allen