email from a table as text

[rat_1965]

OK guru's here is what I am trying to do. I have a table with the data I want emailed. There is more than one record in the table and I would like to send out the email with all the data in the body of one email. I can't do this as an attachment as some people are using blackberries. The table (actually one of 4 different ones that need to be emailed) is not the record source for the current form. Any ideas?

 

[Travis]

1. Open a recordset on the Data
2. Loop through the Recordset appending each record to a string variable
3. Set the Body Property of your Email = to the String.

 

[rat_1965]

I don't mean to sound stupid but I am not too sure how to set the recordset to the table I want. The loop part I think I have down. Could I get a sample?

 

[Travis]

Dim rst as New ADODB.RecordSet
Dim cnn as New ADODB.Connection
Dim sSQL as String
Dim sMessage as String


Set cnn=Currentproject.Connection

'You can use the Query Builder to get you query built and then copy the SQL View here
sSQL="Select * From [Your Table Name] Where [Criteria Field] ='" & sCriteria & "'"

rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly

rst.MoveFirst
Do while Not rst.EOF
     'Append the Fields to the Message
     sMessage = sMessage & rst.Fields("FieldName") & vbCrLf
     rst.MoveNext
Loop

'sMessage will be the concatination of the records from the table when finished.