Solved

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

Posted on 2014-02-15
12
564 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
ID: 39862100
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
ID: 39862382
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
ID: 39862392
What version of Outlook are you using?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:AJDeveloper
ID: 39862398
Outlook 2013
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 39862445
Thx...stand by
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 39862510
Do you want the code that searches for the distribution list to return a distribution list object?
0
 

Author Comment

by:AJDeveloper
ID: 39862515
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
ID: 39862525
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
ID: 39862553
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
ID: 39862690
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
ID: 39862779
Thanks a lot Chris ,I will test it and update you
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 39871540
Any final comment on how things went?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
If you don't know how to downgrade, my instructions below should be helpful.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

791 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