"For each" statement VBA - MS access calling a lotus notes odbc connection

have an access db with an odbc connection to lotus notes mailbox.  VBA code automatically runs query and then sends results to sender of email received in timed intervals.  Trying to incorporate a field in the query to "group" the whole looping process, so basically to run the whole VBA process for each ML_SUPV_EMPLID in this case.  VBA code:

Function Email()

Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim session As Object
Dim EmbedObj1 As Object
Dim Eml2 As String
Dim Reports_To_Name As String
Dim Recp As String
Dim Reports_to_FNAME As String
Dim Subject2 As String
Dim From As String
Dim CountOfID As String
Dim Email_text As String
Dim Email_text1 As String
Dim db As DAO.Database
Dim rstTEST As DAO.Recordset
Dim rstData As DAO.Recordset
Dim tablename As String
'Dim SampleEmails As DAO.Recordset
Dim rstqry_Direct_Reports As DAO.Recordset
Dim ML_SUPV_EMPLID As Object
Dim Bob As Object

Set db = CurrentDb()
'Set rstData = db.OpenRecordset("Data")
'Set rstSampleEmails = db.OpenRecordset("SampleEmails")
Set rstqry_Direct_Reports = db.OpenRecordset("qry_Direct_Reports")
Set session = CreateObject("Notes.NotesSession")
UserName = session.UserName
MsgBox (UserName)
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = session.GETDATABASE("", MailDbName)
If Maildb.ISOPEN = True Then
Else
Maildb.OPENMAIL
End If
rstqry_Direct_Reports.MoveFirst
Set Bob = [rstqry_Direct_Reports]![ML_SUPV_EMPLID]
For Each Bob In rstqry_Direct_Reports
'Do While Not rstqry_Direct_Reports.EOF
Eml2 = rstqry_Direct_Reports![Eml]
'Subject2 = [Forms]![EmailSample]![Text2]
'Email_text = "Dear " & rstqry_Direct_Reports![First] & "," & Chr(13) & Chr(13)
'Email_text1 = Email_text & [Forms]![EmailSample]![EmailText1] & Chr(13) & Chr(13)
Email_text2 = "Below are the employees and non-employees for" & " " & [rstqry_Direct_Reports]![ML_REPORTS_TO_NAME] & " " & "as of" & " " & [rstqry_Direct_Reports]![Today] & Chr(13) & Chr(13)
Email_text3 = Email_text3 & Chr(13) & [rstqry_Direct_Reports]![EMPLID] & "     " & [rstqry_Direct_Reports]![Name] & "     " & [rstqry_Direct_Reports]![Employee Type]
Email_text4 = Email_text2 & Email_text3

'& "Username: " & rstTEST![UserName] & Chr(13) & Chr(13) & "Password: " & rstTEST![Password]
'From2 = [Forms]![EmailSample]![From]
On Error GoTo errorhandler1
rstqry_Direct_Reports.MoveNext
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.sendto = Eml2
MailDoc.Principal = "DatabasesRUs"
MailDoc.Subject = "Works"
MailDoc.Body = Email_text4
MailDoc.SaveMessageOnSend = True

MailDoc.PostedDate = Now()


'Loop
Next

MailDoc.Send 0, Recipient

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set session = Nothing
Set EmbedObj1 = Nothing


errorhandler1:

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set session = Nothing
Set EmbedObj1 = Nothing
MsgBox ("Email Sent")
End Function

shawngertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
I doubt whether it's possible to loop over a RecordSet that way, using a For Each. I think a recordset isn't enumerable, see also http://msdn.microsoft.com/en-us/library/5ebk1751.aspx

0
leonstrykerCommented:
Definently not. For Each loop will not work. You can use For i = 1 to rstqry_Direct_Reports.Count instead.

Leon
0
shawngertAuthor Commented:
leonstryker,

would you be able to modify my code to incorporate the i = 1?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

leonstrykerCommented:
Try this loop (Note, this is not the full function. I it is just a loop)
Dim lngCount As Long, lngRecordCount As Long
    rstqry_Direct_Reports.MoveLast
    rstqry_Direct_Reports.MoveFirst
    lngRecordCount = rstqry_Direct_Reports.RecordCount
    For lngCount = 0 To lngRecordCount
        Eml2 = rstqry_Direct_Reports![Eml]
        Email_text2 = "Below are the employees and non-employees for" & " " & [rstqry_Direct_Reports]![ML_REPORTS_TO_NAME] & " " & "as of" & " " & [rstqry_Direct_Reports]![Today] & Chr(13) & Chr(13)
        Email_text3 = Email_text3 & Chr(13) & [rstqry_Direct_Reports]![EMPLID] & "     " & [rstqry_Direct_Reports]![Name] & "     " & [rstqry_Direct_Reports]![Employee Type]
        Email_text4 = Email_text2 & Email_text3
        On Error GoTo errorhandler1
        rstqry_Direct_Reports.MoveNext
        Set MailDoc = Maildb.CreateDocument
        MailDoc.Form = "Memo"
        MailDoc.sendto = Eml2
        MailDoc.Principal = "DatabasesRUs"
        MailDoc.Subject = "Works"
        MailDoc.Body = Email_text4
        MailDoc.SaveMessageOnSend = True
        MailDoc.PostedDate = Now()
    Next

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sjef BosmanGroupware ConsultantCommented:
I don't see much difference with the original Do-While loop... You could change the Do-While-Not in Do-Until, but that's all.

By the way, a Do-loop is probably even faster than the indexed way Leon suggested.
0
shawngertAuthor Commented:
any chance you could post a code sample sjef bosman?
0
shawngertAuthor Commented:
in the recordset, is there a way to have the loop send a new email whenever the ML_SUPV_EMPLID changes (with the resulting records)?
0
shawngertAuthor Commented:
no
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.