recordsetclone to loop through and send email

Posted on 2012-08-13
Last Modified: 2012-08-13
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 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") & ";"
        End If
End With

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

Open in new window

Question by:Shawn
    LVL 30

    Accepted Solution

    Switch line 19 and 20 in above code

            End If
    LVL 1

    Author Comment

    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.
    LVL 1

    Author Comment


     .MoveFirst          'move to the first record

    works now. thanks again
    LVL 30

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now