How to compare 2 contacts with VBA?

Hi,

I have written a macro that lists all duplicates in an outlook
contacts folder. Now that I know which contacts are duplicated, I am
trying to find out WHAT differences, if any, there are between the
duplicates.

So, does anyone know how to find/list all the non empty fields in any
given contact using vba?

Thanks.

You can use the ItemProperties collection to iterate all the standard fields except for collection like Links.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"Chris S" <cseilern@gmail.com> wrote in message news:e6612f28-27d6-44ca-ba2e-59078769d206@t1g2000pra.googlegroups.com...
> Hi,
>
> I have written a macro that lists all duplicates in an outlook
> contacts folder. Now that I know which contacts are duplicated, I am
> trying to find out WHAT differences, if any, there are between the
> duplicates.
>
> So, does anyone know how to find/list all the non empty fields in any
> given contact using vba?
>
> Thanks.

On Dec 18, 3:52 pm, "Sue Mosher [MVP-Outlook]"
<sue...@outlookcode.com> wrote:
> You can use the ItemProperties collection to iterate all the standard fields except for collection like Links.
>
> --
> Sue Mosher, Outlook MVP
> Author of Microsoft Outlook 2007 Programming:
> Jumpstart for Power Users and Administrators
> http://www.outlookcode.com/article.aspx?id=54
>
> "Chris S" <cseil...@gmail.com> wrote in messagenews:e6612f28-27d6-44ca-ba2e-59078769d206@t1g2000pra.googlegroups.com...
> > Hi,
>
> > I have written a macro that lists all duplicates in an outlook
> > contacts folder. Now that I know which contacts are duplicated, I am
> > trying to find out WHAT differences, if any, there are between the
> > duplicates.
>
> > So, does anyone know how to find/list all the non empty fields in any
> > given contact using vba?
>
> > Thanks.

Thanks Sue. I am currently trying that route, but so far not
successfully! this is what I have:


For Each ItemProperty In olColItems(i).ItemProperties
If ItemProperty Is Empty Then propCount = propCount + 1
Next

But I get an object required error on the If statement... I am not
sure i am using it properly, do you have any ideas what I am doing
wrong? i am trying to get proCount to be populated with the number of
fields that are NOT empty in any given contact.

How is olColItems instantiated?

I'm not sure that Empty is going to work, as standard properties generally do have values. Try looking at ItemProperty.Type and then test for "", 0, and #1/1/4501#, depending on whether the property is text, numeric, or date/time. You should ignore any properties where Type = olOutlookInternal, olFormula, or olCombination.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"Chris S" <cseilern@gmail.com> wrote in message news:f90cc6a0-7326-4eee-af58-fd17719add3b@s19g2000prg.googlegroups.com...
>>
>> > I have written a macro that lists all duplicates in an outlook
>> > contacts folder. Now that I know which contacts are duplicated, I am
>> > trying to find out WHAT differences, if any, there are between the
>> > duplicates.
>>
> this is what I have:
>
> For Each ItemProperty In olColItems(i).ItemProperties
> If ItemProperty Is Empty Then propCount = propCount + 1
> Next
>
> But I get an object required error on the If statement... I am not
> sure i am using it properly, do you have any ideas what I am doing
> wrong? i am trying to get proCount to be populated with the number of
> fields that are NOT empty in any given contact.

On Dec 18, 5:01 pm, "Sue Mosher [MVP-Outlook]"
<sue...@outlookcode.com> wrote:
> How is olColItems instantiated?
>
> I'm not sure that Empty is going to work, as standard properties generally do have values. Try looking at ItemProperty.Type and then test for "", 0, and #1/1/4501#, depending on whether the property is text, numeric, or date/time. You should ignore any properties where Type = olOutlookInternal, olFormula, or olCombination.
> --
> Sue Mosher, Outlook MVP
> Author of Microsoft Outlook 2007 Programming:
> Jumpstart for Power Users and Administrators
> http://www.outlookcode.com/article.aspx?id=54
>
> "Chris S" <cseil...@gmail.com> wrote in messagenews:f90cc6a0-7326-4eee-af58-fd17719add3b@s19g2000prg.googlegroups.com...
>
> >> > I have written a macro that lists all duplicates in an outlook
> >> > contacts folder. Now that I know which contacts are duplicated, I am
> >> > trying to find out WHAT differences, if any, there are between the
> >> > duplicates.
>
> > this is what I have:
>
> > For Each ItemProperty In olColItems(i).ItemProperties
> > If ItemProperty Is Empty Then propCount = propCount + 1
> > Next
>
> > But I get an object required error on the If statement... I am not
> > sure i am using it properly, do you have any ideas what I am doing
> > wrong? i am trying to get proCount to be populated with the number of
> > fields that are NOT empty in any given contact.

