"I have a query (emailnotifytest) based on 2 tables . One table (shipmentdata) filters on new records. the other table (contact) filters which contains the email address filters whether the address is not null. The tables are joined by the companyname.
the code below works as far as sending the recordset to each different email address but I think the loop statements are in the wrong place.
what is happening is for example if the recordset contains 5 new records for that one email address it sends 5 email
each email progressing in the number of records in the email body. ie 1st email record 1. 2nd email records 1 & 2. 3rd email records 1 ,2 & 3 and so on.
how do I get all the records in 1 email. to each unique email.
Function GetOrderBody() As String
On Error GoTo GetOrderBody_Err
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim Subjectline As String
Dim Body As String
Dim strInfo As String
Dim webaddress As String
webaddress = "www.xxx.com"
Set dbs = CurrentDb
Set qdf = CurrentDb.QueryDefs("emailnotifytest")
Set rst = qdf.OpenRecordset
Do Until rst.EOF
strInfo = strInfo & rst!stCompanyName & vbTab
strInfo = strInfo & "Ref No." & rst!siShipmentReference1 & vbCrLf
Body$ = "***Do not reply to this e-mail. We will not receive your reply." & vbCrLf & vbCrLf & "The following new shipments have been shipped :" & vbCrLf & vbCrLf & strInfo & vbCrLf & vbCrLf & "See detailed activity at " & webaddress & " " & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & "XXX -Your Single Source For Shipping"
Subjectline$ = "New Shipments sent from XXX"
DoCmd.SendObject acSendNoObject, "", "", rst![outboundnotifyaddress], "", "", [Subjectline], [Body], False, ""
Set rst = Nothing
Set qdf = Nothing