none
Populate UserForm Listbox with OUTLOOK Contact Data RRS feed

  • Question

  • I would like to populate a Microsoft Word Template Add-In UserForm with all OUTLOOK Contact entries. I've managed to do this in a crude sort of way by opening and instance of OUTLOOK.

    I was wondering if it is possible to do using ADODB.

    I have done this sort of thing using Excel and Access, but simply don't know enough about OUTLOOK to even get off to a good start. I found this bit of code online so I'm thinking it is possible:

    Dim RstConnect As ADODB.Connection
    Dim RSTADO As ADODB.Recordset
    Dim strConn As String
    Dim o
      Set RstConnect = New ADODB.Connection
      Set RSTADO = New ADODB.Recordset
      With RstConnect
        'Change the Connection String below to the correct settings
        .ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Exchange 4.0;MAPILEVEL=Outlook Address Book\;PROFILE=Outlook;TABLETYPE=1;DATABASE=c:\temp"
        .Open
      End With
      With RSTADO
        Set .ActiveConnection = RstConnect
          .CursorType = adOpenStatic
          .LockType = adLockReadOnly
          .Open "Select * from [Contacts]"
          .MoveFirst
          Do
            Dim i As Long
            For i = 0 To RSTADO.RecordCount - 1
             If RSTADO(i).Name = "E-mail address" Then List1.AddItem Format(RSTADO(i).Value)
            Next i
            .MoveNext
            Loop Until .EOF
            .Close
        End With
    
        Set RSTADO = Nothing
    
        RstConnect.Close
        Set RstConnect = Nothing

    I don't really need any conditionals.  I'm just trying to populate a listbox with all contacts and all fields for each contact. 

    Thanks.


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Friday, April 17, 2015 1:32 PM

Answers

  • Hi Greg,

    >> 1. How could I determine a user's OUTLOOK profile and pst folder location at runtime?

    You could call InputBox function to display a prompt and waits for user to input text.

    >> 2.  If the folder name was say "Personal Contacts" how would I need to modify the SQL statement?

    You could let user to specify the folder. (Similar to specify the path)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Greg Maxey Monday, April 20, 2015 11:43 AM
    Monday, April 20, 2015 7:04 AM
    Moderator
  • Hi Greg,

    Glad to hear that your code is working correctly. Thank you for sharing for other forum readers. For example, see How to find full path of Outlook .pst file? .

    The most reliable way to get the answers is to run outlook programmatically and get all the required information.

    1. How could I determine a user's OUTLOOK profile and pst folder location at runtime?

    You can find profile names in the window registry.

    Old versions of Outlook stored profiles in a version-independent manner under

    HKCU\Software\Microsoft\Windows NT\Windows Messaging Subsystem\Profiles

    But Outlook 2013 stores profiles in a versioned hive under the following key:

    HKEY_CURRENT_USER\Software\Microsoft\Office\<version>\Outlook\Profiles

    where <version> is a string representing the xx.0 major version, such as 15.0 for Outlook 2013.

    Also you may try to explore windows registry keys for .pst file location.

    2.  If the folder name was say "Personal Contacts" how would I need to modify the SQL statement?


    Try to specify the folder path (see the corresponding property value in the OOM).

    • Marked as answer by Greg Maxey Monday, April 20, 2015 11:43 AM
    Monday, April 20, 2015 10:52 AM

