Working With Outlook from Access

The function, DistListPeek(), peeks into all the distribution lists and allows you to extract the names and email addresses. As you might expect, the Outlook Security model doesn’t like it when code does this either, so again your users will get a security dialog asking for permission to proceed. It seems like the Redemption library should allow a way to circumvent this, but my initial attempts proved unsuccessful.

Again, I have a local table where the list items are saved for display on the demo form. The code simply instantiates an Address List object and interrogates it for lists and items, inserting each one into the temp table as it goes. I found that some text parsing and cleanup had to be done to get a good list and once again, this extent cleanup may depend on the quality of your address lists.

Function DistListPeek()
On Error Resume Next

  Dim oOut   As Object    ' Outlook.Application
  Dim oNS    As Object    ' Outlook.NameSpace
  Dim oAL    As Object    ' Outlook.AddressList
  Dim oDL    As Object    ' Outlook.AddressEntry
  Dim sSQL   As String
  Dim iPos   As Integer
  Dim sList  As String
  Dim sName  As String
  Dim sType  As String
  Dim sEmail As String

  Set oOut = GetOutlookObject()
  Set oNS  = oOut.GetNamespace("MAPI")
  oNS.Logon , , False, True

  'Return the personal address book.
  Set oNS = oOut.GetNamespace("MAPI")  
  
  sSQL = "DELETE FROM tblContacts"
  CurrentDb.Execute sSQL

  For Each oAL In oNS.AddressLists
    iPos = 0
    sList = oAL.Name
    
    For Each oDL In oAL.AddressEntries
      iPos   = iPos + 1
      sEmail = oDL.Address
      sName  = oDL.Name
      sType  = oDL.Type
      
      ' The Name property sometimes includes the Email address, 
      ' so strip it out.  Other times, it IS the email address.
      sName = Trim(Replace(sName, "(" & sEmail & ")", ""))
      If sName = "" Then sName = sEmail
      
      sList = Replace(sList, "'", "''")
      sType = Replace(sType, "'", "''")
      sName = Replace(sName, "'", "''")

      sSQL = "INSERT INTO tblContacts " & _
             "(ListName, ListType, Position, Name, Email) " & _
             "VALUES ('" & sList & "','" & _
             sType & "'," & iPos & ",'" & _
             sName& "','" & sEmail & "')"

      CurrentDb.Execute sSQL
    Next
  Next
  
End Function

From here …

The best way to understand this process is to run the code in break mode, stepping through it one line at a time. This will allow you to see how the text strings are parsed and to identify any issues that might arise which are peculiar to your mail setup.

Personally, I’m very excited about this demo code because it brings together in one place all the snippets I’ve used in various applications. It’s the kind of thing you could import into an application and start using right away and because it relies on late binding, you needn’t worry about the version of Microsoft Outlook that the user has installed. The code supplied with this article should be an adequate starting point for all your Outlook to Access automation projects.

» See All Articles by Columnist Danny J. Lesandrini