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 :mad:

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

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 :mad:

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

Yes Hay

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

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

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"

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

Thanks Hay

Thats great.

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

No probs, glad you got it sorted :)

Hay