Howdy!  

It seems this question has been posed all over the interwebs, but my problem has me stuck.  I'd appreciate any help!

I need to be able to run a query based on the selections from a MULTI-Select listbox.  Documentation is very clear that you cannot simply reference the listbox in your query, as the multiselect option will simply return a null value.

The solutions I have found seem to be to create a string based on the list items with a code like this:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
    Set frm = Form!frmMyForm
    Set ctl = frm!lbMultiSelectListbox
    strSQL = "Select * from Employees where [EmpID]="
    'Assuming long [EmpID] is the bound field in lb
    'enumerate selected items and
    'concatenate to strSQL
    For Each varItem In ctl.ItemsSelected
        strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
    Next varItem

    'Trim the end of strSQL
    strSQL=left$(strSQL,len(strSQL)-12))


However, what do I do if my listbox information is populated by an additional query in the form (based on the year), and is therefore variable?  

Here's some background info:  The database I operate tracks the fundraising for hundreds of team members across dozens of teams.  Each year, new teams and teammembers are formed.

I already have set up a form that allows me to email individual team members their balance report (Thanks to Richard and his Email via SMTP tutorial), but now I need to set up bulk mailing.

The workflow looks like this:  I want to be able to select multiple teams from the multi-select listbox, hit "send", and have the database query that pulls all of the team members based on the teams selected, generates individual reports for those individual team members, then works through the email form one record at a time.

My biggest hurdle is the query based on multiple selections from a listbox.  The second hurdle will be setting up the email loop, but I'm confident that Richard's course on that will suffice.

Thanks to anyone who can point me in the right direction!

Seth
[email protected]

---

Reply from Alex Hedley:

I'd say the way the listbox is filled is irrelevant.
Once you have the data in it you can use it how you want.

If you have a single email working just make that a function with a parameter, To address (and any others that are going to change per person).
Now you can add a loop

For Each varItem In ctl.ItemsSelected
  SENDEMAIL(varItem)
Next varItem

varItem being the email address.

If you have a single report working it's just a case of wrapping that whole procedure and looping that.