none
Error in code RRS feed

  • Question

  • Hello,

    this is a code retrieving data from contacts - after inserting the email-address - there is an error, that i dont understand - is there a missing reference?

    thank you

    Martin

    Sub FindEmailAddressInContacts()
      Dim objApp As Application
      Dim objNS As NameSpace
      Dim objContacts As MAPIFolder
      Dim colItems As Items
      Dim objItem As Object
      Dim strAddress As String
      Dim strWhere As String
      Dim blnFound As Boolean
    
      ' get folder to search
      Set objApp = CreateObject("Outlook.Application")
      Set objNS = objApp.GetNamespace("MAPI")
      Set objContacts = objNS.GetDefaultFolder(olFolderContacts)
      strWhere = "[Email1Address] <> vbNullString " & _
                 "Or [Email2Address] <> vbNullString " & _
                 "Or [Email3Address] <> vbNullString "
      Set colItems = objContacts.Items.Restrict(strWhere)
    
      ' get address to search for
      strAddress = InputBox("Find an Address in Contacts")
      If strAddress <> "" Then
        colItems.SetColumns ("Email1Address, Email2Address, Email3Address")
        For Each objItem In colItems
          ' must test for item type to avoid distribution lists
          If TypeName(objItem) = "ContactItem" Then
            If InStr(objItem.Email1Address, strAddress) > 0 Then
              objItem.Display
              blnFound = True
              Exit For
            ElseIf InStr(objItem.Email2Address, strAddress) > 0 Then
              objItem.Display
              blnFound = True
              Exit For
            ElseIf InStr(objItem.Email3Address, strAddress) > 0 Then
              objItem.Display
              blnFound = True
              Exit For
            End If
          End If
        Next
      End If

    Tuesday, March 7, 2017 11:37 AM

Answers

All replies

  • Hello,

    I'd suggest checking the Count property of the Items class which returns a Long indicating the count of objects in the specified collection before calling the SetColumns method.

    Sub FindEmailAddressInContacts()
      Dim objApp As Application
      Dim objNS As NameSpace
      Dim objContacts As MAPIFolder
      Dim colItems As Items
      Dim objItem As Object
      Dim strAddress As String
      Dim strWhere As String
      Dim blnFound As Boolean
    
      ' get folder to search
      Set objApp = CreateObject("Outlook.Application")
      Set objNS = objApp.GetNamespace("MAPI")
      Set objContacts = objNS.GetDefaultFolder(olFolderContacts)
      strWhere = "[Email1Address] <> vbNullString " & _
                 "Or [Email2Address] <> vbNullString " & _
                 "Or [Email3Address] <> vbNullString "
      Set colItems = objContacts.Items.Restrict(strWhere)
    
      ' get address to search for
      strAddress = InputBox("Find an Address in Contacts")
      If strAddress <> "" Then
        If colItems.Count > 0 Then
        colItems.SetColumns ("Email1Address, Email2Address, Email3Address")
        For Each objItem In colItems
          ' must test for item type to avoid distribution lists
          If TypeName(objItem) = "ContactItem" Then
            If InStr(objItem.Email1Address, strAddress) > 0 Then
              objItem.Display
              blnFound = True
              Exit For
            ElseIf InStr(objItem.Email2Address, strAddress) > 0 Then
              objItem.Display
              blnFound = True
              Exit For
            ElseIf InStr(objItem.Email3Address, strAddress) > 0 Then
              objItem.Display
              blnFound = True
              Exit For
            End If
          End If
          End If
        Next
      End If

    What line of code fires an exception or error? Did you try to debug the code?

    Anyway, the SetColumns method has the following description in MSDN:

    The SetColumns method is useful for iterating through an Items collection. If you don't use this method, Microsoft Outlook must open each item to access the property. With the SetColumns method, Outlook only checks the properties that you have cached, and provides fast, read-only access to these properties.

    After applying the SetColumns method on specific properties of the collection, you cannot read other properties of that collection; properties which are not cached are returned empty. You cannot write to any of the properties of that collection either. Alternatively, if you require read-write, fast access to a set of items, use the Table object.

    Try to remove it from the code and see whether it helps.


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers


    Tuesday, March 7, 2017 1:06 PM
  • Thank you, the error occurs in the line

    colItems.SetColumns ("Email1Address, Email2Address, Email3Address")

    it Counts 706 items, that are the contacts in my contact Folder

    Microsoft Visual Basic for Applications

    Laufzeitfehler '-2147352567 (80020009':

    Die EmaillAddress, Emai[2Address, EmaiBAddress-Eigenschaft ist
    unbekannt.

    Hilfe

    in english the error is like

    runtime error

    the email 1, email2, email 3 property is unknown.

    Any idea left?

    Thank  you very much

    Tuesday, March 7, 2017 1:28 PM
  • Looks like not all item have got these properties set. So, you need to remove the SetColumns method or just use the Table object instead.

    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Tuesday, March 7, 2017 2:23 PM