Avatar of SteveL13
SteveL13
Flag 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?
Microsoft Access

Avatar of undefined
Last Comment
Rey Obrero (Capricorn1)

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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)

add this line to delete the copy of the report

docmd.deleteobject acreport, newdoc
SteveL13

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

???
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rey Obrero (Capricorn1)

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

ASKER
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)

how do you initiate the sending of the report? button click event?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveL13

ASKER
Yes.  Onclick event
Rey Obrero (Capricorn1)

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)

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")
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SteveL13

ASKER
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

ASKER
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)

do  a compact and repair

do a debug > compile, correct any errors raised
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveL13

ASKER
Have done both.  Over and over again.
Rey Obrero (Capricorn1)

do you see the new copy of the report?

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

ASKER
I can see the newly named report in the navigation pane.  But the attachment in the outgoing email still has the original name.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rey Obrero (Capricorn1)

can you post an image of the outgoing email
SteveL13

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

Email Snapshot
Rey Obrero (Capricorn1)

just tried it and this is the result

x

can you try this using a different computer?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveL13

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

ASKER
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)

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SteveL13

ASKER
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
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.