Selecting records in lots [100's] (1 Viewer)

[lightray]

Hi there,

My exercise is to split a thousand or so email addresses down to managable groups of 100 [ISP prefers this too] I have easily created the query to extract the email adresses and used sendobject to get them to outlook. But what I need is an idea of how to select them in groups of 100. I have considered looping through a recordset, and creating a new output file for each hundred, is this the best way to tackle this? thanks in advance.

 

[jdraw]

If you read them as a recordset, you could add a field eg. LotNumber. You increment the LotNumber +1 for each Loop (100 or whatever you decide). So now your emails have a LotNumber and you can use that to send emails to Outlook.

Just a thought.

 

[lightray]

Thx for your idea jdraw. I have bounced off that to consider just a counter in the loop, which is zerod down.. Here is my code [much thanks to the forum]

Private Sub SendEmail_Click()
On Error GoTo Err_SendEmail_Click

Dim stDocName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEmail As String
Dim intCountR As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("qryEmailAdress")
intCountR = 0

With rs
Do While Not .EOF
strEmail = strEmail & .Fields("Email") & ";"
intCountR = intCountR + 1
If intCountR = 100 Then 'process groups inside the loop
intCountR = 0
strEmail = Left(strEmail, Len(strEmail) - 1)
DoCmd.SendObject , , , "Email@To", , strEmail, "promotion", "Hey there! this weeks deal is just amazing...", True
strEmail = ""
End If
.MoveNext
Loop
.Close
End With

SetEmails: 'process the remainder
strEmail = Left(strEmail, Len(strEmail) - 1)
DoCmd.SendObject , , , "Email@To", , strEmail, "promotion", "Hey there! this weeks deal is just amazing...", True

Exit_SendEmail_Click:
Exit Sub

Err_SendEmail_Click:
MsgBox Err.Description
Resume Exit_SendEmail_Click

End Sub