Send email based on a filter????

[Shadez]

I have a combo box that applies a filter to a form.

Private Sub Combo62_AfterUpdate()
myString = Me.Combo62
Me.RecordSource = "SELECT BMembers.*, [BQualify].[Category] FROM (BQualifyList INNER JOIN BQualify ON [BQualifyList].[BQualify]=[BQualify].[Category]) INNER JOIN BMembers ON [BQualify].[MemberID]=[BMembers].[CustomerID] WHERE ((([BQualify].[Category])=""" & myString & ""&quot);"
'Me.Filter = "[BQualify] = " + Me![Combo62]
'Me.FilterOn = True
End Sub

this works fine. I have tried several methods to try and send an email out to the records that are returned. I have had no luch I have used the bulk emailing finctions before. I am really stuck on this and would appreciate any help I may get here.

 

[Shadez]

Ok Seems I got the code to work. I only have one problem left here. If the filter only returns one record. The email is Address dor that record is doubled up so it shows in outlook as test@test.com;test@test.com

Private Sub EmailResults_Click()
Set db = CurrentDb
myString = Me.Combo62
SQL = "SELECT BMembers.*, [BQualify].[Category] FROM (BQualifyList INNER JOIN BQualify ON [BQualifyList].[BQualify]=[BQualify].[Category]) INNER JOIN BMembers ON [BQualify].[MemberID]=[BMembers].[CustomerID] WHERE ((([BQualify].[Category])=""" & myString & ""&quot);"
Set rs = db.OpenRecordset(SQL)


With rs
Do While Not .EOF
SENDmail = BEmail & IIf(Len(BEmail) > 0, ";", "&quot & rs!BEmail
.MoveNext
Loop

End With

If BEmail = "" Then
MsgBox "There are currently no client records listed under this category", vbExclamation, "E-Mail Error"
Exit Sub
End If

DoCmd.SendObject acSendNoObject, , , , , SENDmail, , , , True


End Sub