Sending a Recordset in E-mail body

[Mkauley]

I've hit a bit of stumbling block and hope someone can help. I'm attempting
to generate an e-mail in access that has my company's release schedule
attached as an RTF file and then put a list of specific products in the
message of the body. My code for such is below. I'm having three problems
with the data...

1.) I have to open the report before I run the SendObject command because of
ther Where clause. If I don't open the report first, it claims that it
cannot find it.

2.) I receive an error on the OpenRecordset SQL expression because I put a
Where clause in there (another set of quotes).

3.) I can't get the recordset data to post into the body of the message. If
I take out the recordset code leaving a generic message body, it works fine.

Any help would be VERY appreciated.

(here's the code...)

On Error GoTo Err_Command39_Click

Dim st3MonthCond As String
Dim stMsgbody As String
Dim rs As Recordset

'Define the string for the month release lists
st3MonthCond = "[Release Date] Between #" & DateAdd("m", -2, Date) & "#
AND #" & DateAdd("m", 3, Date) & "#"

Set rs = CurrentDb.OpenRecordset("SELECT [tblData].[Sales Code] AS SC,
[tblData].[EAN13 Bar Code] AS [Barcode], [tblData].ITEM_DESC AS [Desc],
[tblData].[Unit Price] AS Price FROM [tblData] WHERE
((([tblData].[Released])=â€Yesâ€)); ")

With rs
.MoveLast
.MoveFirst
Count = .RecordCount
End With

stMsgbody = "Sales Code - Bar Code - Description - Price"

For Counter = 1 To Count

stMsgbody = stMsgbody & vbNewLine & rs![SC] & " - " & rs![Barcode] & " -
" & rs![Desc] & " - " & rs![Price]

rs.MoveNext

Next Counter

rs.Close


'Open, Send and Close the 3 month list
DoCmd.OpenReport "rptNewRelease", acViewPreview, , st3MonthCond
DoCmd.SendObject acSendReport, " rptNewRelease ", acFormatRTF,
"(e-mail address removed)", , , "Release Information", stMsgbody
DoCmd.Close acReport, " rptNewRelease "

Exit_Command39_Click:
Exit Sub

Err_Command39_Click:
MsgBox Err.Description
Resume Exit_Command

End Sub

(...end of code)

 

[Alex Dybenko]

Hi,
to send report in message body you can use a following code:
http://maug.pointltd.com/access/Queries/TipDetail.asp?TipID=71

to "pre-filter" report - you can build report's recordsource query SQL based
on criteria and then send report

 

[Guest]

((([tblData].[Released])='Yes')); ")

Single quotes inside double quotes. Or if the field is boolean,

((([tblData].[Released])=True)); ")

What version of Access are you using? Access 2000 needs to
be SR3 to get this to work, A2003 needs to be ? SR1 ?

Also, RTF will arbitrarily truncate memo fields, and make sure
you have extra space at the end of numeric fields.

(david)

 

[Malcolm Cook]

....you might consider using rs.gettext instead of your loop also....