Pass selected items in list box as a string

[Kurt]

When a user clicks on one or more people in a multi-select list box, the
email addressees of these people are passed to an unbound text box,
txtSelected, as a string.

Then the user clicks on a command button called “Send Email†which opens up
Outlook and populates the To: box with the string of selected email addresses.

This works great, but …

I’ve since added a “Select All†command button on the form so the user can
quickly select all of the names in the listbox. The code selects all of the
names, but I don’t know how to edit it so all of the selected emails are
passed as a string to txtSelected (as would happen when the user manually
clicks on names in the ListBox).

Any ideas how I can edit the code for the “Select All†button to pass the
string of email addresses to txtSelected?

Thanks. Code below.

Kurt

“Select All†Command Button
-------------------------------------
Private Sub cmdAll_Click()
Dim lngX As Long
With Me.lstMailTo
For lngX = Abs(.ColumnHeads) To (.ListCount - 1)
.Selected(lngX) = True
Next
End With
End Sub


“Send Email†Command Button
---------------------------------------
Private Sub cmdEmail_Click()
Dim strEmail As String
strEmail = Me.txtSelected & vbNullString
DoCmd.SendObject To:=strEmail
End Sub


OnClick Code for List Box
---------------------------------
Private Sub lstMailTo_Click()
Dim varItem As Variant
Dim strList As String

With Me!lstMailTo
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ";"
Next varItem
strList = Left$(strList, Len(strList) - 1)
Me!txtSelected = strList
End If
End With
End Sub

 

[Douglas J. Steele]

Private Sub cmdAll_Click()
Dim strList As String

Dim lngX As Long
With Me.lstMailTo
For lngX = Abs(.ColumnHeads) To (.ListCount - 1)
.Selected(lngX) = True
strList = strList & .Column(0, varItem) & ";"
Next
End With
If Len(strList) > 0 Then
strList = Left$(strList, Len(strList) - 1)
End If
Me!txtSelected = strList
End Sub

 

[Kurt]

Everything is selected but only the first item in the listbox is passed to
txtSelected, but it's repeated as many times as there are items in the list
box. (e.g., (e-mail address removed);[email protected];[email protected], etc.)

However, when I deselect any item, the txtSelected gets updated with the
correct string of emails, and then I can reselect the item I deselected to
add it back in.

I rarely use list boxes so I'm not sure which part of the code needs fixing.
Thoughts? (Also, I added "Dim varItem As Variant" to your code.)

Thank you. - Kurt

 

[Douglas J. Steele]

Sorry: my fault. I was copying-and-pasting your code, and forgot a critical
change.

should be

(and then you won't need the Dim varItem As Variant)