Send mail to list of users from query

[ByteWarrior]

I have created a query that contains:
- last name
- first name
- email address
- course n°

This is the SQL:
SELECT tblParticipant.Participantname,
tblParticipant.Participantfirstname, tblCouse.Coursedescription,
tblParticipant.Email, tblParticipant.ParticipantCourse
FROM tblTrainers INNER JOIN (tblLocation INNER JOIN (tblCourse INNER
JOIN tblParticipant ON tblCourse.CourseID =
tblParticipant.ParticipantCourse) ON tblLocation.LocationID =
tblCourse.CourseLocation) ON tblTrainers.TrainerID =
tblCourse.CourseTrainer
WHERE (((tblParticipant.ParticipantCourse) Like
[forms]![frmCursus].[CursusID]))
ORDER BY tblParticipant.Participantname;

I want to be able to send all the participants from a course a reminder
mail (each time a new mail). I was trying to use SendObject to do this,
but I can't get it to work.

How can I accomplish this?

 

[Rick Brandt]

I have created a query that contains:
- last name
- first name
- email address
- course n°

This is the SQL:
SELECT tblParticipant.Participantname,
tblParticipant.Participantfirstname, tblCouse.Coursedescription,
tblParticipant.Email, tblParticipant.ParticipantCourse
FROM tblTrainers INNER JOIN (tblLocation INNER JOIN (tblCourse INNER
JOIN tblParticipant ON tblCourse.CourseID =
tblParticipant.ParticipantCourse) ON tblLocation.LocationID =
tblCourse.CourseLocation) ON tblTrainers.TrainerID =
tblCourse.CourseTrainer
WHERE (((tblParticipant.ParticipantCourse) Like
[forms]![frmCursus].[CursusID]))
ORDER BY tblParticipant.Participantname;

I want to be able to send all the participants from a course a
reminder mail (each time a new mail). I was trying to use SendObject
to do this, but I can't get it to work.

How can I accomplish this?

What problem did you have with SendObject?

 

[Ben]

What problem did you have with SendObject?

The problem is not really related to SendObject itself (I think). I use
another subroutine where is works fine.

The problem is that the subroutine, that is linked to a command button,
always gives an error message. I've already tried several solutions
that appeared in this group, but none of them works, they all spit out
errors. No emails are send.

I'm looking for the code that generates one mail to each recipient. All
the recipients are pulled from a query.





Ben

 

[Rick Brandt]

The problem is not really related to SendObject itself (I think). I
use another subroutine where is works fine.

The problem is that the subroutine, that is linked to a command
button, always gives an error message. I've already tried several
solutions that appeared in this group, but none of them works, they
all spit out errors. No emails are send.

I'm looking for the code that generates one mail to each recipient.
All the recipients are pulled from a query.

And that error message would be....

 

[Arvin Meyer [MVP]]

The demo at my website allows sending an email to multiple recipients using
SendObject. The list is geberated dynamically, but could easily be replaced
by the results of a query:

http://www.datastrat.com/Download/EmailSenate2K.zip

to generate the list with a recordset drawn on a query, look at this code
from my website which uses Outlook to send the email. By combing the 2 you
will have your solution:

http://www.datastrat.com/Code/MultipleEmail.txt
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I have created a query that contains:
- last name
- first name
- email address
- course n°

This is the SQL:
SELECT tblParticipant.Participantname,
tblParticipant.Participantfirstname, tblCouse.Coursedescription,
tblParticipant.Email, tblParticipant.ParticipantCourse
FROM tblTrainers INNER JOIN (tblLocation INNER JOIN (tblCourse INNER
JOIN tblParticipant ON tblCourse.CourseID =
tblParticipant.ParticipantCourse) ON tblLocation.LocationID =
tblCourse.CourseLocation) ON tblTrainers.TrainerID =
tblCourse.CourseTrainer
WHERE (((tblParticipant.ParticipantCourse) Like
[forms]![frmCursus].[CursusID]))
ORDER BY tblParticipant.Participantname;

I want to be able to send all the participants from a course a reminder
mail (each time a new mail). I was trying to use SendObject to do this,
but I can't get it to work.

How can I accomplish this?

 

[Ben]

I tried it, but I keep getting an error (too few arguments, -1):