olcolitems is DIM'ed as outlook.items a an SET to contain individual
contact items from the default contact folder (i am also DIM'ming/
SET'ing an outlook.application, an outlook.namespace and an
outlook.mapifolder), and seems to work well in the context of my
duplicate finding macro.

i dont, however, understand what you mean by testing for property
type, as i am looking to count/list non empty fields.

The problem is that "non empty" is meaningless in your scenario, because standard properties generally are not null or empty. They have values. If you want to know whether a property is "blank," you need to test for the value appropriate to that type of property. For example, you might consider a "blank" text property to be one with a value of "". A "blank" date/time property would have a value of #1/1/4501#.

Please show the code you're using to instantiate olColItems.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"Chris S" <cseilern@gmail.com> wrote in message news:332fde4f-85ef-4bf4-affd-76d4cb5c3666@w56g2000hsf.googlegroups.com...
> On Dec 18, 5:01 pm, "Sue Mosher [MVP-Outlook]"
> <sue...@outlookcode.com> wrote:
>> How is olColItems instantiated?
>>
>> I'm not sure that Empty is going to work, as standard properties generally do have values. Try looking at ItemProperty.Type and then test for "", 0, and #1/1/4501#, depending on whether the property is text, numeric, or date/time. You should ignore any properties where Type = olOutlookInternal, olFormula, or olCombination.

>> "Chris S" <cseil...@gmail.com> wrote in messagenews:f90cc6a0-7326-4eee-af58-fd17719add3b@s19g2000prg.googlegroups..com...
>>
>> >> > I have written a macro that lists all duplicates in an outlook
>> >> > contacts folder. Now that I know which contacts are duplicated, I am
>> >> > trying to find out WHAT differences, if any, there are between the
>> >> > duplicates.
>>
>> > this is what I have:
>>
>> > For Each ItemProperty In olColItems(i).ItemProperties
>> > If ItemProperty Is Empty Then propCount = propCount + 1
>> > Next
>>
>> > But I get an object required error on the If statement... I am not
>> > sure i am using it properly, do you have any ideas what I am doing
>> > wrong? i am trying to get proCount to be populated with the number of
>> > fields that are NOT empty in any given contact.
>
> olcolitems is DIM'ed as outlook.items a an SET to contain individual
> contact items from the default contact folder (i am also DIM'ming/
> SET'ing an outlook.application, an outlook.namespace and an
> outlook.mapifolder), and seems to work well in the context of my
> duplicate finding macro.
>
> i dont, however, understand what you mean by testing for property
> type, as i am looking to count/list non empty fields.
>

