VBA access code for pulling together a list of e-mails and passing to a form

Below some standard code that I use to loop through a selection of records and create a string from the individual [Email] s in the below case the e-mails are passed to a form (FORMTOOPEN) – if you are using this then all text in capitals will need to be replaced by application specific information. You want to set up a global variable that is made public which you can pass the combined rsemail (in this case SendBCC) string to between opening up forms.

I tend to have a module called modGlobalVariables and I would put this in

Public SendBcc as string

Remember to be careful with your variable definitions – If you have the same variable dimensioned locally within a command and publically over the whole project values may not appear as expected when you get them.

Private Sub CommandGroupEmail_Click()
On Error GoTo Err_CommandGroupEmail_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    MsgBox "Please note all available e-mails are placed in BCC section of a new form in alphabetical person name order. If a person doesn't have a listed e-mail address he/she will be omitted", , "APPLICATIONNAME"

    Dim MyDB As DAO.Database
    Dim rsEmail as DAO.Database

    Set MyDB = OpenDatabase("\\SERVERNAME\DIRECTORYPATH\" & "TARGET.MDB")
    Set rsEmail = MyDB.OpenRecordset("SELECT STATEMENT HERE"
   
    With rsEmail
    .MoveFirst
    Do Until rsEmail.EOF
    If IsNull(![Email]) = False Then
    SendBcc = SendBcc & rsEmail![Email] & ";"
    MessageSubject = ""
    End If
    .MoveNext
    Loop
    End With
    
    stDocName = "FORMTOOPEN"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Set MyDB = Nothing
    Set rsEmail = Nothing
    SendBcc = ""

     
Exit_CommandGroupEmail_Click:
    Exit Sub

Err_CommandGroupEmail_Click:

    If Err.Number = 2501 Then
    MsgBox "The e-mail was cancelled without sending", , "APPLICATIONNAME"
    Exit Sub
    
    If Err.Number = 3734 Then
    MsgBox "There are no Records Cancelling", , "APPLICATIONNAME"
    Exit Sub
        
    Else
    
    MsgBox Err.Number
     
    End If
    
    End If
    
    Resume Exit_CommandGroupEmail_Click
    
End Sub

Note on loading of the new form you will need to pass SendBCC into whatever text box you wish to see it in where Me.Bcc is the name of the field receiving the SendBCC string.

Private Sub Form_Load()

 Me.Bcc = SendBcc

End Sub