Including results of a query/recordset in body of an email?

[JDCO]

I have a database I've created to send out tailored reports to recipients on email. This was done using a module (which creates the outlook session etc and has all the handling info etc on it) and a form with a command button + click event with the mailbody/subject,attachm ent path etc on it.

Everything works fine like this and currently reports are sent in the form of an attachments to the tailor made email for each recipient. What we want to do now though is instead of having each report on an attachment, we'd like the report detail to be included in the body of the email. The purpose of the attachments is for them to be completed and sent back which isn't happening.. so if recipients could just reply to the original email (with the detail on) its much easier for all concerned. I'm sure this is possible but I'm stuck on how to achieve it.

The attachment is based on a query - which is refreshed for each email according to the recipient (and data) so I was hoping to be able to reference in code to the SQL query in the on click event. I'm assuming I need to assign it to a string maybe as a recordset and do a loop so I get all the records from the query on my email. Its the coding for it that I'm struggling with and where to put it amongst the other code already written.

Any help would be great.. I've attached a file showing what i've got - hope this helps?

[ADezii]

Hi

I have a database I've created to send out tailored reports to recipients on email. This was done using a module (which creates the outlook session etc and has all the handling info etc on it) and a form with a command button + click event with the mailbody/subject,attachm ent path etc on it.

Everything works fine like this and currently reports are sent in the form of an attachments to the tailor made email for each recipient. What we want to do now though is instead of having each report on an attachment, we'd like the report detail to be included in the body of the email. The purpose of the attachments is for them to be completed and sent back which isn't happening.. so if recipients could just reply to the original email (with the detail on) its much easier for all concerned. I'm sure this is possible but I'm stuck on how to achieve it.

The attachment is based on a query - which is refreshed for each email according to the recipient (and data) so I was hoping to be able to reference in code to the SQL query in the on click event. I'm assuming I need to assign it to a string maybe as a recordset and do a loop so I get all the records from the query on my email. Its the coding for it that I'm struggling with and where to put it amongst the other code already written.

Any help would be great.. I've attached a file showing what i've got - hope this helps?

Here is the general concept:

1. Create a Recordset based on your Query, for this demo I used the Orders Qry of the Northwind Sample Database.
2. Loop through the Recordset and concatenate Field Values (I used 3 Fields) to a String Variable (Mailbody) inserting a Carriage Return/Line Feed after each Record.
3. Assign the Variable (Mailbody) to the Body Property of the Instance of Outlook.

'************************************************************************************************
   
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Orders Qry", dbOpenForwardOnly)

With rst
  Do While Not .EOF
    Mailbody = Mailbody & ![OrderID] & " | " & ![CustomerID] & " | " & ![ShippedDate] & vbCrLf
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing
    
oMail.Body = Mailbody

'************************************************************************************************