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 said:
On Dec 18, 5:01 pm, "Sue Mosher [MVP-Outlook]"
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" <
[email protected]> 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.
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
