Looping.....HELP!!!

[CBragg]

Right, so far i have some code which should loop through every record on my for and send an email until it gets to the end of the file. For some reason it wont move to the next record using rsEmail.MoveNext, if i change this to the docmd.gotorecord,, acnext record then it creates a new one at the end of the records. Can anyone help?? Here's my code:

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim intEmail as Integer
Dim i as Integer

Set rsEmail = CurrentDb.OpenRecordset("SELECT * FROM tbl WHERE SEND = True")

'Refresh recordset
'There may be a more efficient way of doing this, but I usually use
rsEmail.MoveLast
rsEmail.MoveFirst

'Count the records in the recordset
intEmail = rsEmail.RecordCount

'Set the email subject, body, and add the first recipient email
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "TESTING"
EmailSend.Recipients.Add rsEmail!Email

'Loop through all records and add the email addresses, one at a time, to the email item
For i = 1 to intEmail
rsEmail.MoveNext
EmailSend.Recipients.Add Email
Next

'Send the email
EmailSend.Send

Set rsEmail = Nothing
MsgBox "Your Message has been sent successfully!"

End Sub


Any ideas would be greatly appreciated.

Cheers.

 

[crosmill]

I would write the for like this

For i = 0 to intEmail
EmailSend.Recipients.Add Email
rsEmail.MoveNext
Next

I also find that recordcount always gives me a value of 1 if there are records and 0 if there arent any, don't ask me why, but it might be worth checking the value ou get.

Also I wouldn't use for, I'd use..

While NOT rsEmail.EOF
EmailSend.Recipients.Add Email
rsEmail.MoveNext
Wend

'this bit (While NOT rsEmail.EOF) means: while the recordset is not at the end of file...


If you want the content of the email to change you need to put...

'Set the email subject, body, and add the first recipient email
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "TESTING"
EmailSend.Recipients.Add rsEmail!Email

...in the loop/while somthing like this

While NOT rsEmail.EOF

'Set the email subject, body, and add the first recipient email
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = reEmail("Content")*
EmailSend.Recipients.Add rsEmail("emailAddress")*

EmailSend.Recipients.Add Email
rsEmail.MoveNext
Wend

*You need to check this is the correct format to pull data from the record set, I code ASP and that's how it's done with ASP, it should be the same with VBA but somtimes the syntax is a little different.

Let me know how you get on.

 

[CBragg]

Thanks for your reply, sorted it eventually though. Here's the code i used in case your interested:

Refresh
DoCmd.Requery
DoCmd.GoToRecord , , acFirst

Dim NameSpace As Object
Dim EmailSend As MailItem
Dim EmailApp As Object


Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim intEmail As Integer
'Dim i As Integer

Set rsEmail = CurrentDb.OpenRecordset("qrySend")


'Count the records in the recordset
intEmail = rsEmail.RecordCount

'Set the email subject and body
EmailSend.Subject = "Information Systems - Hardware Audit"
EmailSend.HTMLBody = "Testing

Do Until rsEmail.EOF
EmailSend.Recipients.Add rsEmail!Email
'Loop through all records and add the email addresses, one at a time, to the email item
rsEmail.MoveNext
Loop
'Sends when at end of file
EmailSend.Send

Set rsEmail = Nothing
MsgBox "Your Message has been sent successfully!", vbInformation, "Microsoft Outlook"

End Sub

Cheers