Email code Problem

[peterbowles]

hi
I am using this code
Private Sub Command10_Click()


Dim strEmail As String
Dim rst As Recordset
Dim strSQL As String
Set rst = Me.RecordsetClone

rst.MoveFirst
Do

If rst!SendMail = True Then
'Place all the email address into the TO: field

strEmail = rst.Fields("Email").Value
DoCmd.SendObject acSendNoObject, , , Me.Email
'Send EMail

strSQL = "UPDATE qryEmail SET SendMail = false "
'Set the field back to false

DoCmd.RunSQL strSQL
Me.Requery

End If

rst.MoveNext
Loop Until rst.EOF

End Sub

I want to be able to place all email address that have the SendMail chkbox = true into the TO: Field, then set the ckbox back to false.

The problem is that is does not put all the email address in the same TO: field it does a new message for each record. and the update will only update the selected record

Any help would be great thanks

 

[KevinM]

Dim strEmail As String
Dim rst As Recordset
Dim strSQL As String
Dim x as Integer

Set rst = Me.RecordsetClone

rst.MoveFirst
Do

If rst!SendMail = True Then
'Place all the email address into the TO: field
strEmail = Trim(strEMail & rst!EMail & ";")

End If

rst.MoveNext
Loop Until rst.EOF

'Remove final ';'
x = Len(strEMail)-1
strEMail = Left(strEMail, x)

DoCmd.SendObject acSendNoObject, , , strEMail
'Send EMail

strSQL = "UPDATE qryEmail SET SendMail = false "
'Set the field back to false

DoCmd.RunSQL strSQL
Me.Requery

End Sub

 

[peterbowles]

Thanks works just how I wanted.

Just out of interest
what does (Trim) do

Thanks again

 

[KevinM]

Removes leading and trailing spaces.