email

[jemar707311]

Okay experts - I need to send emails to people that have an email stored in the database, some do, some don't. query says to return not null, then i want to write code to send one email to all of these people......how? I don't want to send anything from the database, just need their addresses in the to field of an email....i can explain in more detail if necessary.

 

[BukHix]

This should do it for you:

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("YourQuery")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("EmailAddress").Value
DoCmd.SendObject , , , strEmail, , , "Subject", "Message Text"

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

"YourQuery" = Table or Query Name.
"EmailAddress" = Field in Table or Query holding the email address.

This will loop through an entire table or query sending an email to everyone in it.

 

[jemar707311]

It's not working - getting this error message. Runtime 3061:Two few parameters. Expected 1.

The query I am using is a parameter query, including a selection made from a combo box on a form. It doesn't seem to be reading it.

Now what?

 

[BukHix]

Hmmm, I never use paremeter queries so I am not qualified to answer that but I think you are going to need to use an SQL statement in place of the query.

The code I posted will work when using a normal query or table so hopefuly someone will come along and explain how to combine the two.

 

[rich.barry]

If you read the syntax for SendObject

outputformat (3rd value)
One of the following intrinsic constants:
acFormatDAP
acFormatHTML
acFormatRTF
acFormatTXT
acFormatXLS
If you leave this argument blank, Microsoft Access prompts you for the output format.

It implies that Access will spit the dummy if you leave it blank, so perhaps this is your problem.

try

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("YourQuery")

Do While Not rsEmail.EOF
strEmail = strEMail & rsEmail.Fields("EmailAddress").Value & ";"
rsEmail.MoveNext
Loop
Set rsEmail = Nothing

DoCmd.SendObject ,,acFormatTXT,strEMail,,,"Subject","Message Text"


and see if it makes a difference

[This message has been edited by rich.barry (edited 09-25-2001).]

 

[jemar707311]

Thanks, but still not working....it is hanging when opening the query.....at:
Set rsEmail = CurrentDb.OpenRecordset("YourQuery")

Any Other Suggestions Appreciated

 

[BukHix]

jemar70731, you need to use the name of your query in the place of "YourQuery" so if your query is actualy named queFilterEmail it would look like this:

Set rsEmail = CurrentDb.OpenRecordset("queFilterEmail")

 

[jemar707311]

I know that much!!!! :*)
I am reading the info about parameter queries now - that seems to be the problem.

 

[BukHix]

Sorry if I offended. I assumed you knew that but just wanted to make sure.

 

[jemar707311]

Not offended! I though that was funny - I wish that was the problem. Still not working - grrrrr.....frustrated, that's all.

 

[BukHix]

Does it work without a paremeter query? I mean have you tried making a new query with a couple of addresses and run the code that way? That would certainly isolate it to the PQ.

My best guess is that you are going to have to use a recordset and use a SQL statement in place of the paremeter query. Something like this:

Dim strMyQuery as String
strMyquery = txtGetQuery

SELECT Table1.ID, Table1.Name, Table1.Email FROM Table1 WHERE (((Table1.Email)="strMyquery"));

My knowledge of SQL is very minimum so I am not sure how it would actualy look in practice but I think that is in the same ball park.

Once you have that part correct you would modify the original code to get the value from a text box or combo box in the form instead of the paremeter query.

[This message has been edited by BukHix (edited 09-25-2001).]

 

[jemar707311]

here is the code i am working with now:

Private Sub Command21_Click()

Dim dbs As Database
Dim rst As Recordset
Dim intRecordCount As Integer
Dim qd As QueryDef
Dim strEmail As String

Set db = CurrentDb
Set qd = db.QueryDefs!QRY_SERVICE_by_category
qd.Parameters![CategoryID] = [Forms]![frm_Vendors_bycategory_service]![combo_Category]
Set rst = dbs.OpenRecordset("QRY_SERVICE_by_category")

Do While Not rst.EOF
strEmail = strEmail & rst.Fields("VendorEmail").Value & ";"
rst.MoveNext
Loop
Set rst = Nothing

DoCmd.SendObject , , , strEmail, , acFormatTXT, "Subject", "Message Text"
End Sub

 

[rich.barry]

You are defining dbs as the database, then using db in the code.
Use either one or the other consistently through the code.
Also I stuffed up the order of the SendObject constants. It should be:

DoCmd.SendObject ,,acFormatTXT,strEMail,,,"Subject","Message Text"