Link to home
Create AccountLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

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?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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")
add this line to delete the copy of the report

docmd.deleteobject acreport, newdoc
Avatar of SteveL13

ASKER

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

???
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
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

how do you initiate the sending of the report? button click event?
Yes.  Onclick event
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")
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")
Sorry.  Had to go away for a while yesterday.  I tried both of your last two suggestions.  The attachment still is not being renamed.
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.

????
do  a compact and repair

do a debug > compile, correct any errors raised
Have done both.  Over and over again.
do you see the new copy of the report?

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

User generated image
just tried it and this is the result

User generated image

can you try this using a different computer?
I am out of town this morning but will try this afternoon. Thanks.
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?
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

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.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account