How to import to excell outlook distribution list RRS feed

  • Question

  • Dear all,

    I have build a simple macro in order to get Distribution list  and memebers from the Global adress book in outlook

    The code snipet is as below

    Set olEntry = olGAL.AddressEntries
    On Error Resume Next
    'first row of entries
    curRowIdx = 2
    ' loop through dist list and extract members
    For i = 1 To olEntry.Count
    If olEntry.Item(i).AddressEntryUserType = olExchangeDistributionListAddressEntry Then
      'Set olMember = olEntry.Item(i)
        'If olMember.AddressEntryUserType = olExchangeDistributionListAddressEntry Then
        'add to worksheet
         lMemberCount = olEntry.Item(i).Members.Count
          ' loop through dist list and extract members
             For j = 1 To lMemberCount
              Set olMember = olEntry.Item(i).Members.Item(j)
              ListName = olEntry.Item(i).Name
              MemberName = olMember.Name
               ws.Cells(curRowIdx, 1).Value = ListName
               ws.Cells(curRowIdx, 2).Value = MemberName
               curRowIdx = curRowIdx + 1
            Next j
       'curRowIdx = curRowIdx + 1
    End If
    Next i

    The issue I get is that :

    1 - It is extremly slow to get data back to excell, more than 5 minutes , have you met this behaviour, and issue with my code 

    2 - During that time my all excell is freeze when I execute the macro., but in addition seems my outlook behaves slowly as well

    Any idea how to speed up ?


    • Edited by wakefun Friday, March 29, 2019 3:00 PM
    Friday, March 29, 2019 8:29 AM