Broadcast e-mail from a query - simple problem 1

[DwayneS]

Novice user of vb with Access. I'm trying to send a seperate e-mail to each of a few dozen people I can find easily with a simple select query.

I've read through all kinds of suggestions on this board and tried lots of your code, but mine still won't run.

Someone PLEASE look at this and tell me what I'm doing wrong.

I really appreciate you guys. Usually I fix whatever I need to do by just watching and copying code. This time I can't

Here's what I'm trying to get running:

Private Sub MailGroup_Click()
' I'm trying to hard-wire a broadcast e-mail to this query for now ...
' Later I can do more by feeding parameters to the query.
' eMailGroup is a very simple select query
' With one of its fields being email

Dim dbs As Database
Dim rs As Recordset
Dim subject As String
Dim message As String
Set dbs = CurrentDb
subject = "Test"
message = "This is only a test"

' The following line of code always fails with
' "Run-time error '13' Type mismatch"
Set rs = CurrentDb.OpenRecordset("eMailGroup&quot

With rs
Do While Not .EOF
DoCmd.SendObject acSendNoObject, , , , , , subject, message, False
.MoveNext
Loop
End With

' Is this done right to send individual e-mails to every record from the query?

End Sub

 

[petersdaniel]

What version of Access are you on? petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[DwayneS]

Access 2000. Sorry. I should have said that.

 

[KyleS]

If you haven't set DAO ahead of ADO (and you really shouldn't) you need to set a reference to Microsoft DAO 3.6 (I think - whatever the latest version you have available is) and change

Dim dbs As Database
Dim rs As Recordset

to:

Dim dbs As DAO.Database
Dim rs As DAO.Recordset Kyle

 

[DwayneS]

I am sorry to be such a pain. You are helping me greatly and I appreciate. Now I died on the next line of code. If you have a minute look at the e-mail address problem in the docmd statement. I really appreciate your help

Private Sub MailGroup_Click()
' I'm trying to hard-wire a broadcast e-mail to this query for now ...
' Later I can do more by feeding parameters to the query.
' eMailGroup is a very simple select query
' With one of its fields being email

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim subject As String
Dim message As String
Dim eMailAdd As String
Set dbs = CurrentDb
subject = "Test"
message = "This is only a test"

Set rs = CurrentDb.OpenRecordset("eMailGroup&quot

With rs
Do While Not .EOF

' I've tried all kinds of tricks, but I can't get it to find
' the email field from my query.

' If I put this line in the code, it sends e-mails (All to this add)
' eMailAdd = "dwayne@pptsysop.com"

' As soon as I try something like the
' next line, it fails with "Variable not defined".

eMailAdd = email

DoCmd.SendObject acSendNoObject, , , eMailAdd, , , subject, message, False
.MoveNext
Loop
End With

' Is this done right to send individual e-mails to every record from the query?

End Sub Dwayne Streeter, CPA, CITP

 

[DwayneS]

One more line of code, PLEASE. I won't bother you again, I promise. Dwayne Streeter, CPA, CITP

 

[KyleS]

It's no bother, just been tied up at work...

Why do you have eMailAdd = email in there?

You could set eMailAdd = rs!YOUR EMAIL FIELD, that would work.

Or on your DoCmd.SendObject line you could do this and do away with the eMailAdd string.

DoCmd.SendObject acSendNoObject, , , !Your eMail Field, , , subject, message, False

Give that a try, all access needs to do is recognize the eMail address as a string and it should work.

Kyle