This is the code that I use:

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT tblDeelnemers.DeelnemerNaam,
tblDeelnemers.DeelnemerVoornaam, tblCursus.CursusOmschrijving,
tblDeelnemers.DeelnemerEmail, tblDeelnemers.DeelnemerCursus FROM
tblTrainers INNER JOIN (tblLocatie INNER JOIN (tblCursus INNER JOIN
tblDeelnemers ON tblCursus.CursusID = tblDeelnemers.DeelnemerCursus) ON
tblLocatie.LocatieID = tblCursus.CursusLocatie) ON
tblTrainers.TrainerID = tblCursus.CursusTrainer WHERE
(((tblDeelnemers.DeelnemerCursus) Like [Forms]![frmCursus].[CursusID]))
ORDER BY tblDeelnemers.DeelnemerNaam")

If Not rs.EOF Then
Do While Not rs.EOF
ToList = rs![DeelnemerEmail]
DoCmd.SendObject acSendNoObject, , , ToList, , , "Daily Summaries",
"Division Summaries attached.", False
rs.MoveNext
Loop
End If

I copied the SQL code from a query I made (which is working correctly).
Any ideas where it goes wrong? (As the code stops, it's highlights the
SQL-line, so I think the error is in there).

 

[Arvin Meyer [MVP]]

Try using the name of the saved query in place of the select statement like:

Set rs = db.OpenRecordset("MyQuery", dbOpenSbapshot)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I tried it, but I keep getting an error (too few arguments, -1):

This is the code that I use:

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT tblDeelnemers.DeelnemerNaam,
tblDeelnemers.DeelnemerVoornaam, tblCursus.CursusOmschrijving,
tblDeelnemers.DeelnemerEmail, tblDeelnemers.DeelnemerCursus FROM
tblTrainers INNER JOIN (tblLocatie INNER JOIN (tblCursus INNER JOIN
tblDeelnemers ON tblCursus.CursusID = tblDeelnemers.DeelnemerCursus) ON
tblLocatie.LocatieID = tblCursus.CursusLocatie) ON
tblTrainers.TrainerID = tblCursus.CursusTrainer WHERE
(((tblDeelnemers.DeelnemerCursus) Like [Forms]![frmCursus].[CursusID]))
ORDER BY tblDeelnemers.DeelnemerNaam")

If Not rs.EOF Then
Do While Not rs.EOF
ToList = rs![DeelnemerEmail]
DoCmd.SendObject acSendNoObject, , , ToList, , , "Daily Summaries",
"Division Summaries attached.", False
rs.MoveNext
Loop
End If

I copied the SQL code from a query I made (which is working correctly).
Any ideas where it goes wrong? (As the code stops, it's highlights the
SQL-line, so I think the error is in there).

 

[Ben]

Arvin Meyer [MVP] schreef:

Try using the name of the saved query in place of the select statement like:

Sorry, I'm getting the error "Object needed (424)" when I do this:
Dim recipients As String
Dim rs As Recordset

Set rs = db.OpenRecordset("qryHerinnering", dbOpenSbapshot)
If Not rs.EOF Then
Do While Not rs.EOF
ToList = rs![DeelnemerEmail]
DoCmd.SendObject acSendNoObject, , , ToList, , , "Daily Summaries",
"Division Summaries attached.", False
rs.MoveNext
Loop
End If

 

[Arvin Meyer [MVP]]

a couple of errors I see:

1. you forgot to Dim db As DAO.Database
2. you forgot to qualify the recordset as DAO.Recordset
3. where is recipients being used?
4. dbOpenSbapshot is spelled dbOpenSnapshot

HTH,
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Arvin Meyer [MVP] schreef:

Try using the name of the saved query in place of the select statement
like:

Sorry, I'm getting the error "Object needed (424)" when I do this:
Dim recipients As String
Dim rs As Recordset

Set rs = db.OpenRecordset("qryHerinnering", dbOpenSbapshot)
If Not rs.EOF Then
Do While Not rs.EOF
ToList = rs![DeelnemerEmail]
DoCmd.SendObject acSendNoObject, , , ToList, , , "Daily Summaries",
"Division Summaries attached.", False
rs.MoveNext
Loop
End If