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.

CODE
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
/CODE

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.

CODE
Private Sub Form_Load()

Me.Bcc = SendBcc

End Sub
/CODE

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, Database Design, MS Access, VBA Code MS Access. Bookmark the permalink.