email query recordset
"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.
<code>
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
rst.MoveNext
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, ""
Loop
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
GetOrderBody_Exit:
Exit Function
GetOrderBody_Err:
MsgBox Error$
Resume GetOrderBody_Exit
End Function
"
Software/Hardware used:
Software/Hardware used:
ASKED:
September 25, 2008 4:34 PM
UPDATED:
September 25, 2008 5:43 PM
Answer Wiki:
Hello Roundy.
One way to accomplish this, would be using 2 querys (and 2 loops, nested).
let;s say:
emails_query = query to get the <b>distinct </b>email accounts having new records.
shipments_query = query to get the shipments of one specific email address (the current from the emails_query)
I don't really know the syntax you should use here, so make the necessary corrections, and pay atention to the logic only.
<pre>Set qdf = CurrentDb.QueryDefs("emails_query")
Set rst = qdf.OpenRecordset
Do Until rst.EOF
rst.MoveNext
strInfo = strInfo & rst!stCompanyName & vbTab
set qdf2 = CurrentDb.QueryDefs("shipments_query")
Set rst2 = qdf2.OpenRecordset
Do Until rst2.EOF
rst2.MoveNext
strInfo = strInfo & "Ref No." & rst2!siShipmentReference1 & vbCrLf
Loop
rst2.Close
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(</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
