tblCLIENTS: main table
- CommandEmail: command button that sits on main table
- Concatenate: function
- modConcatenate: blank module
tblCONTACTS: table containing all contact info fields
- EmailAddress: text field containing email address
- GroupEmail: yes/no field used to select specific email address
Private Sub CommandEmail_Click()
Dim strHighAddress As String
strHighAddress = Concatenate("SELECT EmailAddress FROM tblCONTACTS WHERE GroupEmail = True", ";")
Application.FollowHyperlink "mailto:" & "" & strHighAddress & ""
End Sub
Option Compare Database
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant
' Created by Duane Hookom, 2003
' this code may be included in any application/mdb providing
' this statement is left intact
' example
' tblFamily with FamID as numeric primary key
' tblFamMem with FamID, FirstName, DOB,...
' return a comma separated list of FirstNames
' for a FamID
' John, Mary, Susan
' ======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
' FROM tblFamily
' ============================================
' to get a return like Duane, Laura, Jake, and Chelsey
' ======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID], ",",", and ") as FirstNames
' FROM tblFamily
' ============================================
' If FamID is a string rather than numeric,
' it will need to be delimited with quotes
' ======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =""" & [FamID] & """", ",",", and ") as FirstNames
' FROM tblFamily
' ============================================
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)
'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'length before last concatenation
Dim intLenB4Last As Integer
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
intLenB4Last = Len(strConcat)
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
If Len(pstrLastDelim) > 0 Then
strConcat = Left(strConcat, _
intLenB4Last - Len(pstrDelim) - 1) _
& pstrLastDelim & Mid(strConcat, intLenB4Last + 1)
End If
End If
If Len(strConcat) > 0 Then
Concatenate = strConcat
Else
Concatenate = Null
End If
End Function
