Sending Email to Multiple Addresses Located in a Database

[esilva002]

Background Info - Hi everyone. I am trying to send an email to multiple addresses on a button click. I store my addresses in a SQL Server database view vrptJudicalCalendarAtt and have a private sub that would take the addresses and put them into a string that has " ; ". Then use the SendObject method to send my email with attachment and get the addresses from the private sub.

Here is my code for the private sub:

Private Function EmailList() As String
'-- Return all of the email addresses in the EmailAddress table
'-- as one string separated with a semicolon ";"

On Error GoTo Err_EmailList

Dim DB As Database
Dim rs As DAO.Recordset

Set DB = CurrentDb
Set rs = DB.OpenRecordset("Select Email From vrptJudicalCalendarAtt Where CaseNumber = '" & fld1 & "'", dbOpenForwardOnly)

With rs
  Do While Not rs.EOF
      EmailList = EmailList & ![Email] & ";"
      rs.MoveNext
   Loop
End With

'-- Strip off the last ";"
EmailList = Left(EmailList, Len(EmailList) - 1)

Exit_EmailList:
If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
End If
Exit Function
    
Err_EmailList:
   MsgBox "Error No:    " & err.Number & vbCr & _
   "Description: " & err.Description
   Resume Exit_EmailList

End Function

Here is the code for the btn Click:
Private Sub btnEmailCase_Click()

On Error GoTo MailError

DoCmd.SendObject acSendReport, "rptJudicialcalendarValidation", acFormatRTF, EmailList(), , , "Sandusky County Hearing Notice", "Hearing Notice for " & Caption1 & " Case Number: " & CaseNumber & " at " & Right([BeginTime], 11) & " on " & BeginDate & ""

MailError:
    MsgBox "Email Your Was Not Sent"

End Sub