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 tables....one 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 (http://www.glscrip.com)?

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


nico5038
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()
  2.  
  3. Dim rs As Recordset
  4. Dim strBody As String
  5.  
  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
  12.  
  13. strBody = "Dear sir, " & vbCRLF & vbCRLF
  14. strBody = strBody & "We found the following transactions: " 
  15.  
  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" 
  24.  
  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: "emailname@home.com"
  30.  
  31. DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubject, strBody
  32.  
  33. exit_btnMail_Click:
  34. End Sub
  35.  
Nic;o)
Mar 17 '07 #2

Post your reply

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