By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

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

P: 1
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,attachment 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?
Attached Files
File Type: zip Private Sub Command1.zip (7.9 KB, 404 views)
Apr 25 '10 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,597
@JDCO
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.
Expand|Select|Wrap|Line Numbers
  1. '************************************************************************************************
  2.  
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5.  
  6. Set MyDB = CurrentDb
  7. Set rst = MyDB.OpenRecordset("Orders Qry", dbOpenForwardOnly)
  8.  
  9. With rst
  10.   Do While Not .EOF
  11.     Mailbody = Mailbody & ![OrderID] & " | " & ![CustomerID] & " | " & ![ShippedDate] & vbCrLf
  12.       .MoveNext
  13.   Loop
  14. End With
  15.  
  16. rst.Close
  17. Set rst = Nothing
  18.  
  19. oMail.Body = Mailbody
  20.  
  21. '************************************************************************************************
Apr 25 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.