ich habe den nachfolgenden Code zum Auslesen von Kontaktdaten aus Excel.
Jedoch wird nur der erste Kontakt ausgelesen und sobald die E-MailAdresse ausgelesen werden soll, kommt der Fehler: Laufzeitfehler 287
Wenn ich aber den E-Mail Import auskommentiere geht es.
Weis jemand warum ich die E-Maildresse nicht einlesen kann?
Sub ContactsToExcel()
Worksheets("Auslesen").Unprotect Password:="sperl"
'Deklaration
Dim oApp As New Outlook.Application
Dim nspMapi As Outlook.Namespace
Dim folMapi As Outlook.MAPIFolder
Dim itmAll As Outlook.Items
Dim itmReal As Outlook.Items
Dim itmContacts As Outlook.ContactItem
Dim strContactFilter As String
Dim excApp As Object
Dim excWkb As Object
Dim excWks As Object
Dim intRow As Integer
'Outlook-Objekte öffnen
Set nspMapi = oApp.GetNamespace("MAPI")
'Set folMapi = nspMapi.GetDefaultFolder(olFolderContacts)
Set folMapi = nspMapi.GetDefaultFolder(olFolderContacts).Folders(Sheets(2).Range("H6"). _
Value)
Set itmAll = folMapi.Items
'Verteilerlisten herausfiltern,
'nur 'Richtige Kontakte' verwenden
strContactFilter = "[MessageClass] = 'IPM.Contact'"
Set itmReal = itmAll.Restrict(strContactFilter)
'Excel-Objekte öffnen
Set excApp = Excel.Application
Set excWkb = excApp.ThisWorkbook 'Neues Workbook anlegen
Set excWks = excWkb.Sheets(2) 'Erstes Sheet
'Excel-Worksheet aufbereiten
With excWks
'Sheet-Name
.Name = "Auslesen"
'Spaltenüberschriften
'.Cells(1, 1).Value = "Vorname"
'.Cells(1, 2).Value = "Nachname"
'.Cells(1, 3).Value = "Strasse"
'.Cells(1, 4).Value = "PLZ"
'.Cells(1, 5).Value = "Ort"
'.Cells(1, 6).Value = "Land"
'.Cells(1, 7).Value = "Telefon"
'Spaltenüberschriften fett
'.Rows("1:1").Font.Bold = True
'Outlook-Kontakte nach Excel übertragen
intRow = 9
For Each itmContacts In itmReal
.Cells(intRow, 1).Value = itmContacts.FirstName
.Cells(intRow, 2).Value = itmContacts.LastName
.Cells(intRow, 3).Value = itmContacts.CompanyName
.Cells(intRow, 4).Value = itmContacts.JobTitle
.Cells(intRow, 5).Value = itmContacts.BusinessAddressStreet
.Cells(intRow, 6).Value = itmContacts.BusinessAddressPostalCode
.Cells(intRow, 7).Value = itmContacts.BusinessAddressCity
.Cells(intRow, 8).Value = itmContacts.BusinessAddressCountry
.Cells(intRow, 9).Value = itmContacts.BusinessFaxNumber
.Cells(intRow, 10).Value = itmContacts.BusinessTelephoneNumber
.Cells(intRow, 11).Value = itmContacts.MobileTelephoneNumber
.Cells(intRow, 12).Value = itmContacts.Email1Address
intRow = intRow + 1
Next itmContacts
'Optimale Spaltenbreite
.Columns.AutoFit
End With
'Excel einblenden
excApp.Visible = True
'Speicher freigeben
Set itmReal = Nothing
Set itmAll = Nothing
Set folMapi = Nothing
Set nspMapi = Nothing
Set excWks = Nothing
Set excWkb = Nothing
Set excApp = Nothing
Worksheets("Auslesen").Protect Password:="sperl"
End Sub