Access Outlook Addressbook from within Excel

New Contributor

Is it possible to access the Outlook address book and read out the members of an email group and import it in Excel?

 

Kind regards,
Jan

3 Replies

@JanKrooshof7881 

 

Hier eine möglichkeit mit VBA

Option Explicit


Sub ListBoxfromOutlook()
Dim Verz       As Object
Dim iIndx      As Integer
Dim olMAPI     As Object
Dim objItem    As Object
Set olMAPI = CreateObject("Outlook.Application")
 
   Application.DisplayAlerts = False
   
   Application.StatusBar = "   the addresses are taken from Outlook " _
                         & "- This may take a moment."
             
   Set Verz = olMAPI.GetNamespace("MAPI").GetDefaultFolder(olFolderContacts)
   
   UserForm1.ListBox1.ColumnCount = 7
   UserForm1.ListBox1.ColumnWidths = _
            "7,0 cm; 3,5 cm; 1,0 cm; 3,0 cm; 1,0 cm; 3,5 cm; 3,0 cm"
   For iIndx = 1 To Verz.Items.Count
      Set objItem = Verz.Items(iIndx)
      With objItem
         UserForm1.ListBox1.AddItem " "
         UserForm1.ListBox1.List(iIndx - 1, 0) = .FirstName _
                                         & " " & .LastName
         If .BusinessAddressPostOfficeBox = "" Then
            UserForm1.ListBox1.List(iIndx - 1, 1) = .BusinessAddressStreet
          Else
            UserForm1.ListBox1.List(iIndx - 1, 1) = .BusinessAddressPostOfficeBox
         End If
         UserForm1.ListBox1.List(iIndx - 1, 2) = .BusinessAddressPostalCode
         UserForm1.ListBox1.List(iIndx - 1, 3) = .BusinessAddressCity
         UserForm1.ListBox1.List(iIndx - 1, 4) = .CustomerID
         UserForm1.ListBox1.List(iIndx - 1, 5) = .AssistantName
         UserForm1.ListBox1.List(iIndx - 1, 6) = .MiddleName
      End With
   Next iIndx
      
   Set objItem = Nothing
   Set olMAPI = Nothing
   
'   sort the ListBox by name (by only one, the first column)   
'   listbox_quick_sort 0, 6, UserForm1.ListBox1, 0, UserForm1.ListBox1.ListCount - 1
   
   Application.DisplayAlerts = True
   
   Application.StatusBar = False
              
End Sub

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE 

 

Hi Nikolino,

 

Thank you for your respons on my question. It has been a while ago, but I did not have the time before to get to your answer.

The VBA stops at: 

JanKrooshof7881_0-1629622331884.png

What can I do about that?

 

Kind regards,
Jan

 

mohammedshihab8/22/2021