All replies

  • Hello Greg,

    You may find the following resources helpful:

    Connecting Outlook to Databases

    How to retrieve Exchange and Outlook data with the Jet 4.0 OLE DB provider in Access 2000

    Also consider automating Outlook from another applications, see How to automate Outlook from another program .

    Friday, April 17, 2015 1:50 PM
  • Eugene,

    I looked at two of those links before posting.  I simply don't know enough about OUTLOOK to see a solution in either.  Here is some code that I use to populate a UserForm Listbox with data in an Excel Worksheet.

    Public Function xlFillList(oListOrComboBox As Object, strWorkbook As String, _
                               strRange As String, bisRangeASheet As Boolean, _
                               bSuppressHeader As Boolean, bSingleColumn As Boolean)
    'Fills the passed list or combobox with data from an Excel worksheet or a named range in a worksheet.
    ' - oListOrComboBox is the name of the list or combo box'
    ' - strWorkbook is the name of the Excel data file
    ' - strRange is the part of the data file to be used (sheet or named range).
    ' - bisRangeASheet sets the range 'strRange' a sheet (true) or a named range (false)
    
      
      If bisRangeASheet Then
        'strRange is a named worksheet use:
        strRange = strRange & "$]"
      Else
        'strRange is a named range use:
        strRange = strRange & "]"
      End If
      Set m_oConn = CreateObject("ADODB.Connection")
      If bSuppressHeader Then
        m_strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & strWorkbook & ";" & _
                        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
      Else
        m_strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & strWorkbook & ";" & _
                        "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
      End If
      m_oConn.Open ConnectionString:=m_strConnection
      'Alternative connection 2
      '*********************************
      'm_oConn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
      'm_oConn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strWorkbook
      'm_oConn.Open
      '*********************************
      Set m_oRecordSet = CreateObject("ADODB.Recordset")
      'Read the data from the worksheet.
      m_oRecordSet.Open "SELECT * FROM [" & strRange, m_oConn, 2, 1
      With m_oRecordSet
        'Find the last record.
        .MoveLast
        'Get count.
        m_lngNumRecs = .RecordCount
        'Return to the start.
        .MoveFirst
      End With
      With oListOrComboBox
        'Load the records into the columns of the named list/combo box.
        .ColumnCount = m_oRecordSet.Fields.Count
        .Column = m_oRecordSet.GetRows(m_lngNumRecs)
        If bSingleColumn Then
          'Set the widths of the combo/list box columns to display only the first column.
          m_strWidth = .Width - 2 & " pt;"
          For m_lngIndex = 2 To .ColumnCount
            m_strWidth = m_strWidth & "0 pt"
            If m_lngIndex < .ColumnCount Then
               m_strWidth = m_strWidth & ";"
             End If
          Next m_lngIndex
          .ColumnWidths = m_strWidth
        End If
      End With
      'Cleanup
      If m_oRecordSet.State = 1 Then m_oRecordSet.Close
      Set m_oRecordSet = Nothing
      If m_oConn.State = 1 Then m_oConn.Close
      Set m_oConn = Nothing
    lbl_Exit:
      Exit Function
    End Function
    
    
    

    With this code of course I know in advance the name of the Excel file.  For my current requirement, I need to fill the list box with the User's Outlook Contacts.  According the connection string has to be dynamic.  Is this possible?  Thanks.


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Friday, April 17, 2015 6:25 PM
  • You can built the connection string at runtime. But I don't see any line of code which is related to Outlook. 
    Friday, April 17, 2015 6:40 PM
  • Eugene,

    I assume that you can because you know how.  I don't.  That is why I am here asking for help.

    My first post has a line of code related to the OUTLOOK.  The second post doesn't because as I explained, I can load a listbox with Excel data using ADODB and that code and I would like to do something similar using the User's OUTLOOK Contacts folder as the data source. 

    I assume the part that needs to change in that code is m_strConnection is defined:
    m_strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                       
    "Data Source=" & strWorkbook & ";" & _
                       
    "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    and the:

    SELECT FROM line.

    Can you help?


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Friday, April 17, 2015 11:17 PM
  • Ok, I've managed to cobble something together that basically works:

    Sub LoadOutlookContacts(oListBox As Object, strProfile As String, strPSTFolder As String, strDataBase As String)
    'strProfile - Outlook profile name
    'strPSTFolder - Outlook PST folder name
    'strDatabaseTemp - database temporary folder
    Dim strMAPILevel As String
    Dim strTableType As String
    Dim oConn As New ADODB.Connection
    Dim oRS As New ADODB.Recordset
    Dim lngRecordCount As Long
     
      strMAPILevel = "MAPILEVEL=" & strPSTFolder & "|;" 'Ends with a PIPE char
      strProfile = "PROFILE=" & strProfile & ";"
      strTableType = "TABLETYPE=0;"
      strDataBase = "DATABASE=" & strDataBase & ";"
      With oConn
        .ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Exchange 4.0;" & strMAPILevel & strProfile & strTableType & strDataBase
        .Open
      End With
      With oRS
        .Open "Select * from Contacts", oConn, adOpenStatic, adLockReadOnly
        .MoveLast
        'Get Count
        lngRecordCount = .RecordCount
        .MoveFirst
      End With
      With oListBox
        'Load the records into the columns of the named list/combo box.
        .ColumnCount = oRS.Fields.Count
        .Column = oRS.GetRows(lngRecordCount)
      End With
      'Cleanup
      If oRS.State = 1 Then oRS.Close
      If oConn.State = 1 Then oConn.Close
      Set oRS = Nothing
      Set oConn = Nothing
    lbl_Exit:
      Exit Sub
    End Sub

    I call it form the userform initialize event:

    Private Sub UserForm_Initialize()
      Module2.LoadOutlookContacts ListBox1, "Outlook", "Gregory K. Maxey", "C:\Windows\Temp"
    End Sub
    

    Where Outlook is my profile name and Gregory K. Maxey is my .pst folder name.

    This is working for my "Contacts" folder and it works for a folder name "Test"

    Questions:
    1. How could I determine a user's OUTLOOK profile and pst folder location at runtime?
    2.  If the folder name was say "Personal Contacts" how would I need to modify the SQL statement?

    Thanks.


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Saturday, April 18, 2015 12:55 AM
  • Hi Greg,

    >> 1. How could I determine a user's OUTLOOK profile and pst folder location at runtime?

    You could call InputBox function to display a prompt and waits for user to input text.

    >> 2.  If the folder name was say "Personal Contacts" how would I need to modify the SQL statement?

    You could let user to specify the folder. (Similar to specify the path)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Greg Maxey Monday, April 20, 2015 11:43 AM
    Monday, April 20, 2015 7:04 AM
    Moderator
  • Hi Greg,

    Glad to hear that your code is working correctly. Thank you for sharing for other forum readers. For example, see How to find full path of Outlook .pst file? .

    The most reliable way to get the answers is to run outlook programmatically and get all the required information.

    1. How could I determine a user's OUTLOOK profile and pst folder location at runtime?

    You can find profile names in the window registry.

    Old versions of Outlook stored profiles in a version-independent manner under

    HKCU\Software\Microsoft\Windows NT\Windows Messaging Subsystem\Profiles

    But Outlook 2013 stores profiles in a versioned hive under the following key:

    HKEY_CURRENT_USER\Software\Microsoft\Office\<version>\Outlook\Profiles

    where <version> is a string representing the xx.0 major version, such as 15.0 for Outlook 2013.

    Also you may try to explore windows registry keys for .pst file location.

    2.  If the folder name was say "Personal Contacts" how would I need to modify the SQL statement?


    Try to specify the folder path (see the corresponding property value in the OOM).

    • Marked as answer by Greg Maxey Monday, April 20, 2015 11:43 AM
    Monday, April 20, 2015 10:52 AM
  • Starain, Eugene,

    Thanks.  Unfortunately since data defined in a UserProperty field is apparently not accessible using this manner (https://social.msdn.microsoft.com/Forums/office/en-US/b2bdf0a1-bf89-4401-b1fc-84df66b4cf76/contact-data-retrieved-using-an-adodb-connection-missing-userproperty-data?forum=outlookdev) the whole exercise has been a trip down an empty rabbit hole :-(


    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm

    Monday, April 20, 2015 11:47 AM