Column values to single string "John" <John[ at ]nospam.infovis.co.uk> 01.10.2006 01:28:48 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

RE: Column values to single string Tom Wickerath 01.10.2006 01:55:01 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" wrote: [Quoted Text] > 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

RE: Column values to single string Numfric 01.10.2006 02:35:01 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: [Quoted Text] > 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" 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