On Dec 18, 5:36 pm, "Sue Mosher [MVP-Outlook]"
<sue...@outlookcode.com> wrote:
> The problem is that "non empty" is meaningless in your scenario, because standard properties generally are not null or empty. They have values. If you want to know whether a property is "blank," you need to test for the value appropriate to that type of property. For example, you might consider a "blank" text property to be one with a value of "". A "blank" date/time property would have a value of #1/1/4501#.
>
> Please show the code you're using to instantiate olColItems.
> --
> Sue Mosher, Outlook MVP
> Author of Microsoft Outlook 2007 Programming:
> Jumpstart for Power Users and Administrators
> http://www.outlookcode.com/article.aspx?id=54
>
> "Chris S" <cseil...@gmail.com> wrote in messagenews:332fde4f-85ef-4bf4-affd-76d4cb5c3666@w56g2000hsf.googlegroups.com...
> > On Dec 18, 5:01 pm, "Sue Mosher [MVP-Outlook]"
> > <sue...@outlookcode.com> wrote:
> >> How is olColItems instantiated?
>
> >> I'm not sure that Empty is going to work, as standard properties generally do have values. Try looking at ItemProperty.Type and then test for "", 0, and #1/1/4501#, depending on whether the property is text, numeric, or date/time. You should ignore any properties where Type = olOutlookInternal, olFormula, or olCombination.
> >> "Chris S" <cseil...@gmail.com> wrote in messagenews:f90cc6a0-7326-4eee-af58-fd17719add3b@s19g2000prg.googlegroups.com...
>
> >> >> > I have written a macro that lists all duplicates in an outlook
> >> >> > contacts folder. Now that I know which contacts are duplicated, I am
> >> >> > trying to find out WHAT differences, if any, there are between the
> >> >> > duplicates.
>
> >> > this is what I have:
>
> >> > For Each ItemProperty In olColItems(i).ItemProperties
> >> > If ItemProperty Is Empty Then propCount = propCount + 1
> >> > Next
>
> >> > But I get an object required error on the If statement... I am not
> >> > sure i am using it properly, do you have any ideas what I am doing
> >> > wrong? i am trying to get proCount to be populated with the number of
> >> > fields that are NOT empty in any given contact.
>
> > olcolitems is DIM'ed as outlook.items a an SET to contain individual
> > contact items from the default contact folder (i am also DIM'ming/
> > SET'ing an outlook.application, an outlook.namespace and an
> > outlook.mapifolder), and seems to work well in the context of my
> > duplicate finding macro.
>
> > i dont, however, understand what you mean by testing for property
> > type, as i am looking to count/list non empty fields.

thanks again for that Sue. olcolitems code below. Is there also a way
of NOT testing each individual type for blanks? i.e. I am only really
interested in the contents of any particular field (whatever that
filed may be) if the user has populated it with data.



Sub count_duplicates() ' will count duplicate outlook contacts from
within excel and list them in column A

'declare variables
Dim olApp As Outlook.Application
Dim olNamespace As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim olColItems As Outlook.Items
'Dim olProperties As Outlook.ItemProperties
'Dim olProp As Outlook.ItemProperty
Dim i, d As Integer ' used to count items
Dim dupes As String

' set start values
Set olApp = New Outlook.Application
Set olNamespace = olApp.GetNamespace("MAPI")
Set olFolder = olNamespace.GetDefaultFolder(olFolderContacts)
'Set olFolder = olNamespace.PickFolder
Set olColItems = olFolder.Items
dupes = ""
d = 0
Range("A:A").ClearContents
Range("A1") = "The following contacts are duplicates:"


' count items
d = olColItems.Count

' sort the database (used to count dupes)
Dim strTri
strTri = ""
strTri = strTri & "[LastName]"
strTri = strTri & "[FirstName]"
strTri = strTri & "[CompanyName]"
strTri = strTri & "[FileAs]"
strTri = strTri & "[Sensitivity]"
olColItems.Sort strTri

' count successive equal items (used to count dupes)
Dim lastStr, Str, nbrCount ', propCount
lastStr = ""
nbrCount = 0

For i = olColItems.Count To 1 Step -1
propCount = 0
If Not olColItems(i).Class = olDistributionList Then
Str = ""
Str = Str & vbCrLf & olColItems(i).LastName
Str = Str & vbCrLf & olColItems(i).FirstName
Str = Str & vbCrLf & olColItems(i).CompanyName
Str = Str & vbCrLf & olColItems(i).FileAs
Str = Str & vbCrLf & olColItems(i).Sensitivity
Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
If Str = lastStr Then
' propCount = olColItems(i).ItemProperties.Count
' Set olProperties = olColItems(i).ItemProperties
' For Each ItemProperty In olColItems(i).ItemProperties
' If ItemProperty Is Empty Then propCount = propCount + 1
' Next
nbrCount = nbrCount + 1
dupes = dupes & olColItems(i).FileAs & vbCrLf
Range("A" & nbrCount + 1) = olColItems(i).FileAs
' Range("B" & nbrCount + 1) = propCount
End If
lastStr = Str
End If
Next i

' message box to display the results of the count
MsgBox "There are " & d & " items, of which " & nbrCount & " have
duplicate First Name, Last Name and Company Name" & vbCrLf

' reset start values to nothing
Set olColItems = Nothing
Set olFolder = Nothing
Set olNamespace = Nothing
Set olApp = Nothing

End Sub

