Using EMAIL option

[Steven.Allman]

So I have been using Access to create emails for some time.
I have two or three contacts for each "State", but I am wanting to now create REGIONS and email those regions.
So currently my SendNoObject works perfect, with a good script for looking at EMAIL_1 and if not blank ";" & EMAIL_2, etc works great..
but... how can I code something to look at
contacts!Email_1 for all records where ME!Region = Contacts!region
I don't even know if I am headed the right direction with this code.....
but it would, in theory, compose my email to everyone in Indiana, Illinois, Michigan, Iowa, Wisconsin, Ohio......

The following is my current code for emailing a single state.
_________________________________
Private Sub Command38_Click()
Dim rstBook1 As DAO.Recordset
Dim strTo As String
Set rstBook1 = CurrentDb().OpenRecordset("SELECT * FROM book1 WHERE [BAC]='" & Me!BAC & "'", dbOpenDynaset)
strTo = ""
If Not IsNull(rstBook1("ncoEmail1")) Then
strTo = strTo + rstBook1("NCOEmail1")


End If
If Not IsNull(rstBook1("NCOEmail2")) Then
strTo = strTo & ";" & rstBook1("NCOEmail2")
End If

If Not IsNull(rstBook1("NCOemail3")) Then
strTo = strTo & ";" & rstBook1("NCOemail3")
End If
DoCmd.SendObject acSendNoObject, , , strTo, STRCC, , "Message from SAP", "Good Day...", , 0
End Sub

[pbaldy]

Sounds like you can open a recordset using the region criteria. Presuming that can return multiple records, you'd loop the recordset and build a string of your recipients. Here's my loop template code:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'build your string here
    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing