megatronixs
Registered User.
- Local time
- Today, 18:10
- Joined
- Aug 17, 2012
- Messages
- 719
Hi all,
I have a form with a subform in it that gets feed by a query. I have in the subform as datasheet view the participants for a training (connected with the event_id). I use it as well to send email to all the participants.
I created a new functionality that will check if the participant did attend the training, if not, then there is a check box that stays blank. I would like to loop now trough the list and send only email to the team leaders where the check box is blank. I tried in the below code to, but the checkbox is always -1. The subform name is: "frm_recipient_subform" and the checkbox: "attendance"
This is the code I have so far:
(the email body I still need to change)
Greetings.
I have a form with a subform in it that gets feed by a query. I have in the subform as datasheet view the participants for a training (connected with the event_id). I use it as well to send email to all the participants.
I created a new functionality that will check if the participant did attend the training, if not, then there is a check box that stays blank. I would like to loop now trough the list and send only email to the team leaders where the check box is blank. I tried in the below code to, but the checkbox is always -1. The subform name is: "frm_recipient_subform" and the checkbox: "attendance"
This is the code I have so far:
Code:
Private Sub btn_unconfirmed_absence_Click()
Dim objItem As Object
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim oMail As Outlook.MailItem
Dim oApp As Object
Dim create_event_table As Recordset
Set create_event_table = CurrentDb.OpenRecordset("tbl_create_event")
Set oMail = objItem
Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)
Set db = CurrentDb()
Set MailList = db.OpenRecordset("MyEmailAddresses")
Dim training_name_text As String
Dim yesno As String
' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.
Do Until MailList.EOF
' This creates the e-mail
' We need to move it BEFORE we start the loop, since
' we don't want to make a bunch of e-mails, we just want one.
' this is where we loop through our list of addresses,
' and we add them to the RECIPIENTS collection
Do Until MailList.EOF
' This adds the address to the list of recipients
If Forms!frm_created_event_form!frm_recipient_subform.Form!attendance = 0 Then
oMail.Recipients.Add MailList("E-Mail Address")
End If
'OnError GoToNext
'And on to the next one...
MailList.MoveNext
Loop
'And now that we've addressed it, we can finish composing the rest of the fields.
'This gives it a subject, sent on behalf of
oMail.Subject = "Canceled Training: " & Me.training_name_text
'oMail.SentOnBehalfOfName = "on behalf from"
'This gives it the body
oMail.HTMLBody = "<HTML><HEAD><Font Size= 5><style> table, th, td </style> </HEAD> <BODY><br><p>" & _
"*** This is an automatically generated email, please do not reply ***<br><br>" & _
"Please be aware that the below training has been canceled:<br><br>" & _
"<TABLE><TR><TD>Training Name:</TD><TD>" & Me.training_name_text & "</TD></TR><TR>" & _
"<TD>Date:</TD><TD>" & Me.training_date_start & " - " & Me.training_end_date & "</TD></TR><TR>" & _
"<TD>Training Location:</TD><TD>" & Me.training_location_text & "</TD></TR><TR>" & _
"<TD>Start Time:</TD><TD>" & Me.training_time_start & "</TD></TR><TR>" & _
"<TD>End Time:</TD><TD>" & Me.training_end_time & "</TD></TR><TR>" & _
"<TD>Trainer:</TD><TD>" & Me.trainer_1_name_text & "</TD></TR><TR></TD></TABLE> </BODY> </HTML>"
'This displays it!
oMail.Display
Loop
'Cleanup after ourselves
Set oApp = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End SubGreetings.
