Generate list of emails separated by commas

[Alkaline]

Hi, I want to get all the email addresses from a query, and put them into a textbox in a form, where I can copy and paste them into an email. I don't really know how to do it. First of all, not all the records in the query have emails.

Any ideas appreciated.

 

[DCrake]

Simple Software Solutions

Hi

similar issue with one recently resolved, here is the code

Create a recordset of the intended recipients

Dim Rs As DAO.Recordset
Dim strItems As String

Set Rs = CurrentDb.OpenRecordset(MyRecords)

Do until Rs.EOF

strItems = strItems & Trim(Rs("EmailAddress") & " ") & ";"

Rs.MoveNext

Loop

'Then strip off the last semi-colon
strIems = left(strItems,Len(strItems)-1)


For x = 1 To Len(strItems)

If Instr(strItems,";;") > 0 Then

strItems = Replace(strItems,";;",";")​

Else

Exit For​

End If​

Next

Summary:
Generate a recordset of all the recipients (MyRecords)


Loop through each record and string together the email address of the record to the previous ones. I have added the Trim(... & " ") to contend with Null entries.

Next check for any blank email addresses using the For ... Next ... Loop

The Replace() replaces all occurances of ;; with ; this removes all empty email addresses from the string

You should now have one long string of email addresses all separated with a semi-colon which you can populate to your text box on the form.

Code Master: