How do you change the name of a pdf exported by access to a field name in the report?

[DanicaDear]

I have a form. On the form I have a button that calls a report, saves it as a PDF, and attaches the PDF to Outlook in a blank email message. The button was created with the wizard in Access, as I'm not too handy with the code writing. I attach one report per email, and I use parameters to call the single record I want displayed in the report.
The problem is every report I attach takes on the exact same name--the name of the Access report("Transformer_Report.pdf"). So that we can differentiate all the files from each other, I want each report to take on the name from within a field on the report: "MENS#".

Here is the code the wizard wrote for me:

Private Sub btnEmailReport_Click()
On Error GoTo Err_btnEmailReport_Click
 
    Dim stDocName As String
 
    stDocName = "Transformer_Report"
    DoCmd.SendObject acReport, stDocName
 
Exit_btnEmailReport_Click:
    Exit Sub
 
Err_btnEmailReport_Click:
    MsgBox Err.Description
    Resume Exit_btnEmailReport_Click
 
End Sub

Something tells me I ought to be able to play with the stDocName =... line and get my report named with the appropriate MENS# for each report I want to export. I can't find a simple way to do this. Can someone help me?

Keep in mind, my go-to reference is an "Access for Dummies" book--just so you know who you might be dealing with. HA!

[ADezii]

Assuming MENS# is Unique for each Record:

stDocName = "Transformer_Report_" & Me![MENS#]

[Seth Schrock]

@ADezii
But isn't the DoCmd.SendObject command looking for a report with the name that is stored in stDocName? So if the name of the report is "Transformer_Report", then adding other text to it would cause an error because it can't find the object to attach.

[DanicaDear]

Seth might be on to something. When I tried that line of code, I get an error:
"Microsoft Office Access cannot find the object "Tranformer_Report_12345."
(12345 IS the correct MENS#).

[Seth Schrock]

If you really want to have the name the way you want it, then the only way that I know of is to create a PDF and save it with the name you want (done using the DoCmd.OutputTo command) and then attach it to the email (can be done with code). I actually do this in one of my databases where we need the report saved on the computer anyway. It is really cool to be able to do this, but it does take quite a bit of coding. If you want to go this way, let me know and I'll post what I have done. Also, are you using MS Outlook or CDO to send your email?

[DanicaDear]

I am using Outlook.

I have seen the complicated posts around the internet but their applications weren't close enough to mine that I could follow the code and adapt it to suit me. After 2 days of searching, I finally decided to ask here. I don't really write code, but thanks to some great interaction on Bytes I have learned to interpret it if it is simple enough. If you have a piece of code that is simple enough that you think I could figure it out and adapt it, I would be thrilled to look it over.

On another note---the PDF would not be required if I could put the items from the report directly into the email, just as text for example. In fact, this would be better, but I have never heard of this being done. Is that an alternative option?

To give a few more details...the items I put in the report are Tranformer serial numbers, weight, height, status, things of that nature. It's about 12 fields or so.

Thanks so much Seth (and ADezii too). I really appreciate your help and time.

[ADezii]

I use an approach very similr to Seth's in that I Output a Report to a specific Filename based on a Unique Value, in your case this would be [MENS#], in the Current Folder. This Reprt, in *.rtf Format, is now attached to an Outlook E-Mail. As also indicated by Seth, this can be a little complicated and using Automation Code.

[Seth Schrock]

If it is just the information that you are wanting, then you could make the text of the message be

Serial Number: 123xyz4
Weight: 100 Lb.
Height: 17 Inches
Etc.

This would be simple code, but a lot of typing to do. You can even send it using Rich Text format (adds more typing). Here is an abbreviated version.

Public Sub SendEmail(SendTo As String, Subject As String)
 
Dim olApp As New Outlook.Application
Dim olMailItem As Outlook.MailItem
Dim strMsg As String
 
Set olMailItem = olApp.CreateItem(olMailItem)
 
strMsg = "<b>Serial Number:</b> " & Me.SerialNumber & "<br>" & _
         "<b>Weight:</b> " & Me.Weight & "<br>" & _
         "<b>Height:</b> " & Me.Height & "<br>"
 
With olMailItem
    .To = SendTo
    .Subject = Subject
    .HTMLBody = strMsg
    '.Send   'Uncomment if you want the email to be sent automatically
    '.Display   'Uncomment if you want to view the email
End With
 
End Sub

This uses early binding which requires a reference set to the Outlook reference. You just need to pass the email address the email gets sent to and the subject that you want. You can skip these if you just want to display the email and enter those things manually. To add the other fields just keep adding them to the strMsg variable. Currently I have the description text put in bold and the value in regular text.

Here is a list of the common tags you can put around the text to make it "rich":

<b>Text</b>   Bold
<u>Text</u>   Underline
<i>Text</i>   Italics
<br>          Line break

[zmbd]

Sticking with your wizard code; thus, trying to keep things simple, the basic format for the command you are using is:

expression.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

Note that there are "Subject" and MessageText parameters that can be used.

Takeing the concept from ADezii for the name and using it for the subject of the email (and/or if you like we can add it to the message text - which is what I've done here) we can do the following:

(Please note: The following may have typos... however it compiled on my PC)

Private Sub btnEmailReport_Click() 
On Error GoTo Err_btnEmailReport_Click
'
    Dim stDocName As String
    Dim stSubject As String
    Dim stMessageText As String
 
'
    stDocName = "Transformer_Report"
    stSubject = "Transformer_Report_" & Me![MENS#]
    stMessageText = "Transformer_Report_" & Me![MENS#]
'
DoCmd.SendObject objecttype:=acReport, ObjectName:=stDocName, subject:=stSubject, MessageText:=stMessageText
'
Exit_btnEmailReport_Click:
    Exit Sub
'
Err_btnEmailReport_Click:
    MsgBox Err.Description
    Resume Exit_btnEmailReport_Click
 
End Sub 

Not exactly what you want; however, very straight forward.
If you truely want to change the report name I'll work on that too.

-z

[zmbd]

BTW: Which version of Access?