Sending Email to Multiple Addresses Located in a Database

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:

Code:

---

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

---

The problem is that I cannot get the email list to show up correctly can i get a little help?

Thanks.

Welcome to the Forums.

Can you explain more of what you mean by "show up correctly"? Whats the correct way it should be showing vs how its actually showing.

I got it up and running thanks.