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"
Do Until rsEmail.EOF
If IsNull(![Email]) = False Then
SendBcc = SendBcc & rsEmail![Email] & ";"
MessageSubject = ""
stDocName = "FORMTOOPEN"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Set MyDB = Nothing
Set rsEmail = Nothing
SendBcc = ""
If Err.Number = 2501 Then
MsgBox "The e-mail was cancelled without sending", , "APPLICATIONNAME"
If Err.Number = 3734 Then
MsgBox "There are no Records Cancelling", , "APPLICATIONNAME"
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