The problem with your code is the usage of Empty. Instead of:

If <expression> Is Empty Then

it should be

If <expression> = Empty Then

The only way to know whether that will work for the fields you're interested in is to try it.

FWIW, I would instantiate a ContactItem, rather than make multiple calls to olColItems(i).Class in your For Each loop.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"Chris S" <cseilern@gmail.com> wrote in message news:2c01e410-cadc-4509-809e-841c59128329@e10g2000prf.googlegroups.com...
> On Dec 18, 5:36 pm, "Sue Mosher [MVP-Outlook]"
> <sue...@outlookcode.com> wrote:
>> The problem is that "non empty" is meaningless in your scenario, because standard properties generally are not null or empty. They have values. If you want to know whether a property is "blank," you need to test for the value appropriate to that type of property. For example, you might consider a "blank" text property to be one with a value of "". A "blank" date/time property would have a value of #1/1/4501#.
>>
>> Please show the code you're using to instantiate olColItems.
>>
>> "Chris S" <cseil...@gmail.com> wrote in messagenews:332fde4f-85ef-4bf4-affd-76d4cb5c3666@w56g2000hsf.googlegroups..com...
>> > On Dec 18, 5:01 pm, "Sue Mosher [MVP-Outlook]"
>> > <sue...@outlookcode.com> wrote:
>> >> How is olColItems instantiated?
>>
>> >> I'm not sure that Empty is going to work, as standard properties generally do have values. Try looking at ItemProperty.Type and then test for "", 0, and #1/1/4501#, depending on whether the property is text, numeric, or date/time. You should ignore any properties where Type = olOutlookInternal, olFormula, or olCombination.
>> >> "Chris S" <cseil...@gmail.com> wrote in messagenews:f90cc6a0-7326-4eee-af58-fd17719add3b@s19g2000prg.googlegroups..com...
>>
>> >> >> > I have written a macro that lists all duplicates in an outlook
>> >> >> > contacts folder. Now that I know which contacts are duplicated, I am
>> >> >> > trying to find out WHAT differences, if any, there are between the
>> >> >> > duplicates.
>>
>> >> > this is what I have:
>>
>> >> > For Each ItemProperty In olColItems(i).ItemProperties
>> >> > If ItemProperty Is Empty Then propCount = propCount + 1
>> >> > Next
>>
>> >> > But I get an object required error on the If statement... I am not
>> >> > sure i am using it properly, do you have any ideas what I am doing
>> >> > wrong? i am trying to get proCount to be populated with the number of
>> >> > fields that are NOT empty in any given contact.
>>
>> > olcolitems is DIM'ed as outlook.items a an SET to contain individual
>> > contact items from the default contact folder (i am also DIM'ming/
>> > SET'ing an outlook.application, an outlook.namespace and an
>> > outlook.mapifolder), and seems to work well in the context of my
>> > duplicate finding macro.
>>
>> > i dont, however, understand what you mean by testing for property
>> > type, as i am looking to count/list non empty fields.
>
> thanks again for that Sue. olcolitems code below. Is there also a way
> of NOT testing each individual type for blanks? i.e. I am only really
> interested in the contents of any particular field (whatever that
> filed may be) if the user has populated it with data.
>
>
>
> Sub count_duplicates() ' will count duplicate outlook contacts from
> within excel and list them in column A
>
> 'declare variables
> Dim olApp As Outlook.Application
> Dim olNamespace As Outlook.Namespace
> Dim olFolder As Outlook.MAPIFolder
> Dim olColItems As Outlook.Items
> 'Dim olProperties As Outlook.ItemProperties
> 'Dim olProp As Outlook.ItemProperty
> Dim i, d As Integer ' used to count items
> Dim dupes As String
>
> ' set start values
> Set olApp = New Outlook.Application
> Set olNamespace = olApp.GetNamespace("MAPI")
> Set olFolder = olNamespace.GetDefaultFolder(olFolderContacts)
> 'Set olFolder = olNamespace.PickFolder
> Set olColItems = olFolder.Items
> dupes = ""
> d = 0
> Range("A:A").ClearContents
> Range("A1") = "The following contacts are duplicates:"
>
>
> ' count items
> d = olColItems.Count
>
> ' sort the database (used to count dupes)
> Dim strTri
> strTri = ""
> strTri = strTri & "[LastName]"
> strTri = strTri & "[FirstName]"
> strTri = strTri & "[CompanyName]"
> strTri = strTri & "[FileAs]"
> strTri = strTri & "[Sensitivity]"
> olColItems.Sort strTri
>
> ' count successive equal items (used to count dupes)
> Dim lastStr, Str, nbrCount ', propCount
> lastStr = ""
> nbrCount = 0
>
> For i = olColItems.Count To 1 Step -1
> propCount = 0
> If Not olColItems(i).Class = olDistributionList Then
> Str = ""
> Str = Str & vbCrLf & olColItems(i).LastName
> Str = Str & vbCrLf & olColItems(i).FirstName
> Str = Str & vbCrLf & olColItems(i).CompanyName
> Str = Str & vbCrLf & olColItems(i).FileAs
> Str = Str & vbCrLf & olColItems(i).Sensitivity
> Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
> Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
> Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
> Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
> If Str = lastStr Then
> ' propCount = olColItems(i).ItemProperties.Count
> ' Set olProperties = olColItems(i).ItemProperties
> ' For Each ItemProperty In olColItems(i).ItemProperties
> ' If ItemProperty Is Empty Then propCount = propCount + 1
> ' Next
> nbrCount = nbrCount + 1
> dupes = dupes & olColItems(i).FileAs & vbCrLf
> Range("A" & nbrCount + 1) = olColItems(i).FileAs
> ' Range("B" & nbrCount + 1) = propCount
> End If
> lastStr = Str
> End If
> Next i
>
> ' message box to display the results of the count
> MsgBox "There are " & d & " items, of which " & nbrCount & " have
> duplicate First Name, Last Name and Company Name" & vbCrLf
>
> ' reset start values to nothing
> Set olColItems = Nothing
> Set olFolder = Nothing
> Set olNamespace = Nothing
> Set olApp = Nothing
>
> End Sub

