Sending out a report with a specific File name

[Thompyt]

When I send a Report email out through VBA, I have figured out how to format the email body and subject, and select the attachment format. How do I set the attachment name?




Currently it saves at the reports base name. I.E. (By Field Report.pdf)

I want it to save as "By Field - " & [Field Name]" - " & Format(Date("dd mmm yyyy"))

So it looks like: By Field - Simulation - 12 Feb 2015.pdf

I understand it would be a save in the temp folder and would be gone as soon as the mail is sent. I can have another button if I want to save locally, or add code to save to the right folder (That's for another thread/day).


Thanks

[June7]

What method are you using to send email with attachment - SendObject? Cannot set attachment name with SendObject method.

[Thompyt]

Yes, it is SendObject. So for me to do this then I would have to save the file in a temp location. Then get the file and attach to the email, all in order to have the file naming convention I want?

[June7]

Yes, could not use SendObject method.

[Thompyt]

Dang, Thank you June, I'll have to figure out how to do it the long way, so I can be lazy on the other end.

[June7]

Do you know code for manipulating Outlook object?

[Thompyt]

Not really. I know enough to be dangerous to myself and others....

[June7]

Okay, when you have specific issue, come back and will try to answer.

[Thompyt]

June, lets make it simpler for me,
I would like to save the file name to a temp location. Filename is of my choosing, but automated naming.

Could I put it like:

[Report]![By Field Report] & " - " & [Fieldname] & Format(Date(), "ddmmmyyy") or sum such code?

[June7]

Would have to use OutputTo method.

And yes, can then specify file path and name.

[Thompyt]

June7
Got this part kind of figured out. It comes out with "By Field - - 23Feb2105" I would like " "By Field - [Whatever fieldname is selected] - 23Feb2105"

expression .OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding, OutputQuality)


DoCmd.OutputTo acOutputReport, "By Field Report", acFormatPDF, "By Field - " & Field_Name & " - " & Format(Date, "dd mmm yyyy") & ".PDF", , False

I get an "invalid or unqualified reference" error on !Field_Name.

I have yet to figure out the location part. I am on a "multiuser" system and my documents go under a my user login name. I am also not too sure where I put the location reference.

C:\users\username\My Documents\TempA This would be part of the output file, after the aoutput format and includes the file name.

[June7]

Do you want the document filed into the My Documents of whomever is logged onto the computer?
Does everyone have a TempA folder in My Documents?

DoCmd.OutputTo acOutputReport, "By Field Report", acFormatPDF, "C:\users\" & Environ("USERNAME") & "\My Documents\TempA\By Field - " & Me.correctfieldnamehere & " - " & Format(Date, "dd mmm yyyy") & ".PDF", , False


If you want the 'correctfieldnamehere' to be a dynamic reference for field name that provides this value, that is another issue.

[Thompyt]

June7,
Got it to save 1 time correctly, changing the field_name caused an issue.


Yes I am trying to get a dynamically linked field_name from:

SELECT Field.Field_ID, Field.[Field Name], System.[System Name], Nomenclature.Nomenclature, Component.Component, Version.Version, Accreditation.[ACC_#], Accreditation.[Type _ACC], Accreditation.Accred, Accreditation.Expires, Accreditation.Compliance, Main.Hold
FROM Version RIGHT JOIN (System RIGHT JOIN (Nomenclature INNER JOIN (Field INNER JOIN (Component INNER JOIN (Accreditation INNER JOIN Main ON Accreditation.Accred_ID = Main.Accred_ID) ON Component.Comp_ID = Main.Comp_ID) ON Field.Field_ID = Main.Field_ID) ON Nomenclature.Nomen_ID = Main.Nomen_ID) ON System.SYS_ID = Main.SYS_ID) ON Version.Vers_ID = Main.Vers_ID
GROUP BY Field.Field_ID, Field.[Field Name], System.[System Name], Nomenclature.Nomenclature, Component.Component, Version.Version, Accreditation.[ACC_#], Accreditation.[Type _ACC], Accreditation.Accred, Accreditation.Expires, Accreditation.Compliance, Main.Hold
HAVING (((Field.[Field Name]) Like [forms]![By Field].[My Field] & "*"))
ORDER BY Field.Field_ID;

I am using a [By Field] Form in conjunction with the By Field Query. The field name is directed to the Field table and selects it by order of Field_ID showing the names of Field_Name. The issue I am getting now is that when I select a Field_Name it rewrites the first Field_Name in row 1.

[June7]

Not sure I understand.

The value of Field_Name determines which field of the record to use in the export naming?

Is there a control on the [By Field] form bound to the Field_Name field? What is name of that control?

Maybe:

"\My Documents\TempA\By Field - " & Me.Controls(Me.[control name]) & " - "

[Thompyt]

Name of the control is [My Field] in [Forms]![By Field] In [Query]![By Field] the control is [Field Name] in the criteria I have: Like [forms]![By Field].[My Field] & "*"