Change name of exported report via VBA

[gojets1721]

I'm using some simple code below to export an invoice and attach it to an email in outlook. The code also adds a default email subject and message.

When using this code, the attached invoice retains the same file name as the report name in Access. I was wondering if there's a way in VBA to rename the attached report to something different. I don't want to change the default naming format in Access (i.e. rptInvoice) but it would look a little bit better in Outlook if it was named something cleaner (i.e. Invoice, New Invoice, etc.)

Any suggestions?

Private Sub btnEmailInvoice_Click()
On Error GoTo btnEmailInvoice_Click_Err

    DoCmd.RunCommand acCmdRefresh
    DoCmd.SendObject acReport, "rptInvoice", "PDFFormat(*.pdf)", "", "", "", "Invoice", "Hello, your invoice is attached.", True, ""
    
btnEmailInvoice_Click_Exit:
    Exit Sub

btnEmailInvoice_Click_Err:
    If Err = 2501 Then
      
    Else
        ' Report the error
        MsgBox Err.Description, vbExclamation
    End If
    
End Sub

 

[theDBguy]

Hi. Before you use SendObject, you can open the report in hidden view and then adjust its Caption property. Do the SendObject and then close the hidden report.

 

[KitaYama]

In cases like this, I save the report as pdf in a folder, then attach it to a mail and then delete the pdf.

        FileName = GetEnviron("Desktop") & "myPDFName.pdf"
        DoCmd.OpenReport "MyReport", AcView, , , acHidden, Args
        DoCmd.OutputTo acOutputReport, "MyReport", "PDF", FileName
        DoCmd.Close acReport, "MyReport"