Default Text for Email Report

[John Big Booty]

I have a buttton that generates and emails a report.

It run a code on the "On click" event that looks like this:


Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String


stDocName = "RPT_SFPOStatusGen"

DoCmd.SendObject acReport, stDocName, "Rich Text Format(*.rtf)", , , , "Order Tracking Report", "PO Tracking Report attached"
Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub


That runs fine, but what I would like to do is replace the "PO Tracking Report attached" text with a longer more complicated piece of text that is stored in a table TBL_EMText.

I'm sure there is a simple solution however it has eluded me this afternoon

 

[Ron_dK]

I would suggest to make a new report, based on the table TBL_EMText,
create a button and run :

Private Sub CommandX_Click()
On Error GoTo Err_CommandX_Click

    Dim stDocName As String

    stDocName = "Your new report here"
    DoCmd.SendObject acReport, stDocName

Exit_CommandX_Click:
    Exit Sub

Err_CommandX_Click:
    MsgBox Err.Description
    Resume Exit_CommandX_Click
    
End Sub

Hth

 

[Hayley Baxter]

I have a buttton that generates and emails a report.

It run a code on the "On click" event that looks like this:


Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String


stDocName = "RPT_SFPOStatusGen"

DoCmd.SendObject acReport, stDocName, "Rich Text Format(*.rtf)", , , , "Order Tracking Report", "PO Tracking Report attached"
Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub


That runs fine, but what I would like to do is replace the "PO Tracking Report attached" text with a longer more complicated piece of text that is stored in a table TBL_EMText.

I'm sure there is a simple solution however it has eluded me this afternoon

If I am understanding your question correctly, it is not the report you wish to replace but the actual text for the body of the message which comes from a table. All you need to do is grab the fieldname from the table which holds the text you need and everything else remains the same

replace "PO Tracking Report attached" with [yourfieldname]

Hay

 

[John Big Booty]

Yes Hay

Youve got it in one. However my question is what is the syntax that I need to use to achieve my goal?

 

[Hayley Baxter]

Yes Hay

Youve got it in one. However my question is what is the syntax that I need to use to achieve my goal?

Private Sub CmdEmail_Click()
DoCmd.SendObject acSendReport, "ReportNameHere", "FormatHere", messageto, , , "SubjectHere", [Body], True
End Sub

Grab the fieldname from your table by using surrounding square brackets. The syntax above should work as I've just tested it here.

Anymore problems, let me know and I'll post you the sample.

Hay

 

[John Big Booty]

Thanks Hay

I was pretty sure it should have looked something like that.

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "RPT_SFPOStatusGen"

DoCmd.SendObject acReport, stDocName, "Rich Text Format(*.rtf)", , , , "Order Tracking Report", [Mssg], True
Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub

Where [Mssg] is a field of TBL_EMText, this table hold only one field with one record; being the text body I want to insert in the email.

This is what I have, but it's causing an error message that read;

"Microsoft Office Access can't find the field "I" reffered to in your expression"

 

[Hayley Baxter]

With that error message, it sounds as though you may have quotation marks surrounding the field name.

Here is a quick sample anyway, I apologise for the poor field names etc but I just did this very quickly to let you see an example.

Hay

 

[John Big Booty]

Thanks Hay

Thats great.

I now see were I was going wrong, and have rearanged that part of the database.

 

[Hayley Baxter]

No probs, glad you got it sorted

Hay