?
Solved

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

Posted on 2014-02-15
12
Medium Priority
?
613 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Suggested Courses

764 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