On Dec 18, 9:11 pm, "Sue Mosher [MVP-Outlook]"
<sue...@outlookcode.com> wrote:
> The problem with your code is the usage of Empty. Instead of:
>
> If <expression> Is Empty Then
>
> it should be
>
> If <expression> = Empty Then
>
> The only way to know whether that will work for the fields you're interested in is to try it.
>
> FWIW, I would instantiate a ContactItem, rather than make multiple calls to olColItems(i).Class in your For Each loop.
> --
> Sue Mosher, Outlook MVP
> Author of Microsoft Outlook 2007 Programming:
> Jumpstart for Power Users and Administrators
> http://www.outlookcode.com/article.aspx?id=54
>
> "Chris S" <cseil...@gmail.com> wrote in messagenews:2c01e410-cadc-4509-809e-841c59128329@e10g2000prf.googlegroups.com...
> > On Dec 18, 5:36 pm, "Sue Mosher [MVP-Outlook]"
> > <sue...@outlookcode.com> wrote:
> >> The problem is that "non empty" is meaningless in your scenario, because standard properties generally are not null or empty. They have values. If you want to know whether a property is "blank," you need to test for the value appropriate to that type of property. For example, you might consider a "blank" text property to be one with a value of "". A "blank" date/time property would have a value of #1/1/4501#.
>
> >> Please show the code you're using to instantiate olColItems.
>
> >> "Chris S" <cseil...@gmail.com> wrote in messagenews:332fde4f-85ef-4bf4-affd-76d4cb5c3666@w56g2000hsf.googlegroups.com...
> >> > On Dec 18, 5:01 pm, "Sue Mosher [MVP-Outlook]"
> >> > <sue...@outlookcode.com> wrote:
> >> >> How is olColItems instantiated?
>
> >> >> I'm not sure that Empty is going to work, as standard properties generally do have values. Try looking at ItemProperty.Type and then test for "", 0, and #1/1/4501#, depending on whether the property is text, numeric, or date/time. You should ignore any properties where Type = olOutlookInternal, olFormula, or olCombination.
> >> >> "Chris S" <cseil...@gmail.com> wrote in messagenews:f90cc6a0-7326-4eee-af58-fd17719add3b@s19g2000prg.googlegroups.com...
>
> >> >> >> > I have written a macro that lists all duplicates in an outlook
> >> >> >> > contacts folder. Now that I know which contacts are duplicated, I am
> >> >> >> > trying to find out WHAT differences, if any, there are between the
> >> >> >> > duplicates.
>
> >> >> > this is what I have:
>
> >> >> > For Each ItemProperty In olColItems(i).ItemProperties
> >> >> > If ItemProperty Is Empty Then propCount = propCount + 1
> >> >> > Next
>
> >> >> > But I get an object required error on the If statement... I am not
> >> >> > sure i am using it properly, do you have any ideas what I am doing
> >> >> > wrong? i am trying to get proCount to be populated with the number of
> >> >> > fields that are NOT empty in any given contact.
>
> >> > olcolitems is DIM'ed as outlook.items a an SET to contain individual
> >> > contact items from the default contact folder (i am also DIM'ming/
> >> > SET'ing an outlook.application, an outlook.namespace and an
> >> > outlook.mapifolder), and seems to work well in the context of my
> >> > duplicate finding macro.
>
> >> > i dont, however, understand what you mean by testing for property
> >> > type, as i am looking to count/list non empty fields.
>
> > thanks again for that Sue. olcolitems code below. Is there also a way
> > of NOT testing each individual type for blanks? i.e. I am only really
> > interested in the contents of any particular field (whatever that
> > filed may be) if the user has populated it with data.
>
> > Sub count_duplicates() ' will count duplicate outlook contacts from
> > within excel and list them in column A
>
> > 'declare variables
> > Dim olApp As Outlook.Application
> > Dim olNamespace As Outlook.Namespace
> > Dim olFolder As Outlook.MAPIFolder
> > Dim olColItems As Outlook.Items
> > 'Dim olProperties As Outlook.ItemProperties
> > 'Dim olProp As Outlook.ItemProperty
> > Dim i, d As Integer ' used to count items
> > Dim dupes As String
>
> > ' set start values
> > Set olApp = New Outlook.Application
> > Set olNamespace = olApp.GetNamespace("MAPI")
> > Set olFolder = olNamespace.GetDefaultFolder(olFolderContacts)
> > 'Set olFolder = olNamespace.PickFolder
> > Set olColItems = olFolder.Items
> > dupes = ""
> > d = 0
> > Range("A:A").ClearContents
> > Range("A1") = "The following contacts are duplicates:"
>
> > ' count items
> > d = olColItems.Count
>
> > ' sort the database (used to count dupes)
> > Dim strTri
> > strTri = ""
> > strTri = strTri & "[LastName]"
> > strTri = strTri & "[FirstName]"
> > strTri = strTri & "[CompanyName]"
> > strTri = strTri & "[FileAs]"
> > strTri = strTri & "[Sensitivity]"
> > olColItems.Sort strTri
>
> > ' count successive equal items (used to count dupes)
> > Dim lastStr, Str, nbrCount ', propCount
> > lastStr = ""
> > nbrCount = 0
>
> > For i = olColItems.Count To 1 Step -1
> > propCount = 0
> > If Not olColItems(i).Class = olDistributionList Then
> > Str = ""
> > Str = Str & vbCrLf & olColItems(i).LastName
> > Str = Str & vbCrLf & olColItems(i).FirstName
> > Str = Str & vbCrLf & olColItems(i).CompanyName
> > Str = Str & vbCrLf & olColItems(i).FileAs
> > Str = Str & vbCrLf & olColItems(i).Sensitivity
> > Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
> > Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
> > Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
> > Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
> > If Str = lastStr Then
> > ' propCount = olColItems(i).ItemProperties.Count
> > ' Set olProperties = olColItems(i).ItemProperties
> > ' For Each ItemProperty In olColItems(i).ItemProperties
> > ' If ItemProperty Is Empty Then propCount = propCount + 1
> > ' Next
> > nbrCount = nbrCount + 1
> > dupes = dupes & olColItems(i).FileAs & vbCrLf
> > Range("A" & nbrCount + 1) = olColItems(i).FileAs
> > ' Range("B" & nbrCount + 1) = propCount
> > End If
> > lastStr = Str
> > End If
> > Next i
>
> > ' message box to display the results of the count
> > MsgBox "There are " & d & " items, of which " & nbrCount & " have
> > duplicate First Name, Last Name and Company Name" & vbCrLf
>
> > ' reset start values to nothing
> > Set olColItems = Nothing
> > Set olFolder = Nothing
> > Set olNamespace = Nothing
> > Set olApp = Nothing
>
> > End Sub

