Concatenate elements of Combo Box to one string

[Guest]

The Combo Box has (3) columns - Column 3 is what I want here. Instead of
adding the value for each row in the string, it always puts the value of the
*first* row.

Private Sub cmdEmailAll_Click()
Dim itm As Variant
Dim strEmailList As String

' Concatenate elements of Combo Box to one string
For Each itm In Me.cboEmpSelect.Properties
If Nz(Me!cboEmpSelect.Column(2, itm), "") <> "" Then
strEmailList = strEmailList + Me.cboEmpSelect.Column(2, itm) + ";"
End If
Next itm
strEmailList = Left(strEmailList, Len(strEmailList) - 1) ' remove
ending ;
Debug.Print strEmailList
End Sub

 

[Arvin Meyer [MVP]]

If this is a continuous form, you will need to concatenate the combo in the
underlying query or select statement.

 

[Guest]

Not a continuous form. The Combo Box (cboEmpSelect) already exists with
filled data on the form. I am attempting to build a string containing the
email addresses (column 3) which will be passed to SendObject as the To
string. The actual result is it repeats the email address of the current row
displayed in the Combo Box.

 

[Arvin Meyer [MVP]]

Not a continuous form. The Combo Box (cboEmpSelect) already exists with
filled data on the form. I am attempting to build a string containing the
email addresses (column 3) which will be passed to SendObject as the To
string. The actual result is it repeats the email address of the current
row
displayed in the Combo Box.

You can use SendObject to concatenate a list of email addresses, but not the
from a combo, the way you are trying to do it. You either need a
multi-select list box, or you need to build a recordset of those items that
you wish to send. An example of a multi-select is on my website at:

http://www.datastrat.com/Download/EmailSenate2K.zip

And the recordset method is used at:

http://www.datastrat.com/Code/MultipleEmail.txt

 

[Guest]

I decided on the RecordSet method using your MultiEmail example. Created a
query pulling data based on criteria, and it works like a charm. Calling the
query is definitely the easiest method. Thank you for the code and your time!