looping through list when creating the email (1 Viewer)

[deekras]

I have a table with director names and the teachers who work for them and passwords for each teacher - one record per teacher. so that if a director has 4 teachers, there are 4 records.

i would like to send an email to the directors to tell them their teachers' passwords. i imagine that i have to loop through the table somehow as i create the body of the email, but i am lost.

the relevant fields in the table are
orderNo
DirectorName
DirectorEmail
TeacherName
TeacherPassword

thanks for your help.

 

[ajetrumpet]

i assume you already know how to create a mailitem object in outlook, or whatever client you are using. so...to send a list of pw's, something like this would work:

dim rs as recordset
set rs=currentdb.openrecordset("table", dbopendynaset)

'open the entire table first
rs.movelast
rs.movefirst

with rs
   'move to your records here however you wanna do it
   '(e.g. - FINDFIRST(), MOVE(), FINDRECORD(), etc...)

      olMailItem.HTMLbody = "Here is a list of teacher passwords:<br><br>
         "Teacher: " & !NameField & "<br>Password: " & !PasswordField & 
         "<br><br>Teacher: " & !NameField & "<br>Password: " 
         & !PasswordField & "<br><br>", etc etc...
end with

rs.close
set rs=nothing

you can create a loop too, to make the table population easier to write into the email. this is code you would loop through, to make block after block:

"Teacher: " & !NameField & "<br>Password: " & !PasswordField & 
         "<br><br>Teacher: " & !NameField & "<br>Password: " 
         & !PasswordField & "<br><br>"

hope that helps you out.

 

[deekras]

I'm still stumped about how to write the loops.

I want something like this:

Dear DirectorName:
Below are the passwords of your teachers:
TeacherName1 password: 123
TeacherName2 password: 124
Please retain for your records.....

i guess there are 2 loops
one from director to the next director
and the second within each director, from teacher to teacher.

another issue, i am running this off of a query, not a table. i am getting an error on this line: Set rs = CurrentDb.OpenRecordset("qrySendPasswords", dbOpenDynaset) 3061. Too few parameters.

thanks

 

[ajetrumpet]

I'm still stumped about how to write the loops.

I want something like this:

Dear DirectorName:
Below are the passwords of your teachers:
TeacherName1 password: 123
TeacherName2 password: 124
Please retain for your records.....

i guess there are 2 loops
one from director to the next director
and the second within each director, from teacher to teacher.

another issue, i am running this off of a query, not a table. i am getting an error on this line: Set rs = CurrentDb.OpenRecordset("qrySendPasswords", dbOpenDynaset) 3061. Too few parameters.

thanks

you might be getting that error because you need to put params in for the query. do you have any params that are required? IF NOT, just write the SQL statement in the first argument of the openrecset() method instead of the actual object name. example:

currentdb.openrecordset("SELECT * FROM table", dbopendynaset)

also...what is your email client? outlook? if it is, do you know how to create mail items and HTML body text in access VBA code? you'll need that if you want to get this done....

 

[deekras]

Set rs = CurrentDb.OpenRecordset("select * from qrySendPasswords", dbOpenDynaset)
i have tried that. I get the same error. that query has a field whose criteria is that it is a certain field from the particular form.
WHERE ((tblOrders.OrderNo)=[Forms]![frmdates]![OrderNo])
do i have to put something in this openrecordset line?
(I always have that form open and when i run the query alone it works fine)

yes, i know how to create emails for Outlook in VBA. I just don't know how to write the loop. Thanks!

 

[ajetrumpet]

Set rs = CurrentDb.OpenRecordset("select * from qrySendPasswords", dbOpenDynaset)

deek,

this is not what i meant. I said...write the SQL of the query. do not select all the records from the query. at any rate, open the qry, go to SQL view, read what's in that window, and write the same thing in the code window on the openrecset() argument, instead of what you wrote above.

yes, i know how to create emails for Outlook in VBA. I just don't know how to write the loop. Thanks!

the loop will be written like this:

function SendEmail()

Dim STRdirector As String
Dim rs As Recordset
dim rsDirectors as recordset
Set rs = CurrentDb.OpenRecordset("SQL of the query!", dbOpenDynaset)
Set rsDirectors = CurrentDb.OpenRecordset("SELECT DISTINCT " & _ "
                         table.DirectorField FROM table", dbOpenDynaset)

rsDirectors.movefirst
STRdirector = trim(rsDirectors!DirectorField)

DO UNTIL rsDirectors.eof

    With rs
        .MoveFirst
            Do Until .EOF
                If Trim(!DirectorField) = STRdirector Then
                   [COLOR="Red"][B]CODE TO CREATE YOUR EMAIL HERE[/B][/COLOR]
                   .MoveNext
                Else
                   .MoveNext
                End If
            Loop
    End With

rsDirectors.movenext
STRdirector = trim(rsDirectors!DirectorField)

LOOP

    rs.Close
    rsDirectors.close
    Set rs = Nothing
    set rsDirectors = nothing
            
End Function

 

[deekras]

thanks for your reply.

so i put in the whole SQL of the query into the OpenRecordset- (see below) and i still get the error message 3061. Too few parameters. Expected 1.

Set rs = CurrentDb.OpenRecordset("SELECT tblOrders.DirectorName, tblOrders.OrderNo, tblOrders.Email, tblTeacherDetails.TeacherName, tblTeacherDetails.TeacherPassword, tblTeacherDetails.TeacherEmail, tblTeacherDetails.dtAddedForum FROM tblTeacherDetails INNER JOIN tblOrders ON tblTeacherDetails.OrderNo = tblOrders.OrderNo WHERE (((tblOrders.OrderNo) = [Forms]![frmdates]![OrderNo]) And ((tblTeacherDetails.dtAddedForum) Is Not Null))", dbOpenDynaset)

thanks for helping me with this.