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.