Adding contents of second table to body of email

[Skyshadow5]

Hi, I have code that sends an email to each person in a table, the table has four columns, Firstname, Lastname, Email Address, Amount.
The Amount is a total made up from one or more values. What I want to do is add to the body of the email message the results of a second table that holds the detail that make up the total amount.
For example, the email currently looks like this:

Dear John Smith,
The sum of £ 123.45 was paid to your bank account today.

Regards,
Company Name

What I would like it to do is look like this:

Dear John Smith,
The sum of £ 123.45 was paid to your bank account today.

Inv nbr 123456 £ 50.00
Inv nbr 987654 £ 50.00
Inv nbr 654321 £ 23.45

Regards,
Company Name

The second table already exists and there is a linking ID between the two tables but I can't work out how to get only the info
per person into each email. There are usually between 20 and 50 individual emails sent and only having a total amount is not giving enough info, I need to show the breakdown of the total per the example.

This is the code currently being used to send the emails (some info changed for anonymity),
there are two tables 20 - REMITTANCE and 20 - REMITTANCES, when all emails are sent, two delete queries run that clear out the tables ready for next time:
============================================================================================
Public Sub Create_Rem()

' Run Audit Table insert
Create_Audit ("Remittances")

Dim strEmailText As String

' Create remittances
Dim rst3 As DAO.Recordset
Set rst3 = CurrentDb.OpenRecordset("20 - REMITTANCE")
Do Until rst3.EOF = True

Dim olApp As Object 'Added by programmer Hugh Lerwill
Dim olMail As Object 'Added by programmer Hugh Lerwill

Const olMailItem = 0 'Added by programmer Hugh Lerwill

Set olApp = CreateObject("Outlook.Application") 'Added by programmer Hugh Lerwill
Set olMail = olApp.CreateItem(olMailItem)

strEmailText = rst3!FIRSTNAME & "," & vbCr
strEmailText = strEmailText & vbCr
strEmailText = strEmailText & "This email is your remittance advice totalling £" & rst3!SumOfINV_AMNT & "." & vbCr
strEmailText = strEmailText & "" & vbCr
strEmailText = strEmailText & "" & vbCr
strEmailText = strEmailText & "Kind regards," & vbCr
strEmailText = strEmailText & "" & vbCr
strEmailText = strEmailText & "Finance Team" & vbCr

With olMail
.To = rst3!Email
'.CC = If required
.Subject = "Remittance"
'.Attachments.Add strEmailPath
.Body = strEmailText
'.Display
.Send
End With

Set olMail = Nothing
Set olApp = Nothing

rst3.MoveNext
Loop

' Turn information off
DoCmd.SetWarnings False

'Run Queries to clear out the Remittance table
DoCmd.OpenQuery "03 - DELETE DATA IN REMITTANCE", acViewNormal, acEdit

DoCmd.OpenQuery "04 - DELETE DATA IN REMITTANCES", acViewNormal, acEdit

End Sub
============================================================================================
So my question is how do I add in the breakdown of invoices to the body of the email, thanks.

Clive

 

[bubba100]

Clive, you could use a query that returns the detail for the record id from the first query. The contents of a table are a recordset, the results of a query are a recordset. You have already created a recordset to pull the email address and the account name. Now create a second recordeset based on the record id from the first.

Now your question is how to format that information.

Bill