Email from Access

[JeroenMioch]

Hey guys,

I have a code wich i know has the records in my filter :

Dim rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0

Now i found a piece of code that selects emailadressen from my table.

Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailAddress] FROM Members
WHERE [EmailAddress] Is Not Null")
With rst
Do Until .EOF
strTo = strTo & ![EmailAddress] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo)-1)
Set rst = Nothing

My question is how to combine the two codes, or is there a better way to pull emailadresses from my table that are IN my recordset ?
So i have a form and im filtering records. I want to email the people in my recordset.

Im using this to email :

DoCmd.SendObject , , , strTo, , , , , True

Any help would be appreciated !

Greetings, Jeroen

[ranman256]

Dim rst
Dim strTo As String

Set rst = CurrentDb.OpenRecordset("SELECT [EmailAddress] FROM Members WHERE [EmailAddress] Is Not Null")
With rst
    While Not .EOF
         strTo = strTo & .Fields("EmailAddress").Value & ";"
       .MoveNext
    Wend
   .Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing

DoCmd.SendObject acSendReport, "rptMyReport", acFormatPDF, strTo, , , "subject", "body"

if you use a form, put the sql in a list box, so you have a list of the emails, and a list of reports,
then the user can pick any report to print, and send it to everyone in the list.

[JeroenMioch]

Hi Ranman,

Im not sending reports (yet anyway).
I just want the "to" filled with emailadresses that meet my criteria.

So the emailadresses must be in my current recordset and they must not be empty.
Some objects dont have emailadresses.

When i adjust your code to my situation i still get every record in my table rather then the filtered ones from the recordset.

[ranman256]

then the query needs to limit the list to valid emails,
and the OUTPUT can be excel, or pdf or text.

[JeroenMioch]

Well outputting it to a text file before i actually send them is not a bad idea for starters as i just allmost send some 200 mails accidently
But the idea is to eventually put the emailadresses directly into the emails.
Still i get the complete set of records and i can't seem to combine my recordset with the SQL that pulls the emailadresses from the table.