Retrieving and Using Data in Listboxes

Topic

New Reply

Hi Forum members.

I am building a “report manager”, a form that will allow users to select any report, and e-mail it to any number of people.

I’ve built the listbox that displays all of the reports, and i’ve also used to listboxes for the people to e-mail to.

Listbox 1 displays a list of people in a table, which also contains their e-mail address, phone number, company etc.

Double clicking on any name will copy that name across to listbox 2, which has two columns; one to show the name, and another to hold the respective e-mail address. As many names as necessary can be added to listbox 2.

I’m using outlook.application to create the e-mail, and that is all working. I can also pass the report name to attach, as well as the subject header and body details. No problems there at all.

Now for my problem. I can’t work out how to take the e-mail address/addresses from Me![Selected].Column(1) and pass them to the .To of the e-mail.

I’ve tried using a recordset and a rowsource, but to no avail.

Any ideas would be very welcome.

Regards

Peter Gidden
Access97 SR2

Reply | Quote

Replies

Hi Peter,
I’d use a For…Next loop to do this. You’ll need to use the .Column(1,rowindex) property to choose the value in column2 for each row.
Hope that helps.
PS I’m assuming from what you’ve posted that Selected is the name of your listbox? If so, I personally wouldn’t use a name like that as it’s also the name of a property of the listbox.

Reply | Quote

Thanks for your comments Rory. Firstly, i’ve taken your advice and changed the listbox name.

Secondly, i’m OKish on For-Next loops, but how do i refer to the data in column 1 of my listbox. All i seem to be able to do is retrieve the first value?

Sorry if i’m appearing a bit dense today……

Reply | Quote

Peter,
Something along the lines of:
Dim lngRownumber as long
for lngrownumber = 0 to Me!listboxname.listcount – 1
‘your code to create the email goes here
objOLMail.to = me!listboxname.column(1,lngRownumber)
‘send it
next ‘lngRownumber
Hard for me to be more specific without knowing your code, but does that give you the general idea?

Reply | Quote

Rory,

Thanks for your help, i’ve now got it going

Now, one problem or if you like, enhancement. If a user picks 5 people to send a report to, this will send 5 separate e-mails. What i really, really want is to send one e-mail to 5 people, which means that the .To value needs to consist of all the values in lstSelected…….

Reply | Quote

Peter,
In that case, I’d use a string variable (e.g. strAddressees) and before the Next ‘lngRownumber line, put
strAddressees = strAddressees & “; ” & me!lstSelected.column(1,lngRownumber)
then after the For..Next have objOLMail.To = strAddressees
Hope that helps.

Reply | Quote

Rory

Thanks agian, i’ve now got it working exactly as i want it. My probem was that i was not sure how to get Access to a)retrieve and b)sequence through the listbox values.

Reply | Quote