VBA Loop

[DreamOn]

Im trying to set the attached form to email selected people...

It is a continuous form so that everyone in the table can be selected to be sent the email...



by ticking the tick box next to the persons name and then clicking the button it will loop through the table to find all those who have been selected and then add them to the To: box in outlook.

Im good with VBA just not that good with creating loops and then setting them to the To: column.

looking at the attached DB may show you what i mean.

if you need any more info please ask!

[pbaldy]

The loop you have commented out is the way I would go:

Do While Not rst.EOF

The array just over-complicates things. Within the loop you can build a string for your To:

VariableName = VariableName & rst!Email & ";"

You can trim off the trailing semi-colon at the end, but in my experience it doesn't hurt anything (email goes out correctly). Unless you're sure everyone will have an email address, I'd add a criteria to your SQL that made sure that field wasn't Null.

[DreamOn]

I have been tweaking it alot and i have it to work with the following code:

Dim rst As DAO.Recordset
Dim intCounter As Integer
Dim intUpBound As Integer
Dim arrCount() As Variant
Dim strSubject As String, strJoint As String
DoCmd.Requery

Set rst = CurrentDb.OpenRecordset("SELECT * FROM ServiceUser WHERE (ServiceUser.Check)=True")

rst.MoveLast
rst.MoveFirst
intUpBound = rst.RecordCount
ReDim arrCount(1 To intUpBound) As Variant
For intCounter = 1 To intUpBound
arrCount(intCounter) = rst!Email
rst.MoveNext
Next intCounter
strSubject = InputBox("Insert E-mail Subject", "Email Subject")
strJoint = Join(arrCount, "; ")
DoCmd.SendObject , , , , , "" & strJoint & "", strSubject, , True
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE ServiceUser SET Check=False")
Check10 = False
DoCmd.Requery
DoCmd.SetWarnings True
Exit Sub

While testing it, it sometimes crashes the Database, if i changed it to the way you mentioned will it still crash? think ill try it and see what happens, thanks for your help!

[pbaldy]

Well, it's a lot simpler the other way, though I'm not sure it would stop the crashing. One thing you should do set rst to Nothing at the end. Do you get any message that indicates what is causing the error?

[DreamOn]

None, im using win7 and it just goes white and then the curser is just the loading sign and it just pops up saying MS Access is not responding 'End Now'.

i will comment out the current way i do it and will set it up the way you mentioned, and see what happens.