Good Evening,

I am not a programmer or developer, but I've muddled my way through
creating a large Access Database to manage the students at the school
where I am Principal.

I have a querry which lists all of the students who were tardy, and
their parent email address ([Mom Email], and [Dad Email]. I know how
to create the VB code to start an email to an individual parent using
this information, but in my query I have a list of 40 students/recrds
(x 2 fields = 80 email addresses). I want to create a form or
something where I open the list click one button and start an email
with all 80 of those emails in the BCC field of the email.

Does anyone know of a way to do this?

Thank you so much in advance!
Curt

principalfuller (AT) gmail (DOT) com wrote:

This is untested but should be close. You could send a report using
SendObject but I left that option blank. What words you don't
understand you can highlight and press F1 in a code module to read the help.

Sub SendParentsEmail()
Dim strE As String
Dim strNote As String
Dim blnEditMsg As Boolean
Dim rst As Recordset

'display in the email program, don't automatically send
blnEditMsg = True

'create a message body
strBody = "Parents, tardiness is not permitted in my school!"

'modify your query name
Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)

'loop through the records
Do While Not rst.EOF

'modify the email field name(s)
strE = rst!EmailDad & ";" & rstEmailMom & ";"

rst.MoveNext
Loop

'I don't know if you need the trailing ;. It you dont,
'the following code line will remove it. you might want to
'experiment to see if you do or don't.
strE = Left(strE, Len(strE) - 1)

'ok, the list is created of email names for the bcc
'send an email, just send the subject/body.
'you might want to change the email addy and do it to a smaller
'list and try it out first.
DoCmd.SendObject acSendNoObject, , acFormatTXT, , , strE, _
"Student Tardiness", strBody, blnEditMsg

End Sub

Thank you so very much. I had to make a few minor revisions, but in
case anyone else is looking for a similar answer here is the code that
I used:

Dim strE As String
Dim strNote As String
Dim blnEditMsg As Boolean
Dim rst As Recordset

'display in the email program, don't automatically send
blnEditMsg = True

'create a message body
strBody = "(Automated Message): Good Morning... "

'modify your query name
Set rst = CurrentDb.OpenRecordset("UnexcusedTardyEmail",
dbOpenSnapshot)

'loop through the records
Do While Not rst.EOF

'modify the email field name(s)
If rst!ParentEmail = "" Or rst!ParentEmail = " " Then
MsgBox rst!FullName & " does not have an email address"
Else
strE = strE & rst!ParentEmail & ", "
End If
rst.MoveNext
Loop
strE = Left(strE, Len(strE) - 2)

DoCmd.SendObject acSendNoObject, , acFormatTXT,
"adalley (AT) pinellasprep (DOT) org", "cfuller (AT) pinellasprep (DOT) org", strE,
"Unexcused Tardiness", strBody, blnEditMsg

----
I created a single field in the querry that had both of the parent's
email addresses, so I only to include one. And then I had to make
sure that field wasn't empty, otherwise it wouldn't open an email
correctly.
Hope this helps, and thanks again for your help!!