How name email PDF attachment

I have the following code to send an email along with an attachment;

stDocName = "Buyers Confirmation"

DoCmd.SendObject acReport, stDocName, "PDFFormat(*.pdf)", Me.txtBuyerEmail, DLookup("[ConfirmationCCemailAddress]", "tblXYZcompanyInformation"), , "Buyer's Confirmation -- XYZ Transaction #" & Me.txtDealID, DLookup("[ConfirmationEmailBodyText]", "tblXYZcompanyInformation")

But I want the attachment to be named like this:

Buyers Confirmation XYZ Transaction 1234   (1234 is the me.txtDealID)

instead of this:

Buyers Confirmation.pdf

What do I change the code to be?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
dim newDoc as string, stDocName as string

stDocName = "Buyers Confirmation"
newDoc= stDocName & " XYZ Transaction " & me.txtDealID

docmd.copyobject, newDoc, acReport, stDocName

DoCmd.SendObject acReport, newDoc, "PDFFormat(*.pdf)", Me.txtBuyerEmail, DLookup("[ConfirmationCCemailAddress]", "tblXYZcompanyInformation"), , "Buyer's Confirmation -- XYZ Transaction #" & Me.txtDealID, DLookup("[ConfirmationEmailBodyText]", "tblXYZcompanyInformation")
Rey Obrero (Capricorn1)Commented:
add this line to delete the copy of the report

docmd.deleteobject acreport, newdoc
SteveL13Author Commented:
Strange, it is sending the original report name instead of the newly renamed report.

???
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rey Obrero (Capricorn1)Commented:
dim newDoc as string, stDocName as string

 stDocName = "Buyers Confirmation"
 newDoc= stDocName & " XYZ Transaction " & me.txtDealID

 docmd.copyobject, newDoc, acReport, stDocName

RefreshDatabaseWindow  'add this line after the copyobject



DoCmd.SendObject acReport, newDoc, "PDFFormat(*.pdf)", Me.txtBuyerEmail, DLookup("[ConfirmationCCemailAddress]", "tblXYZcompanyInformation"), , "Buyer's Confirmation -- XYZ Transaction #" & Me.txtDealID, DLookup("[ConfirmationEmailBodyText]", "tblXYZcompanyInformation")




check if you use  newDoc and not  stDocName
SteveL13Author Commented:
Still same issue.  Here is my code now:

    Dim newDoc As String, stDocName As String

    stDocName = "Buyers Confirmation"
    newDoc = stDocName & " XYZ Transaction " & Me.txtDealID

    DoCmd.CopyObject , newDoc, acReport, stDocName
    
    RefreshDatabaseWindow

    DoCmd.SendObject acReport, newDoc, "PDFFormat(*.pdf)", Me.txtBuyerEmail, DLookup("[ConfirmationCCemailAddress]", "tblXYZcompanyInformation"), , "Buyer's Confirmation -- XYZ Transaction #" & Me.txtDealID, DLookup("[ConfirmationEmailBodyText]", "tblXYZcompanyInformation")

Open in new window

Rey Obrero (Capricorn1)Commented:
how do you initiate the sending of the report? button click event?
SteveL13Author Commented:
Yes.  Onclick event
Rey Obrero (Capricorn1)Commented:
lets try something

RefreshDatabaseWindow

docmd.openreport newDoc,acviewpreview

DoCmd.SendObject acReport, newDoc, "PDFFormat(*.pdf)", Me.txtBuyerEmail, DLookup("[ConfirmationCCemailAddress]", "tblXYZcompanyInformation"), , "Buyer's Confirmation -- XYZ Transaction #" & Me.txtDealID, DLookup("[ConfirmationEmailBodyText]", "tblXYZcompanyInformation")
Rey Obrero (Capricorn1)Commented:
or perhaps make a delay before sending the report

place this codes in a regular module

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


....

RefreshDatabaseWindow

 sleep 5000

 DoCmd.SendObject acReport, newDoc, "PDFFormat(*.pdf)", Me.txtBuyerEmail, DLookup("[ConfirmationCCemailAddress]", "tblXYZcompanyInformation"), , "Buyer's Confirmation -- XYZ Transaction #" & Me.txtDealID, DLookup("[ConfirmationEmailBodyText]", "tblXYZcompanyInformation")
SteveL13Author Commented:
Sorry.  Had to go away for a while yesterday.  I tried both of your last two suggestions.  The attachment still is not being renamed.
SteveL13Author Commented:
What is strange is that when I put a stop on the DoCmd,SendObject line and hover over newDoc, it DOES read properly with the new name.

????
Rey Obrero (Capricorn1)Commented:
do  a compact and repair

do a debug > compile, correct any errors raised
SteveL13Author Commented:
Have done both.  Over and over again.
Rey Obrero (Capricorn1)Commented:
do you see the new copy of the report?

<The attachment still is not being renamed. > where do you see this?
SteveL13Author Commented:
I can see the newly named report in the navigation pane.  But the attachment in the outgoing email still has the original name.
Rey Obrero (Capricorn1)Commented:
can you post an image of the outgoing email
SteveL13Author Commented:
Here it is.  Note that the subject line is correct.  But the attachment still has the original name.

Email Snapshot
Rey Obrero (Capricorn1)Commented:
just tried it and this is the result

x

can you try this using a different computer?
SteveL13Author Commented:
I am out of town this morning but will try this afternoon. Thanks.
SteveL13Author Commented:
Rey,

I am back and tried it on another computer and get the same result.  Could you possibly post the code you used that worked for you so I can see if I messed it up somehow?
Rey Obrero (Capricorn1)Commented:
I just used the original codes I posted above
dim newDoc as string, stDocName as string

 stDocName = "Buyers Confirmation"
 newDoc= stDocName & " XYZ Transaction " & me.txtDealID

 docmd.copyobject, newDoc, acReport, stDocName

 DoCmd.SendObject acReport, newDoc, "PDFFormat(*.pdf)", Me.txtBuyerEmail, DLookup("[ConfirmationCCemailAddress]", "tblXYZcompanyInformation"), , "Buyer's Confirmation -- XYZ Transaction #" & Me.txtDealID, DLookup("[ConfirmationEmailBodyText]", "tblXYZcompanyInformation") 

Open in new window

SteveL13Author Commented:
I just tried that code again.  Still does not work on either computer.  My main computer is running Windows 10 with Access 2013.  My other computer is running Windows 8.1 with Access 2010.

I just don't get it.
Rey Obrero (Capricorn1)Commented:
i tried it using
Windows 7 access 2010
Windows 10 Access 2013
Windows 10 access 2016

and they all work..

there is typo in the SendObject line

change
"PDFFormat(*.pdf)"
with

"PDF Format(*.pdf)"

or

acFormatPdf

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.