We help IT Professionals succeed at work.

recordsetclone to loop through and send email

Shawn
Shawn asked
on
I am trying to send emails to recipients in a subform using its recordset. The code below is a quick draft and I'm not sure what's going wrong...it seems to spin its wheels, then crash.

Any ideas?


Dim strEmail As String
Dim strMailSubject As String
Dim strMSG As String

strMailSubject = "New translation"
strMSG = Me.tmpTranslatorMessage & _
vbCrLf & vbCrLf & Forms![frmtrad_commandes2].UserName & vbNullString

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = [sbfrmTranslationPreselectTranslators].Form.RecordsetClone

With rst
    Do Until .EOF
        If rst("PreSelectionStatusID") = 1 Then
        strEmail = strEmail & rst("email") & ";"
        .MoveNext
        End If
    Loop
    .Close
End With


strEmail = Left(strEmail, Len(strEmail) - 2)
DoCmd.SendObject to:=strEmail, Subject:=strMailSubject, MessageText:=strMSG

Open in new window

Comment
Watch Question

Retired IT Professional
CERTIFIED EXPERT
Commented:
Switch line 19 and 20 in above code

        End If
       .MoveNext

Author

Commented:
oops, got it.

the button works now the first time you click on it but doesn't if you click on it again. I think I'm missiing something to reset the recordset every time the button is clicked.

Author

Commented:
added    

 .MoveFirst          'move to the first record

works now. thanks again
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Welcome!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.