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

Access / Mail Merge

P: 1
I'm trying to create an e-mail statement that pulls data from two table is simply the Student information (e-mail, name, phone etc) and the other table is a Transaction table that can have multiple rows per student.

Using Word mail merge I'm able to send a single e-mail to each entry in the Student table, however in the body of the e-mail I'd like to include the Student's transactions. Students - Transactions is a one to many relationship. I cannot seem to find a way to add the Student's Transaction information to the e-mail.

Side note...does anyone know where I can simply purchase a MS Access solution to manage scrip transactions (

[email removed]
Mar 15 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 3,072
This is best handled by sending the email from VBA and by constructing the message body from code with a recordset processing loop like in the following code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnMail_Click()
  3. Dim rs As Recordset
  4. Dim strBody As String
  6. ' Get the tansaction data
  7. Set rs = CurrentDb.OpenRecordset("tblTransactions")
  8. If rs.EOF And rs.BOF Then
  9.    MsgBox "No data for email"
  10.    GoTo exit_btnMail_Click
  11. End If
  13. strBody = "Dear sir, " & vbCRLF & vbCRLF
  14. strBody = strBody & "We found the following transactions: " 
  16. rs.MoveFirst
  17. ' loop to fill Body text
  18. While Not rs.EOF
  19.    strBody = strBody & vbCRLF & rs!YourField   
  20.    rs.MoveNext
  21. Wend
  22. strBody = strBody & vbCRLF & "Sincerely," 
  23. strBody = strBody & vbCRLF & "The email team" 
  25. ' Send the Object
  26. ' The fields strTo, strCC, strBCC and strSubject correspond to the string you have to fill yourself
  27. ' e.g. by using for strTo: Me.StudentEmail
  28. ' when the emailaddress is on the form.
  29. ' or hardcoded: ""
  31. DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubject, strBody
  33. exit_btnMail_Click:
  34. End Sub
Mar 17 '07 #2

Post your reply

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