Send email with Access 2007

[blsaint]

I am new at this and want to send an email to each recipient a query gives
me. I am sending the email from a form based on the query. The problem is
that it only sends an email to the first line, the loop does not work.
Please help.

Private Sub cmdSendEmails_Click()

Dim db As DAO.Database
Set db = CurrentDb()

With Me.RecordsetClone
Do Until .EOF
DoCmd.SendObject acSendNoObject, Cierre_de_cuentas, acFormatPDF,
Nz(Me!EMAIL), , , _
"Su Número de Cuenta: " & Nz(Me!NUM_CTA) & " está por vencer.", _
"Favor de revisar la fecha de cierre de su cuenta. Comuníquese a la
Oficina de Finanzas lo antes posible.", Yes
.MoveNext
Loop
End With

db.Close
Set db = Nothing

End Sub

 

[bcap]

You are looping through the form's RecordsetClone, but you are getting the
email address from the form, not from the RecordsetClone.

Also, the database variable db is not needed, you never use it.

Try this:

Private Sub cmdSendEmails_Click()

With Me.RecordsetClone
Do Until .EOF
DoCmd.SendObject acSendNoObject, Cierre_de_cuentas,
acFormatPDF, Nz(!EMAIL), , , _
"Su Número de Cuenta: " & Nz(!NUM_CTA) & " está por
vencer.", _
"Favor de revisar la fecha de cierre de su cuenta.
Comuníquese a la Oficina de Finanzas lo antes posible.", Yes
.MoveNext
Loop
End With

End Sub

 

[blsaint]

Thank you for your help, it worked!

How can I end or change the message error that appears when I cancel send?
It says: Run time error '2501'. The send object action was canceled."

 

[bcap]

Private Sub cmdSendEmails_Click()

On Error GoTo HandleError

With Me.RecordsetClone
Do Until .EOF
DoCmd.SendObject acSendNoObject, Cierre_de_cuentas,
acFormatPDF, Nz(!EMAIL), , , _
"Su Número de Cuenta: " & Nz(!NUM_CTA) & " está por
vencer.", _
"Favor de revisar la fecha de cierre de su cuenta.
Comuníquese a la Oficina de Finanzas lo antes posible.", Yes
.MoveNext
Loop
End With

EndOfSub:
Exit Sub

HandleError:
Select Case Err
Case 2501
Resume Next
Case Else
Err.Raise Err
Resume EndOfSub
End Select

End Sub

 

[blsaint]

I tried it, but it keeps giving me the same message box with the error. When
I cancel the event of sending, I just want it to finish without giving me any
error message. Thanks for your help.