Jun 18 2021 07:21 AM
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
Jun 18 2021 08:29 AM
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.
Aug 22 2021 01:53 AM
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:
What can I do about that?
Kind regards,
Jan