How name email PDF attachment

SteveL13
SteveL13 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Rey Obrero (Capricorn1)
Top Expert 2016

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)
Top Expert 2016

Commented:
add this line to delete the copy of the report

docmd.deleteobject acreport, newdoc
SteveL13

Author

Commented:
Strange, it is sending the original report name instead of the newly renamed report.

???
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Rey Obrero (Capricorn1)
Top Expert 2016

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
SteveL13

Author

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)
Top Expert 2016

Commented:
how do you initiate the sending of the report? button click event?
SteveL13

Author

Commented:
Yes.  Onclick event
Rey Obrero (Capricorn1)
Top Expert 2016

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)
Top Expert 2016

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")
SteveL13

Author

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.
SteveL13

Author

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)
Top Expert 2016

Commented:
do  a compact and repair

do a debug > compile, correct any errors raised
SteveL13

Author

Commented:
Have done both.  Over and over again.
Rey Obrero (Capricorn1)
Top Expert 2016

Commented:
do you see the new copy of the report?

<The attachment still is not being renamed. > where do you see this?
SteveL13

Author

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)
Top Expert 2016

Commented:
can you post an image of the outgoing email
SteveL13

Author

Commented:
Here it is.  Note that the subject line is correct.  But the attachment still has the original name.

Email Snapshot
Rey Obrero (Capricorn1)
Top Expert 2016

Commented:
just tried it and this is the result

x

can you try this using a different computer?
SteveL13

Author

Commented:
I am out of town this morning but will try this afternoon. Thanks.
SteveL13

Author

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)
Top Expert 2016

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

SteveL13

Author

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.
Top Expert 2016
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial