recordset filter question

[Arlan]

I would like to take a query that returns "X" number of rows, and
filter it based on a PK field, then e-mail that record to the e-mail
address that also resides within the row source....

I have the following code...(Thanks to many posts within the usenet
groups..)

Public Function sendmail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailmessage As String
Dim strEmailadress As String
Dim strFilter As String



strSubject = "Special Order-HELD"
strEmailmessage = "The following special order is being rejected by
the system because the customer # is bad"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qry E-Mail Bad Custids")

strFilter = rst!ID (This is where I have attempted to apply a
filter)


rst.MoveFirst
Do Until rst.EOF
strEmailadress = rst![addy]
DoCmd.SendObject acQuery, "qry E-mail Bad Custids", "MS-DOSText
(*.txt)",strEmailadress, , , strSubject, strEmailmessage
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Function


This code works fine, it loops through the record set to obtain the
e-mail address, but it e-mails the entire contents of the query to ALL
e-mail addresses.

I have also found that this code PROMPTS me to SEND each e-mail after
the message has been created...I remember reading some posts about a
bug with send object recordsets...

Any help is appreciated.

Thanks

Arlan

 

[Danny S.]

Arlan,

You need to do two things here: First, apply the filter in
the OpenRecordset statement. Second, from the Access Help
file: "If you leave both the Object Type and Object Name
arguments blank, Access sends a message to the mail
application without any database object." You don't want to
send an object - you want to send a message.

So, try these alternate statements:

.....
Set rst = dbs.OpenRecordset("SELECT ID FROM [qry E-Mail Bad
Custids] WHERE ID = " & IDFilterCriteria)
.....
DoCmd.SendObject , , , strEmailadress, , , strSubject,
strEmailmessage
.....

Let me know if it works, or you need further help.

Danny S.