Thanks for the ContactItem tip, Sue, very appreciated. But as far as
the For Each Loop, i don't think we are talking about the same thing
(or more likely than not, I have not explained it clealry as I am
still very much newbie here).

What I am tryin to achieve is to go through all the fields in an
outlook contact, and find how many and which ones have data without
having to test every single field individually. i.e. for example,
contact x may have a business phone, but contact y does not. So I am
trying to write code that will tell me how many fields have data, and
what their types are. Am i doing this correctly by trying to loop
through itemProperties?

> Am i doing this correctly by trying to loop through itemProperties?

Yes. We are talking about the same thing.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"Chris S" <cseilern@gmail.com> wrote in message news:e7435eb5-c910-4752-bd66-7eea925179d0@x69g2000hsx.googlegroups.com...
> On Dec 18, 9:11 pm, "Sue Mosher [MVP-Outlook]"
> <sue...@outlookcode.com> wrote:
>> The problem with your code is the usage of Empty. Instead of:
>>
>> If <expression> Is Empty Then
>>
>> it should be
>>
>> If <expression> = Empty Then
>>
>> The only way to know whether that will work for the fields you're interested in is to try it.
>>
>> FWIW, I would instantiate a ContactItem, rather than make multiple calls to olColItems(i).Class in your For Each loop.

