Email from Access using results from a Query with DoCmd.SendObject

[jmpWashDC]

Hi,
I am a beginning Access programmer and I'm having a little problem getting Access to send email.
I have had success with the following line--
DoCmd.SendObject SendNoObject, , , "jmp@yadayada.com", , , "EMAIL from blah blah", "my message"

This line opens outlook, adds the address & subject and adds my message to the text area.

All I want to do now is change the recipient FROM> jmp@yadayada.com TO> the results from a Query called QRYEmail which has 50 email addreses in the first column called Email

I looked through the various FAQs, did some searching, tried using the suggested code but I can't get the various suggestions to work.

eventually I will expand from 50 email addresses to some unknown number, but for now, I think the answer to this question will be enough....

I assume this is an easy question for the many experts in this Forum

Thanks,
jmp

 

[jmpWashDC]

I just used this code:

Dim db As Database
Dim rs As Recordset
Dim SQL As String
Dim EMail As String

Set db = CurrentDb
SQL = "SELECT * FROM QRYEmail;"
Set rs = db.OpenRecordset(SQL)

rs.MoveFirst

Do While rs.EOF = False
EMail = EMail & rs!EMail & ";"
rs.MoveNext
Loop


If EMail = "" Then
MsgBox "There are currently no client records listed under this category", vbExclamation, "E-Mail Error"
Exit Sub
End If

DoCmd.SendObject acSendNoObject, , , , , EMail, , , , True

etc...
this seem to work well so far
the FAQ was very helpful after all!!!
jmp

 

[mdthornton]

You need to build your list of recipients first then do the send, eg:

Dim rst as Recordset
Dim sRecipients as string

sRecipients = ""
set rst = myDB.openrecordset("QRYEmail&quot
do while not rst.eof
sRecipients = sRecipients + rst.Email + ";"
rst.movenext
loop

doCmd.sendobject etc Best Regards,
Mike