Looping issue in VBA

[Toolman29]

I'm needing some help with a small piece of code. I'm tring to send out an email through Lotus Notes, using a query that defines the emails to send on my bcc line. I've got everything working except for the loop. The loop current will go through and send the correct number of emails for the total amount of records (ie five records five emails are sent, three records three emails sent, etc), however the same email address (the first record) is getting the email each time (instead of rolling to next record and pulling it's email address). Any help is appreciated.

Private Sub Send_Click()
Dim Email As String
Dim ref As String
Dim notes As String
Dim nosession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim db As DAO.Database

Set db = CurrentDb
Set qdf = db.QueryDefs("Test")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset

Email = rs!EmailAddress
ref = Me.Subject
notes = Me.Memo

Set nosession = CreateObject("Notes.NotesSession")
Set noDatabase = nosession.GETDATABASE("", "")
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
Set noDocument = noDatabase.CreateDocument

rs.MoveFirst
Do Until rs.EOF
With noDocument
.Form = "Memo"
.BlindCopyTo = Email
.Subject = ref
.Body = notes
.SaveMessageOnSend = True
.PostedDate = Now()
.Send 0
End With
rs.MoveNext
Loop

MsgBox "Your email has been sent", vbInformation

Set nosession = Nothing
Set noDatabase = Nothing
Set noDocument = Nothing
End Sub

 

[PHV]

Replace this:
.BlindCopyTo = Email
with this:
.BlindCopyTo = rs!EmailAddress

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Toolman29]

PHV - Thanks for the quick reply. Tried your suggestion and am still getting three emails sent out (my test record amount) to the first [EmailAddress] record. Second and third [EmailAddress] records are getting none.

 

[PHV]

Move this line:
Set noDocument = noDatabase.CreateDocument

just after the following:
Do Until rs.EOF

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Toolman29]

Thanks again PHV. I moved the line under Do Until rs.EOF. Before moving the line, my Sent box would show one email record. After moving the line, my Sent box shows three records (all going to the same [EmailAddress] location, the first record of my Recordset/query). BTW, the first [EmailAddress] is my own, this is how I know the receiving end is getting three emails (the record count of the recordset). Still no emails to the second and third records of the recordset.

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Toolman29]

What's posted is the actual code for the On_Click command of a button on the interface. Interface has just two fields "Subject" and "Memo".
The query is pulling from a table containing thousands of records. Through the query I've narrowed the recordset to three records. Running the query by itself, it shows three records all with different email addresses (field name is [EmailAddress]).

 

[PHV]

What about this ?

...
ref = Me.Subject
notes = Me.Memo
Set nosession = CreateObject("Notes.NotesSession")
Set noDatabase = nosession.GETDATABASE("", "")
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
rs.MoveFirst
Do Until rs.EOF
    Set noDocument = noDatabase.CreateDocument
    With noDocument
        .Form = "Memo"
        .SendTo = rs!EmailAddress
        .Subject = ref
        .Body = notes
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0
    End With
    rs.MoveNext
Loop
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Toolman29]

This would be what the code looks like now after changing it to your suggestions. I'm seeing three emails being sent, all going to the email address of the first record of my query.

The only difference between what you have posted and what I now have is spaces between some lines (one before rs.MoveFirst and one before Set nosession=Create......).

 

[PHV]

I've changed this:
.[!]SendTo[/!] = rs!EmailAddress

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Toolman29]

Hi PHV,

I've attempted to change the send line from the BCC to the To line (.SendTo). No change, still sends three emails to the first records [EmailAddress].