Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Outlook distribution list hanging up on contact items query

Tags:

vba

outlook

I was working from some tutorial on MSDN to learn to make some macros for Outlook. I have this subroutine that gets hung up with a Type mismatch error. Upon stepping through error handling after Stop and Resume it goes back to Next and finishes the query.

Looking through the result set in Immediate, one item is missing which is actually a distribution mailing list instead of a normal contact. I moved the mailing list out of my contacts for testing, and the error did not happen.

I do plan on having other mailing lists as well, as this is for work. Is there a workaround like some way to escape it, other than just saving them somewhere else?

Here is the code:

Sub ContactName()

    On Error GoTo ErrHandler

    Dim ContactsFolder As Folder
    Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
    MsgBox ("Contacts found: " & ContactsFolder.Items.Count)

    Dim Contact As ContactItem
    For Each Contact In ContactsFolder.Items
        Debug.Print Contact.CompanyName
    Next
    Exit Sub

ErrHandler:
    Debug.Print Err.Description
    Stop
    Resume

End Sub
like image 216
Phrancis Avatar asked Nov 20 '14 22:11

Phrancis


People also ask

Why is my distribution list not working in Outlook?

Emails not being delivered to distribution groupMake sure you've waited the appropriate amount of time and try sending the email again. Sometimes, people create a Microsoft 365 group instead of a distribution group. Check out your distribution group in the admin center and make sure you created a distribution group.

How do I see all emails in a distribution list in Outlook?

To do this, you simply log in to your Outlook account and click on “File.” In the dropdown menu, select “Import and Export.” A dialogue box will pop up with some options—click on “Export to File,” then “Next.” The next step will be to select the file type you want to save your downloaded email list as.


2 Answers

Dim Contact As ContactItem
For Each Contact In ContactsFolder.Items
    Debug.Print Contact.CompanyName
Next

When you defined Contact as ContactItem you are telling VBA exactly what type of thing it should find in Items. This works great, only if all items in the ContactsFolder are actually ContactItems.

In other words, you are specifically going through all items in a bag but specifically making each of them an Apple - but when you encounter an Irange, VBA throws an error.

What I generally do in this situation is instead of saying that I want to go through all apples in a bag, I want to go through each item in a bag, so something like:

Dim mContact 'by not dimensioning it you basically allow mContact to become whatever type each item is 
For Each mContact In ContactsFolder.Items
    Debug.Print mContact.CompanyName
Next

Note that I changed your name to mContact because it's probable Contact is a keyword in VBA and it's sometimes better to not deal with this.

This above will still cause errors because any mContact which does not have a .CompanyName attribute.

What you can do is the following:

Dim mContact 
For Each mContact In ContactsFolder.Items
    if mContact.Class = olContact then
        Debug.Print mContact.CompanyName
    Else
        Debug.Print "Not a Contact! Actually a :" & typename(mContact)
    End if
Next

This will check for whether the object you are iterating through (the fruit from the bag) is actually an "apple" first, and if not, tell you what type of fruit it is.

like image 187
enderland Avatar answered Oct 13 '22 12:10

enderland


In order to distinguish between Lists and Contacts, you can alter your code to the following:

Sub ContactName()

On Error GoTo ErrHandler

Dim ContactsFolder As Folder
Set ContactsFolder = Session.GetDefaultFolder(olFolderContacts)
MsgBox ("Contacts found: " & ContactsFolder.Items.Count)

Dim Contact As ContactItem
Dim distList As DistListItem
Dim i As Integer

For i = 1 To ContactsFolder.Items.Count

    If TypeOf ContactsFolder.Items(i) Is DistListItem Then
      Set distList = ContactsFolder.Items(i)
      Debug.Print distList.DLName
    ElseIf TypeOf ContactsFolder.Items(i) Is ContactItem Then
      Set contact = ContactsFolder.Items(i)
      Debug.Print contact.FullName
    Else
      Debug.Print "Item is something else"
    End If

Next i
Exit Sub

ErrHandler:
    Debug.Print Err.Description
    Stop
    Resume

End Sub

Note, I changed the property I'm accessing from CompanyName to FullName for my tests as I didn't have CompanyName for all my contacts.

like image 33
DeanOC Avatar answered Oct 13 '22 11:10

DeanOC