Email Cmd

[Katayon]

I have a Command that generates an email message as follows:

Private Sub cmdSendOverdue_Click()

Dim strMessage As String 'Body of message
Dim strSubject As String 'Email Subject
Dim strTo As String 'To address
strTo = "TEST"
strSubject = "DSL Overdue Items"

strMessage = "The following items are overdue to the DSL." & vbCrLf & vbCrLf
strMessage = strMessage & "" & qryOverdueItems & vbCrLf & vbCrLf

DoCmd.SendObject acSendNoObject, , acFormatHTML, strTo, , , strSubject,
strMessage, True

End Sub

The issue is, I would like to list the query/table qryOverdueItems, as
attempted in the second 'strMessage' above.
Unfortunetly, this is not working. How can I get the above email message to
display the entire results of the query (basically be able to list an entire
table).

Is this possible?

Thanks in advance for your help.

 

[John Nurick]

One possibility, of course, is to make a little report based on the
query, so it includes the recipient's name and lists the items; then use
DoCmd.SendObject to send the report.

Otherwise you need to build a string containing the data returned by the
query. Assuming the query returns one record per overdue item, with one
field per record, you could do something like this:

....
Dim rsOverdueItems As DAO.Recordset
Dim strOverdueItems As String

Set rsOverdueItems = CurrentDB.OpenRecordset( _
"qryOverdueItems", dbSnapshot)
With rsOverdueItems
Do Until .EOF
strOverdueItems = strOverdueItems & Fields(0).Value _
& vbCrLf
.MoveNext
Loop
.Close
End With

....

strMessage = "The following items are overdue to the DSL." _
& vbCrLf & vbCrLf & strOverdueItems & vbCrLf

DoCmd.SendObject ...

 

[Katayon]

Thanks John. I had a feeling it wasn't going to work the way I was hoping.

I think I will just try instead to create a macro that will send the table
to an .rtf file. From there I'll do mail merge.

Thanks again.