Action From RecordCount

[Guest]

I am trying to figure out how I can run code after reaching a set number of
records.

I have a form that allows a user to type a subject and a message in unbound
text boxes. Then the user clicks a send button that emails a message to
addresses from the address table. The problem is there may be up to 3000
addresses in the table, and our mail server only allows 200 addresses to be
sent at 1 time.

I need to be able to all addresses, 200 at a time, with a single click.

Here is what I have so far:
******************************
....
For i = 1 To rst.RecordCount
If Len(rst!EmailAddress) > 0 Then
strTo = strTo & "; " & rst!EmailAddress
rst.MoveNext
End If
Next i
Dim objEml As Outlook.MailItem
Set objEml = objOutl.createItem(olMailitem)

With objEml
.BCC = strTo
.Subject = Me.msgSubject
.Body = Me.msgBody
.Attachments.Add "Attachment.doc", olByValue, 1, "Attachment"
.Send
End With
Set objEml = Nothing
....
********************************
The above code adds all the address into 1 variable strTo.
If I could get it to know when it reaches 200 then send it, then start over
with 201, and continue sending for each 200, until all are sent.

Thanks in advance!

I am using MS Access 2003 and MS Outlook 2003, SP2

This work related email ~ NOT SPAM

Dwight Griffin

 

[Guest]

You just need a loop within a loop, but since your recordcount may not be
evenly divisible by 200, you need to account for that. Also, I think your
End If is in the wrong place. It looks like if it hits a record where
Len(rst!EmailAddress) = 0, it will go into an endless loop. In the example
below, I moved it to where I think it really belongs.

Do While Not rst.EOF
For i = 1 To rst.RecordCount
If Len(rst!EmailAddress) > 0 Then
strTo = strTo & "; " & rst!EmailAddress
End If
rst.MoveNext
'Check to make sure there are more records to process
If rst.EOF Then
Exit For
End If
Next i
Loop