If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Column values to single string
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 |
#2
|
|||
|
|||
Column values to single string
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/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "John" wrote: 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 |
#3
|
|||
|
|||
Column values to single string
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" wrote: 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/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "John" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|