Access VBA Add Multiple email address to Outlook To Line

Question

Good Evening,

I have a continuous form in an Access Database that lists each employee, and their email address, that is assigned to a job.  I need to take each email address from the form and insert them into the To Line in an Outlook email message.

Can someone point me in the right direction to find a function or subroutine that does this?  Or explain to me how I can accomplish this, please?

Thank You in Advance...

Answer 1

It all depends on your exact needs, but the basic principle is

Sub GenEmail()
    Dim frm                   As Form_YourFormName
    Dim rs                    As DAO.Recordset
    Dim sTo                   As String

    On Error GoTo Error_Handler

    Set frm = Forms("YourFormName").Form
    Set rs = frm.RecordsetClone
    With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                sTo = sTo & ![EmailFieldName] & ";"
                .MoveNext
            Loop
        End If
    End With
    
    DoCmd.SendObject acSendNoObject, , , sTo, , , , , True

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then Set rs = Nothing
    If Not frm Is Nothing Then Set frm = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GenEmail" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

---

Daniel Pineault, 2010-2018 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Thank You Mr. Pineault for taking the time to respond.  I will try to make this work this morning.