So Close!! Creating Multi-Line Email With Loop Command

  • Thread starter Thread starter mj
  • Start date Start date
M

mj

Hi. I have some code that loops throught a recordset and
sends specific information (backOrders) to a specific
person via email. The recordset has multiple lines for
each recipient. I have one query that I use to pull my
email addresses from and another query with the data. I
make each line in the dataset a different line in the
message body. It works great except that, after I send a
person's information to them, the next person in the list
not only get's their own information, but also the
information from anyone who is in front of them in the
list of recipients. I must have something wrong with my
loop that appends one persons BackOrder set to the people
ahead of them in the list, but I can't figure it out. Any
suggestions would be great!

Set Db = CurrentDb

'This is my list of reps
Set rsBackOrderReps = Db.OpenRecordset("SELECT * " & _
"FROM [qryBackOrderReps]")

If Not rsBackOrderReps.EOF Then
Do While Not rsBackOrderReps.EOF

'This is my dataset
Set rsBackOrders = Db.OpenRecordset("SELECT * " & _
"FROM [qryBackOrders] WHERE [SalesRep]= '" &
rsBackOrderReps![SalesRep] & "'")

If Not rsBackOrders.EOF Then
Do While Not rsBackOrders.EOF

BackOrders = BackOrders & rsBackOrders!
[BackOrder] & vbCrLf

rsBackOrders.MoveNext

Loop
End If

Msg = BackOrders

DoCmd.SendObject
acSendNoObject, , , "Recipient", , , "BackOrders", Msg,
False

rsBackOrderReps.MoveNext

Loop
End If
 
mj said:
Hi. I have some code that loops throught a recordset and
sends specific information (backOrders) to a specific
person via email. The recordset has multiple lines for
each recipient. I have one query that I use to pull my
email addresses from and another query with the data. I
make each line in the dataset a different line in the
message body. It works great except that, after I send a
person's information to them, the next person in the list
not only get's their own information, but also the
information from anyone who is in front of them in the
list of recipients. I must have something wrong with my
loop that appends one persons BackOrder set to the people
ahead of them in the list, but I can't figure it out. Any
suggestions would be great!

Set Db = CurrentDb

'This is my list of reps
Set rsBackOrderReps = Db.OpenRecordset("SELECT * " & _
"FROM [qryBackOrderReps]")

If Not rsBackOrderReps.EOF Then
Do While Not rsBackOrderReps.EOF

'This is my dataset
Set rsBackOrders = Db.OpenRecordset("SELECT * " & _
"FROM [qryBackOrders] WHERE [SalesRep]= '" &
rsBackOrderReps![SalesRep] & "'")

If Not rsBackOrders.EOF Then
Do While Not rsBackOrders.EOF

BackOrders = BackOrders & rsBackOrders!
[BackOrder] & vbCrLf

rsBackOrders.MoveNext

Loop
End If

Msg = BackOrders

DoCmd.SendObject
acSendNoObject, , , "Recipient", , , "BackOrders", Msg,
False

rsBackOrderReps.MoveNext

Loop
End If
Click to expand...

Presumably somewhere you've declared a string variable named BackOrders.
You keep adding to it in your inner loop, but you never clear it, which
is something you would want to do for each new rep. Add a new line just
after the outer Do While loop:
Do While Not rsBackOrderReps.EOF
Click to expand...
BackOrders = ""

That should take care of it.
 
Perfect! Thanks, Dirk. I had tried doing that with
BackOrders=Nothing after the inner loop without any
success. This is a huge help. Thanks again.
-----Original Message-----
Hi. I have some code that loops throught a recordset and
sends specific information (backOrders) to a specific
person via email. The recordset has multiple lines for
each recipient. I have one query that I use to pull my
email addresses from and another query with the data. I
make each line in the dataset a different line in the
message body. It works great except that, after I send a
person's information to them, the next person in the list
not only get's their own information, but also the
information from anyone who is in front of them in the
list of recipients. I must have something wrong with my
loop that appends one persons BackOrder set to the people
ahead of them in the list, but I can't figure it out. Any
suggestions would be great!

Set Db = CurrentDb

'This is my list of reps
Set rsBackOrderReps = Db.OpenRecordset("SELECT * " & _
"FROM [qryBackOrderReps]")

If Not rsBackOrderReps.EOF Then
Do While Not rsBackOrderReps.EOF

'This is my dataset
Set rsBackOrders = Db.OpenRecordset("SELECT * " & _
"FROM [qryBackOrders] WHERE [SalesRep]= '" &
rsBackOrderReps![SalesRep] & "'")

If Not rsBackOrders.EOF Then
Do While Not rsBackOrders.EOF

BackOrders = BackOrders & rsBackOrders!
[BackOrder] & vbCrLf

rsBackOrders.MoveNext

Loop
End If

Msg = BackOrders

DoCmd.SendObject
acSendNoObject, , , "Recipient", , , "BackOrders", Msg,
False

rsBackOrderReps.MoveNext

Loop
End If
Click to expand...

Presumably somewhere you've declared a string variable named BackOrders.
You keep adding to it in your inner loop, but you never clear it, which
is something you would want to do for each new rep. Add a new line just
after the outer Do While loop:
Do While Not rsBackOrderReps.EOF
Click to expand...
BackOrders = ""

That should take care of it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top