Solved

How to filter according to distribution list using items.find via outlook VBA

Posted on 2014-02-15
12
536 Views
Last Modified: 2014-02-19
I'm trying to filter the contact folder to search for a specific DL .The problem is when I search usisng ContactFolder.Items[FolderName] I'm getting an exception if the DL doesn't exist.
Im trying another syntax ,ContactFodler.Items.Find[filter] but don't know which filter criteria I can use for DL ,can anybody help here ?

Thanks
0
Comment
Question by:AJDeveloper
  • 8
  • 4
12 Comments
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
Please supply the code you are using for the search (the code where the error occurs).

You could iterate through all the items which are found by your filter search and teat to see whether the "type" of the found item is a DL.

After you supply your code I will see if I can tweak it to demonstrate.

Cheers
Chris
0
 

Author Comment

by:AJDeveloper
Comment Utility
Hi Chris

I'm checking if the recipient is a DL so that I can retrieve its email addresses :

object DLst = SharedInfo.ContactFodler.Items[recipient.AddressEntry.Name]

this would throw an exception if the DL doesn't exist .

So m trying to filter the items according to the DL using the code:
SharedInfo.ContactFodler.Items.Find(Filter)

My issue is I don't know what word criteria I should use to filter the items because according to the MSDN link :
http://msdn.microsoft.com/en-us/library/office/ff869662.aspx

I we try to search via DLName it would give error .

Thanks
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
What version of Outlook are you using?
0
 

Author Comment

by:AJDeveloper
Comment Utility
Outlook 2013
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
Thx...stand by
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
Do you want the code that searches for the distribution list to return a distribution list object?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:AJDeveloper
Comment Utility
Hi Chris

Yes ,that's what I want but please don't give me code to loop the items in the contact folder because I already tried that and because I have many thousands of contacts ,outlook would hang when it loops

Thanks
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
I will give you that code first, because I think the following code will be fast.
(I could be wrong but give it a go)
 
If it is still slow, I will investigate down another path.

This code has a stub calling a function. If the Distribution list is found
a message box will appear and the code will stop.

Try itwith a valid distribution box name and then with a non-existant one
(to test how fast copde runs in the latter case).

 
Private Sub main1()
    Dim MyDL As Object
    Set MyDL = FindDL("Students List") 'change this to required name
    If Not MyDL Is Nothing Then
                ' do something with the DL
                '..........
                '..........
    Else
        MsgBox "DL named Students List not found!" 'change message as applicable
    End If
End Sub

Public Function FindDL(strDL As String) As Object
Dim myNameSpace As Outlook.NameSpace
Dim myFolder As Outlook.Folder 'use Outlook.MAPIFolder if Outlook.folder fails.
Dim myFolderItems As Outlook.Items
Dim x As Integer

Set myNameSpace = Application.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderContacts)
Set myFolderItems = myFolder.Items

iCount = myFolderItems.Count

For x = 1 To iCount
  If TypeName(myFolderItems.Item(x)) = "DistListItem" Then
    If myFolderItems.Item(x).DLName = strDL Then
      FindDL = myFolderItems.Item(x)
      MsgBox "Distribution List "+strDL+ " found!"
      Exit For
    End If
  End If
Next
End Function

Open in new window

0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
OK, forget that last comment.

I think I have found the way to attack this.

We will use the RESTRICT method, but use it on the MessageClass property, rather than the prohibited (or should I say, "unuseable" ) DLNAME property.

The above code then becomes:

Private Sub main1()
    Dim MyDL As Object
    Dim strName as string
    strDistName = "Student List"   'change to whatever you wish

    Set MyDL = FindDL(strDistName) 
    If Not MyDL Is Nothing Then
                ' do something with the DL
                '..........
                '..........
    Else
        MsgBox "DL named "+ strDistName +" not found!"
    End If
End Sub

Public Function FindDL(strDL As String) As Object
Dim myNameSpace As Outlook.NameSpace
Dim myFolder As Outlook.Folder 'use Outlook.MAPIFolder if Outlook.folder fails.
Dim myDistList As Outlook.DistListItem
Dim myFolderItems As Outlook.Items
Dim MyRestrictItems As Outlook.Items
Dim sList As String
Dim x As Integer

Set myNameSpace = Application.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderContacts)
Set myFolderItems = myFolder.Items
Set MyRestrictItems = myFolderItems.Restrict("[MessageClass] = 'IPM.DistList'")
If MyRestrictItems.Count > 0 Then
  For x = 1 To MyRestrictItems.Count
    If MyRestrictItems(x).DLName = strDL Then
      Set FindDL = MyRestrictItems(x)
      MsgBox "Dist List for " + strDL + " found  :-)"  'remove this after testing finished
      Exit For
    End If
  Next
End If
End Function

Open in new window

0
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 500 total points
Comment Utility
The final asnwer to your question is to use the RESTRICT function rather than the filter function (RESTRICT is very fast).

All the code I have given you is code I have just written and although it works to a point by supplying back to you the Distribution list, I am not sure how far you can go with that returned list as far as its values are concerned.

A distribution list contains all its internal members data in an object called  "OneOffMembers" but the values therein are not easily readable by ordinary code.

The only way I could find the values was (for example) by writing the following snippet to obtain email addresses (you could obtain other values as well). The values can be printed to the debug window, but for some reason I cannot work with the string contained within Member() since it is really a variant not a string array and the internals of that variant are hidden.

So, this bit of code may lead you on to the path you seek in your coding, anyway, and give you some thoughts.

I think that wraps up this solution since your original request to obtain the DL
has been achieved. :-)

Let me know how things pan out!

Cheers
Chris

Option Explicit
Private Sub main1()
    Dim MyDL As Object
    Dim strDistName As String
    Dim Members As Variant
    Dim nAt As Integer
    Dim x As Integer

    strDistName = "Jokes to Family"   'change to whatever you wish

    Set MyDL = FindDL(strDistName)
    If Not MyDL Is Nothing Then
      'do what you want here, but I am goinf to display email addresses for
      'each item in the distribution list
      Members = MyDL.OneOffMembers
      For x = 0 To MyDL.MemberCount - 1
         nAt = InStr(Members(x), "SMTP")
         If nAt > 0 Then
           Debug.Print CStr(x) + " " + Mid(Members(x), nAt + 4)
         End If
      Next
      Debug.Print "===================================================================="
    Else
      MsgBox "DL named " + strDistName + " not found!"
    End If
End Sub

Open in new window

0
 

Author Comment

by:AJDeveloper
Comment Utility
Thanks a lot Chris ,I will test it and update you
0
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
Any final comment on how things went?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now