Sending one e-mail to multiple recipients in a subform 1

[mandrake06]

Hi guys. I'm extremely new at this, so sorry if I don't provide enough information - if there's anything you need, I'll try my best to answer. Sorry about my english!

I have two tables, "Students" and "Courses", linked together by a "Course Details" table forming a many-to-many relationship. In the "Courses" form I've set up a continuous subform ("Sub StudentsFCourses") that lists all the students that participated in each one of the Courses (it looks pretty weird when I type it but I hope it makes sense).

I've managed to set up some code that lets me e-mail, one at a time, each one of the students in that subform via a command button, using Outlook:
================================
Private Sub CmdMail_Click()
On Error Resume Next

Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stWho As String '-- Reference to Students table
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to assign ticket to
stWho = Me.StudentName
stWhere = "Students.StudentName = " & "'" & stWho & "'"
'-- Looks up email address from Students table
varTo = DLookup("[StudentEMail]", "Students", stWhere)

stSubject = ""

stText = ""

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1


Exit_CmdMail_Click:
Exit Sub


End Sub
=============================
Obs: I don't need to fill out the subject line, e-mail text or attachments as those will change pretty frequently.

What I'd like to do is place another command button in the "Courses" form that will allow me to send just one e-mail with its "BCC" field filled out with all the e-mails from the students in the subform, and the "To:" field containing my own e-mail address (always constant) so that I get a copy of it.

Is that possible? I've read the wonderful threads in the FAQ related to sending e-mails via Access but I could not find any way to do the above specifically. Maybe I missed something? Any help from you guys would be really welcome!

 

[ProgramError]

Take a look at

SendObject link

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[mandrake06]

Thank you! I still couldn't figure out how to organize those commands into code though

 

[GingerR]

Hi--first I think you should have a 'StudentID' field in your Students table, instead of using StudentName as a key: what if two people have the same name? Or someone take several classes over some years, and their name is stored in the CourseDetails table as Mary Smith, then she gets married and her name is now Mary Jones? Do you have the relationships set to change Smith to Jones? What if there is another Mary Jones?

That being said, what you want to do is write code to loop thru your student email addresses and make one long string, i.e.

John.Doe@Microsoft.com; Steve.Jones@BCC.org; maryk009@cfl.rr.com

get it?

You are not looping thru the subform; you are just looping thru the table.

So it'd be something like this when you click the button: (you will have to change control names to match yours)

'This is a query which finds the Students in CourseDetails, filtering on the CourseID that is currently showing in the form you are on. You can instead write a query joining the two tables Students and CourseDetails and put in the criteria of the CourseID on the form you are on, then just open that as the recordset instead.

Dim strSQL, strBCC As String
Dim rs As DAO.Recordset

strSQL = "SELECT CourseDetails.CourseID, Student.StudentEmail " & _
"FROM CourseDetails INNER JOIN Student ON CourseDetails.StudentName = Student.StudentName " & _
"WHERE CourseDetails.CourseID= " & [Forms]![Form99]![CourseID] & ";"

Set rs = CurrentDb.OpenRecordset(strSQL)

'Move to First record
rs.MoveFirst

While Not rs.EOF
   'Add email to strBCC
   If Len(strBCC) = 0 Then
         strBCC = rs!StudentEmail
   Else
         strBCC = strBCC & "; " & rs!StudentEmail
   End If
   'Move to next record and go to the beginning of the loop
   rs.MoveNext
Wend
Set rs = Nothing

Then continue on with your code, using strBCC as the portion of the email string for BCC. Again, you will have to tweak this to use your field/table names.


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mandrake06]

Thank you Ginger!

Actually what I meant to say was that StudentID is actually the key in the Students table. I've attached a link (I can also e-mail) to a rough copy of my database, with just the relevant tables and forms.

In frmCourses, the code I described is attached to the "Email" (CmdMailStudent) button.

I've tried adding a variation of your code to the "Send Mail to Class" (CmdMailClass) button, but I think I've done something wrong, as I get this error when trying it:

"Run-time error '3061': Too few parameters. Expected 1."

Maybe I've replaced the wrong field names by mistake?

By the way thanks for the help and quick replies!

 

[GingerR]

I can't download anything on this computer (my work machine) plus I'm not in the habit of fixing people's databases for them. Please post your code so we can work together. Also you can search this forum for "Error Too Few Parameters" and find some possible solutions.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[ProgramError]

That error usually indicates you have missed one of the parameters from a command



Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.