Access / Mail Merge

[mbigelow]

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]

[nico5038]

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.

Private Sub btnMail_Click()
 
Dim rs As Recordset
Dim strBody As String
 
' Get the tansaction data
Set rs = CurrentDb.OpenRecordset("tblTransactions")
If rs.EOF And rs.BOF Then
   MsgBox "No data for email"
   GoTo exit_btnMail_Click
End If
 
strBody = "Dear sir, " & vbCRLF & vbCRLF
strBody = strBody & "We found the following transactions: " 
 
rs.MoveFirst
' loop to fill Body text
While Not rs.EOF
   strBody = strBody & vbCRLF & rs!YourField   
   rs.MoveNext
Wend
strBody = strBody & vbCRLF & "Sincerely," 
strBody = strBody & vbCRLF & "The email team" 
 
' Send the Object
' The fields strTo, strCC, strBCC and strSubject correspond to the string you have to fill yourself
' e.g. by using for strTo: Me.StudentEmail
' when the emailaddress is on the form.
' or hardcoded: "[e-mail]"
 
DoCmd.SendObject acSendNoObject, , , strTo, strCC, strBCC, strSubject, strBody
 
exit_btnMail_Click:
End Sub
 

Nic;o)