Email from a Table

[smbarney]

I have a database that is used to log daily events. Sometimes, these events require that a group of people be notified via email. I have a number of checkboxes depending on which group or groups should receive the email. I could hard-code the emails; however, the groups of people change often. I would like to have table that lists the group membership for each person with their email and when the corresponding checkbox is selected on the form they are sent an email. Does anyone know of a way to do this? Thanks.

Here is the code I am using now. I had to mask the emails.

Dim bodytext As String
Dim SubjectText As String
Dim PageAlert As String
Dim EmailAlert As String
Dim SSOName As String
Dim SSOEmail As String
Dim SSOPager As String
Dim CSOName As String
Dim CSOEmail As String
Dim CSOPager As String




PageAlert = "pager alert at " & Time()
EmailAlert = "email alert at " & Time()
SubjectText = "C2 Watch Alert--Priority Level:" & " " & [Priority]

If IsNull([OtherEvent]) Then

bodytext = " FOR OFFICIAL USE ONLY" & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "The following message was generated by the USCIS Command Center in response to an event that requires your attention." & vbCrLf & vbCrLf & "Time: " & [TimeStamp] & vbCrLf & vbCrLf & "Event Type: " & [Event] & vbCrLf & vbCrLf & "Operation: " & [Ops] & vbCrLf & vbCrLf & "Note: " & [Notes]

Else

bodytext = " FOR OFFICIAL USE ONLY" & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "The following message was generated by the USCIS Command Center in response to an event that requires your attention." & vbCrLf & vbCrLf & "Time: " & [TimeStamp] & vbCrLf & vbCrLf & "Event Type: " & [OtherEvent] & vbCrLf & vbCrLf & "Operation: " & [Ops] & vbCrLf & vbCrLf & "Note: " & [Notes]

End If

If [Priority] = "Critical" And [SSOFlag] = True Then
DoCmd.SendObject _
, _
, _
, _
"XXX@XXX", _
, _
, _
"Critical C2 Watch Alert", _
"Please check your email for event details or contact C2.", _
False

Me![Notes] = Me![Notes] & Chr(13) & "The SSO was sent an " & PageAlert

Else
GoTo SendPage_Flag

End If

SendPage_Flag:

If [Priority] = "Critical" And [CSOFlag] = True Then
DoCmd.SendObject _
, _
, _
, _
"XXX@XXX", _
, _
, _
"Critical C2 Watch Alert", _
"Please check your email for event details or contact C2.", _
False
Me![Notes] = Me![Notes] & Chr(13) & "The CSO was sent an " & PageAlert
Else

GoTo SendEmail

End If


SendEmail:
If [SSOFlag] = False Then
GoTo CSOFag_Event
Else

DoCmd.SendObject _
, _
, _
, _
"XXX@XXX", _
, _
, _
SubjectText, _
bodytext, _
True
Me![Notes] = Me![Notes] & Chr(13) & "The SSO was sent an " & EmailAlert

End If

CSOFag_Event:
If [CSOFlag] = False Then
DoCmd.Close

Else

DoCmd.SendObject _
, _
, _
, _
"XXX@XXX ", _
, _
, _
SubjectText, _
bodytext, _
True
Me![Notes] = Me![Notes] & Chr(13) & "The CSO was sent an " & EmailAlert
DoCmd.Close

End If

Exit_btnSubEvent_Click:
Exit Sub

Err_btnSubEvent_Click:
MsgBox Err.Description
Resume Exit_btnSubEvent_Click

 

[freakazeud]

re:

Hi,
create a query which filters out the correct records based on the boolean yes/no field value. Then open a recordset based on this query and loop through the records to construct a long concatenated string full of email address values.
You use that in the 'To' argument of the SendObject() method. A good sample of this can be found here.
HTH
Good luck