>> "Chris S" <cseil...@gmail.com> wrote in messagenews:2c01e410-cadc-4509-809e-841c59128329@e10g2000prf.googlegroups..com...
>> > On Dec 18, 5:36 pm, "Sue Mosher [MVP-Outlook]"
>> > <sue...@outlookcode.com> wrote:
>> >> The problem is that "non empty" is meaningless in your scenario, because standard properties generally are not null or empty. They have values. If you want to know whether a property is "blank," you need to test for the value appropriate to that type of property. For example, you might consider a "blank" text property to be one with a value of "". A "blank" date/time property would have a value of #1/1/4501#.
>>
>> >> Please show the code you're using to instantiate olColItems.
>>
>> >> "Chris S" <cseil...@gmail.com> wrote in messagenews:332fde4f-85ef-4bf4-affd-76d4cb5c3666@w56g2000hsf.googlegroups..com...
>> >> > On Dec 18, 5:01 pm, "Sue Mosher [MVP-Outlook]"
>> >> > <sue...@outlookcode.com> wrote:
>> >> >> How is olColItems instantiated?
>>
>> >> >> I'm not sure that Empty is going to work, as standard properties generally do have values. Try looking at ItemProperty.Type and then test for "", 0, and #1/1/4501#, depending on whether the property is text, numeric, or date/time. You should ignore any properties where Type = olOutlookInternal, olFormula, or olCombination.
>> >> >> "Chris S" <cseil...@gmail.com> wrote in messagenews:f90cc6a0-7326-4eee-af58-fd17719add3b@s19g2000prg.googlegroups..com...
>>
>> >> >> >> > I have written a macro that lists all duplicates in an outlook
>> >> >> >> > contacts folder. Now that I know which contacts are duplicated, I am
>> >> >> >> > trying to find out WHAT differences, if any, there are between the
>> >> >> >> > duplicates.
>>
>> >> >> > this is what I have:
>>
>> >> >> > For Each ItemProperty In olColItems(i).ItemProperties
>> >> >> > If ItemProperty Is Empty Then propCount = propCount + 1
>> >> >> > Next
>>
>> >> >> > But I get an object required error on the If statement... I am not
>> >> >> > sure i am using it properly, do you have any ideas what I am doing
>> >> >> > wrong? i am trying to get proCount to be populated with the number of
>> >> >> > fields that are NOT empty in any given contact.
>>
>> >> > olcolitems is DIM'ed as outlook.items a an SET to contain individual
>> >> > contact items from the default contact folder (i am also DIM'ming/
>> >> > SET'ing an outlook.application, an outlook.namespace and an
>> >> > outlook.mapifolder), and seems to work well in the context of my
>> >> > duplicate finding macro.
>>
>> >> > i dont, however, understand what you mean by testing for property
>> >> > type, as i am looking to count/list non empty fields.
>>
>> > thanks again for that Sue. olcolitems code below. Is there also a way
>> > of NOT testing each individual type for blanks? i.e. I am only really
>> > interested in the contents of any particular field (whatever that
>> > filed may be) if the user has populated it with data.
>>
>> > Sub count_duplicates() ' will count duplicate outlook contacts from
>> > within excel and list them in column A
>>
>> > 'declare variables
>> > Dim olApp As Outlook.Application
>> > Dim olNamespace As Outlook.Namespace
>> > Dim olFolder As Outlook.MAPIFolder
>> > Dim olColItems As Outlook.Items
>> > 'Dim olProperties As Outlook.ItemProperties
>> > 'Dim olProp As Outlook.ItemProperty
>> > Dim i, d As Integer ' used to count items
>> > Dim dupes As String
>>
>> > ' set start values
>> > Set olApp = New Outlook.Application
>> > Set olNamespace = olApp.GetNamespace("MAPI")
>> > Set olFolder = olNamespace.GetDefaultFolder(olFolderContacts)
>> > 'Set olFolder = olNamespace.PickFolder
>> > Set olColItems = olFolder.Items
>> > dupes = ""
>> > d = 0
>> > Range("A:A").ClearContents
>> > Range("A1") = "The following contacts are duplicates:"
>>
>> > ' count items
>> > d = olColItems.Count
>>
>> > ' sort the database (used to count dupes)
>> > Dim strTri
>> > strTri = ""
>> > strTri = strTri & "[LastName]"
>> > strTri = strTri & "[FirstName]"
>> > strTri = strTri & "[CompanyName]"
>> > strTri = strTri & "[FileAs]"
>> > strTri = strTri & "[Sensitivity]"
>> > olColItems.Sort strTri
>>
>> > ' count successive equal items (used to count dupes)
>> > Dim lastStr, Str, nbrCount ', propCount
>> > lastStr = ""
>> > nbrCount = 0
>>
>> > For i = olColItems.Count To 1 Step -1
>> > propCount = 0
>> > If Not olColItems(i).Class = olDistributionList Then
>> > Str = ""
>> > Str = Str & vbCrLf & olColItems(i).LastName
>> > Str = Str & vbCrLf & olColItems(i).FirstName
>> > Str = Str & vbCrLf & olColItems(i).CompanyName
>> > Str = Str & vbCrLf & olColItems(i).FileAs
>> > Str = Str & vbCrLf & olColItems(i).Sensitivity
>> > Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
>> > Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
>> > Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
>> > Str = Replace(Str, vbCrLf & vbCrLf, vbCrLf)
>> > If Str = lastStr Then
>> > ' propCount = olColItems(i).ItemProperties.Count
>> > ' Set olProperties = olColItems(i).ItemProperties
>> > ' For Each ItemProperty In olColItems(i).ItemProperties
>> > ' If ItemProperty Is Empty Then propCount = propCount + 1
>> > ' Next
>> > nbrCount = nbrCount + 1
>> > dupes = dupes & olColItems(i).FileAs & vbCrLf
>> > Range("A" & nbrCount + 1) = olColItems(i).FileAs
>> > ' Range("B" & nbrCount + 1) = propCount
>> > End If
>> > lastStr = Str
>> > End If
>> > Next i
>>
>> > ' message box to display the results of the count
>> > MsgBox "There are " & d & " items, of which " & nbrCount & " have
>> > duplicate First Name, Last Name and Company Name" & vbCrLf
>>
>> > ' reset start values to nothing
>> > Set olColItems = Nothing
>> > Set olFolder = Nothing
>> > Set olNamespace = Nothing
>> > Set olApp = Nothing
>>
>> > End Sub
>
> Thanks for the ContactItem tip, Sue, very appreciated. But as far as
> the For Each Loop, i don't think we are talking about the same thing
> (or more likely than not, I have not explained it clealry as I am
> still very much newbie here).
>
> What I am tryin to achieve is to go through all the fields in an
> outlook contact, and find how many and which ones have data without
> having to test every single field individually. i.e. for example,
> contact x may have a business phone, but contact y does not. So I am
> trying to write code that will tell me how many fields have data, and
> what their types are. Am i doing this correctly by trying to loop
> through itemProperties?