Concatenate all email addresses from table (1 Viewer)

[mbamber]

Hi all,

I have a table, "TBL_Email", which simply contains a list of email addresses in a field called "email".

I would like to concatenate all of them together into one string, and add semi-colons to the end e.g.

"123@abc.com;456@def.com;789@hij.com"

I believe a may need a record set, but i'm not entirely sure how they work.

TIA

 

[Cronk]

A recordset is created to hold data from user selected fields from user selected records in one or more tables.

The steps are to create a query to extract the email addresses that you want. Then using VBA, open a recordset based on this query. You can then use a loop to move from the start to end of the recordset, appending the email address and semi colon to a string variable. Then drop the last semi colon.

Use on-line help to seek examples and come back here if you need more assistance.

 

[missinglinq]

Something like this, where the concatenated string is named varEmailAdd:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varEmailAdd As String

Set db = CurrentDb
Set rs = db.OpenRecordset("TBL_Email")

Do While Not rs.EOF

  varEmailAdd = varEmailAdd & rs!Email & ";"

  rs.MoveNext

Loop

rs.Close

Set rs = Nothing

varEmailAdd = Left(varEmailAdd, Len(varEmailAdd) - 1)

Linq ;0)>

 

[mbamber]

Brilliant! Thank you both!

 

[missinglinq]

Glad we could help!

Linq ;0)>