Column values to single string

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have an email column in a table. How can I turn values in the column for
all records into a single string with each email separated by a ;?

Thanks

Regards
 
Hi John,

You can use a function similar to the one shown below. This uses a table
named tblContacts, with fields named EMail (a text data type with the actual
e-mail address) and a Yes/No data type named OnEmailDistribution:


Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = "; "

Set db = CurrentDb
strSQL = "SELECT EMail " _
& "FROM tblContacts " _
& "WHERE EMail Is Not Null AND OnEmailDistribution=Yes;"

Set rs = db.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![BEMSID] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
Else
BulkEmail = ""
End If

ExitProc:
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in
BulkEmail function..."
Resume ExitProc
End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
You can also copy the column from Access, paste it into Word, and do a
table-to-text in Word. Then do a replace all ^p by semicolon.
I have to do this from time to time to notify future guests of changes in
contact information or directions to our vacation rentals.

Tom Wickerath said:
Hi John,

You can use a function similar to the one shown below. This uses a table
named tblContacts, with fields named EMail (a text data type with the actual
e-mail address) and a Yes/No data type named OnEmailDistribution:


Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = "; "

Set db = CurrentDb
strSQL = "SELECT EMail " _
& "FROM tblContacts " _
& "WHERE EMail Is Not Null AND OnEmailDistribution=Yes;"

Set rs = db.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![BEMSID] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
Else
BulkEmail = ""
End If

ExitProc:
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in
BulkEmail function..."
Resume ExitProc
End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

John said:
Hi

I have an email column in a table. How can I turn values in the column for
all records into a single string with each email separated by a ;?

Thanks

Regards
Click to expand...
Click to expand...
 
Back
Top