email query recordset

5 pts.
Tags:
Microsoft Access
VBA
"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 "
ASKED: September 25, 2008  4:34 PM
UPDATED: September 25, 2008  5:43 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following