title: Populating a listbox in Outlook from an Excel Range - getting a cell's hyperlink
body: <p>I'm trying to populate a multicolumn listbox in Outlook VBA with data from an Excel range.</p>
  
  <p>I've managed to get it working so far using the code:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  'Late binding.  No reference to Excel Object required.
  Dim xlApp As Object
  Dim xlWB As Object
  Dim xlWS As Object
  Dim cRows As Long
  Dim I As Long
    Set xlApp = CreateObject("Excel.Application")
    'Open the spreadsheet to get data
    Set xlWB = xlApp.Workbooks.Open("Query Log.xlsx")
    Set xlWS = xlWB.Worksheets(1)
    cRows = xlWS.Range("Guides").Rows.Count - xlWS.Range("Guides").Row + 1
    ListBox1.ColumnCount = 2
    'Populate the listbox.
    With Me.ListBox1
      For I = 2 To cRows
         'Use .AddItem property to add a new row for each record and populate column 0
        .AddItem xlWS.Range("Guides").Cells(I, 1)
        'Use .List method to populate the remaining columns
        .List(.ListCount - 1, 1) = xlWS.Range("Guides").Cells(I, 2)
      Next I
    End With
    'Clean up
    Set xlWS = Nothing
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
  lbl_Exit:
    Exit Sub
  End Sub
  </code></pre>
  
  <p>with the Excel range being 2 columns - first column being a title and the second column being a hyperlinked cell to a Word document.</p>
  
  <p>With the code above I can get the listbox populated fine, but what I want to do is when one of the rows has been selected I want to be able to find out the hyperlink that is in the corresponding cell.</p>
  
  <p>eg, the range looks like:</p>
  
  <pre><code>Guide 1  |  Link to guide (&lt;--- hyperlinked to "guide1.doc")
  Guide 2  |  Link to guide (&lt;--- hyperlinked to "guide2.doc")
  Guide 3  |  Link to guide (&lt;--- hyperlinked to "guide3.doc")
  Guide 4  |  Link to guide (&lt;--- hyperlinked to "guide4.doc")
  </code></pre>
  
  <p>Using the code I get back the hyperlink text (eg, "Link to guide") but I need what the hyperlink location is (eg, "guide1.doc").</p>
  
  <p>Is there any way to load the hyperlink location into the listbox without having to rewrite the Excel file? (it's maintained by someone else so it's possible, but would take them a very long time to do so).</p>
  
  <p>I hope I'm clear in what I'm trying to do!</p>
  
  <p>Does anyone have any ideas?</p>
  
  <p>Thanks</p>