Sending Mass Email from Query

[lewis1682]

Okay so I have a query called "Filter_Contacts_Include" I am trying to send bulk emails using the Email field inside this query. This is my code:

Dim db As DAO.DatabaseDim rs As DAO.Recordset
Dim strEmail As String


Set db = CurrentDb
Set rs = db.OpenRecordset("Filter_Contacts_Include")


With rs
    Do While Not .EOF
        strEmail = strEmail & .Fields("Email") & ";"
        .MoveNext
    Loop
    .Close
End With


strEmail = Left(strEmail, Len(strEmail) - 1)


DoCmd.SendObject , , , strEmail, , , , , True


End Sub

However when run I get the error 3061, Too few parameters, expected six. The error is on the line

Set rs = db.OpenRecordset("Filter_Contacts_Include")

If I try and get the email address from a table it works exactly how it should.



Thank you.

[RuralGuy]

Can you open the "Filter_Contacts_Include" query by itself in Datasheet view?

[lewis1682]

Yes I can open the query in Datasheet view

[RuralGuy]

Try this:
Set rs = db.OpenRecordset("Filter_Contacts_Include", dbOpenDynaset)

[lewis1682]

No, I still get the same error.

[RuralGuy]

Have you tried a Compact and Repair yet? Something is weird. If that doesn't work then try *importing* your db into a fresh new db and see if the problem follows. It only take a minute or so.

[pbaldy]

I'm guessing there's a form reference in the query?

http://support.microsoft.com/kb/209203

[lewis1682]

Yes there is a form reference in another query which the query is linked to, this query searches the other query to look to see if a checkbox is set to true, if so showing the results. In the Include criteria field I have entered -1. What do I need to do so this code will work?

[RuralGuy]

Forms referenced in queries need to be running. Are they?

[lewis1682]

Yes they are running

[RuralGuy]

When something weird seems to be happening one of the first things I do is *import* the db into an fresh new db and see if the issue follows. Give it a try: http://www.btabdevelopment.com/ts/impnew

[lewis1682]

I still get the same error, I will attach the SQL from the Filter_Contacts query and the SQL for the Filter_Contacts_Include. What I want to be able to do is after I have filtered the Filter_Contacts query with textboxes on a form on a button press it looks for all the results in that query with the include checkbox checked and send the email, this works perfectly when printing mailing labels but not this send email.

FIlter_Contacts SQL:

SELECT All_Contacts.ID, All_Contacts.Title, All_Contacts.FirstName, All_Contacts.LastName, All_Contacts.JobTitle, All_Contacts.Company, All_Contacts.AddressLine1, All_Contacts.AddressLine2, All_Contacts.Town, All_Contacts.County, All_Contacts.PostalCode, All_Contacts.Phone, All_Contacts.Fax, All_Contacts.Email, All_Contacts.Website, All_Contacts.Category, All_Contacts.IncludeFROM All_Contacts
WHERE (((All_Contacts.FirstName) Like "*" & [forms]![Filter_Contacts]![s_FirstName] & "*" Or (All_Contacts.FirstName) Is Null) AND ((All_Contacts.LastName) Like "*" & [forms]![Filter_Contacts]![s_LastName] & "*" Or (All_Contacts.LastName) Is Null) AND ((All_Contacts.Company) Like "*" & [forms]![Filter_Contacts]![s_Company] & "*" Or (All_Contacts.Company) Is Null) AND ((All_Contacts.County) Like "*" & [forms]![Filter_Contacts]![s_County] & "*" Or (All_Contacts.County) Is Null) AND ((All_Contacts.PostalCode) Like "*" & [forms]![Filter_Contacts]![s_PostalCode] & "*" Or (All_Contacts.PostalCode) Is Null) AND ((All_Contacts.Category) Like "*" & [forms]![Filter_Contacts]![CategoryList] & "*"));

Filter_Contacts_Include SQL:

SELECT Filter_Contacts.ID, Filter_Contacts.Title, Filter_Contacts.FirstName, Filter_Contacts.LastName, Filter_Contacts.JobTitle, Filter_Contacts.Company, Filter_Contacts.AddressLine1, Filter_Contacts.AddressLine2, Filter_Contacts.Town, Filter_Contacts.County, Filter_Contacts.PostalCode, Filter_Contacts.Phone, Filter_Contacts.Fax, Filter_Contacts.Email, Filter_Contacts.Website, Filter_Contacts.Category, Filter_Contacts.IncludeFROM Filter_Contacts
WHERE (((Filter_Contacts.Include)=-1));

[pbaldy]

The problem is that the open recordset line can't resolve the form reference. Here are the relevant lines from a db I have doing something similar:

Set qdfStatement = db.QueryDefs(strQuery)

qdfStatement![Forms!frmMainMenu!txtDate] = Forms![frmMainMenu]![txtDate]
qdfStatement![Forms!frmMainMenu!txtEndDate] = Forms![frmMainMenu]![txtEndDate]

Set rs = qdfStatement.OpenRecordset

[lewis1682]

Okay so what should I add to my button code because it is running of a separate query which is running off the Filter_Contacts one which has the form references in for the textboxes. This is what I have put in the criteria field for the Filter_Contacts form

 Like "*" & [forms]![Filter_Contacts]![s_FirstName] & "*" Or Is Null

on the following fields: Company, County, PostalCode, Category. Updating the criteria depending on the field.