Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving and Using Data in Listboxes

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Retrieving and Using Data in Listboxes

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving and Using Data in Listboxes

    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...... :-)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Retrieving and Using Data in Listboxes

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving and Using Data in Listboxes

    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.......

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Retrieving and Using Data in Listboxes

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving and Using Data in Listboxes

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •