OLK2K3: Date comparison fails with unknown exception error

[Bill Billmire]

During the recent upgrade of my form (added printing and user notification
functions), I was required to change some of the Outlook field names to
operate with "Words" bookmarks. Now I want to export the historical data
from previous months/years to MSAccess, and due to the change in the forms
field names, I need to compare the two forms for changes and process the data
differently depending on the results of the test. I can test for the date,
or if the field name changed. However, I get errors when testing for either
condition.
The comparison of the Date/Time fails with "unknown exception" error.
The comparison of the Field/String fails with "Object variable not set" error.

Date/Time Compare: (If itm.SentOn <= "#1/1/2005 1:00:00PM#" then)
Field/String Compare: (If itm.UserProperties("Assigned To") = "Assigned To"
then)

Is there a better way to do this? I've been experimenting all day without
much progress.

'----------------Export Loop Code----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim ItemCount
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If

For Each itm in itms
rst.AddNew

If itm.SentOn <= "#1/1/2005 1:00:00PM#" then

'Export the following items if before 1/1/2005
rst.AssignedTo = itm.UserProperties("Assigned To")
rst.ClosedBy = itm.UserProperties("Closed By")
rst.DepartmentName = itm.UserProperties("DepartmentName")
rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
rst.FullName = itm.UserProperties("FullName")
rst.ITComments = itm.UserProperties("ITComments")
rst.OSRPriority = itm.UserProperties("Problem Priority")
rst.OSRStatus = itm.UserProperties("Problem Status")
rst.PhoneExtension = itm.UserProperties("Phone Extension")
rst.ProblemDescription = itm.UserProperties("ProblemDescription")
rst.ProductName = itm.UserProperties("Product Name")
rst.ProductVersion = itm.UserProperties("Product Version")
rst.Sent = CStr(itm.SentOn)
rst.TicketID = itm.UserProperties("TicketID")
rst.Update

Else

'Export the following items if after 1/1/2005
rst.AssignedTo = itm.UserProperties("AssignedTo")
rst.ClosedBy = itm.UserProperties("ClosedBy")
rst.DepartmentName = itm.UserProperties("DepartmentName")
rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
rst.FullName = itm.UserProperties("FullName")
rst.ITComments = itm.UserProperties("ITComments")
rst.OSRPriority = itm.UserProperties("OSRPriority")
rst.OSRStatus = itm.UserProperties("OSRStatus")
rst.PhoneExtension = itm.UserProperties("PhoneExtension")
rst.ProblemDescription = itm.UserProperties("ProblemDescription")
rst.ProductName = itm.UserProperties("ProductName")
rst.ProductVersion = itm.UserProperties("ProductVersion")
rst.Sent = CStr(itm.SentOn)
rst.TicketID = itm.UserProperties("TicketID")
rst.Update
End If
Next

rst.Close
MsgBox ItemCount & "All OSR's exported!"

End Sub

 

[Sue Mosher [MVP-Outlook]]

The comparison of the Date/Time fails with "unknown exception" error.

Date/Time Compare: (If itm.SentOn <= "#1/1/2005 1:00:00PM#" then)

Did you try using DateDiff() instead? You should also always check itm.Class
before invoking properties specific to a certain type of item.

The comparison of the Field/String fails with "Object variable not set"
error.

Field/String Compare: (If itm.UserProperties("Assigned To") = "Assigned
To"
then)

This would seem indicate that there is no custom "Assigned To" property in
the item in question.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Bill Billmire]

Sue,

What "tool" would I use to inspect the messages for custom fields embedded
in the messages themselves? OutlookSpy? I guess, I need to see whats inside
these old messages to determine how to export their data.

Thanks,

Bill Billmire -

The comparison of the Date/Time fails with "unknown exception" error.

Date/Time Compare: (If itm.SentOn <= "#1/1/2005 1:00:00PM#" then)

Did you try using DateDiff() instead? You should also always check itm.Class
before invoking properties specific to a certain type of item.

The comparison of the Field/String fails with "Object variable not set"
error.

Field/String Compare: (If itm.UserProperties("Assigned To") = "Assigned
To"
then)

This would seem indicate that there is no custom "Assigned To" property in
the item in question.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Sue Mosher [MVP-Outlook]]

I'd just write a few lines of code to iterate the UserProperties collection.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Bill Billmire]

Thanks Sue,

What I need to do is test the name of the field... is it "Assigned To" or is
it "AssignedTo"; [without the space], depending on whether this is true or
false the correct export code then gets executed. For this test, I don't
care what the contents of the field is, only it's name. Is the "Field Name"
in the UserProperties collection?

Thanks,

Bill Billmire -

 

[Sue Mosher [MVP-Outlook]]

The only thing in the UserProperties collection are individual UserProperty
items. Either an AssignedTo property is there or it's not. You'll have to
try to get it in order to find out:

Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
' so do something else
End If

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Thanks Sue,

What I need to do is test the name of the field... is it "Assigned To" or
is
it "AssignedTo"; [without the space], depending on whether this is true or
false the correct export code then gets executed. For this test, I don't
care what the contents of the field is, only it's name. Is the "Field
Name"
in the UserProperties collection?

Thanks,

Bill Billmire -

I'd just write a few lines of code to iterate the UserProperties
collection.

 

[Bill Billmire]

Hi Sue!

I tried your sample code, but I cannot get to work. (error: Object
required: 'objItem'). What I want to find, is the name of the control (which
is either AssignedTo or Assigned To). If I look in OutlookSpy, in one of the
posts, and click on IMessage, the Property Tag is: 0x8209 Assigned To, so I
know it is there... How do I get that "Property Tag" information?

Looking at your book... Page 368 (Referring to Outlook form controls) seems
similar to what [I think] I want to do... but... I am still not able to
pull
that Control/Property Tag data.

Current Code Below:

Option Explicit

'---------------Exporting-----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim objProp
Dim objPage
Dim ItemCount
Dim objItem
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If

For Each itm in itms
Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
End If
stop
Next

'---the balance is commented out until I can find the control name "Assigned
To"----
' For Each itm in itms
' rst.AddNew
'
' If itm.UserProperties(Assigned To) = "Assigned To" then
'
' 'Export the following items if "Assigned To"
' rst.AssignedTo = itm.UserProperties("Assigned To")
' rst.ClosedBy = itm.UserProperties("Closed By")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("Problem Priority")
' rst.OSRStatus = itm.UserProperties("Problem Status")
' rst.PhoneExtension = itm.UserProperties("Phone Extension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("Product Name")
' rst.ProductVersion = itm.UserProperties("Product Version")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
'
' Else
'
' 'Export the following items if "AssignedTo"
' rst.AssignedTo = itm.UserProperties("AssignedTo")
' rst.ClosedBy = itm.UserProperties("ClosedBy")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("OSRPriority")
' rst.OSRStatus = itm.UserProperties("OSRStatus")
' rst.PhoneExtension = itm.UserProperties("PhoneExtension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("ProductName")
' rst.ProductVersion = itm.UserProperties("ProductVersion")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
' End If
' Next
'
' rst.Close
' MsgBox ItemCount & "All OSR's exported!"

End Sub

Bill Billmire -

The only thing in the UserProperties collection are individual UserProperty
items. Either an AssignedTo property is there or it's not. You'll have to
try to get it in order to find out:

Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
' so do something else
End If

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Thanks Sue,

What I need to do is test the name of the field... is it "Assigned To" or
is
it "AssignedTo"; [without the space], depending on whether this is true or
false the correct export code then gets executed. For this test, I don't
care what the contents of the field is, only it's name. Is the "Field
Name"
in the UserProperties collection?

Thanks,

Bill Billmire -

I'd just write a few lines of code to iterate the UserProperties
collection.

 

[Sue Mosher [MVP-Outlook]]

The point of the sample was to illustrate how to determine whether a
particular custom property exists. You need to provide a statement somewhere
to instantiate an object variable representing the item you want to work
with. I had no way of knowing what item that might be. Since your code below
is working with a For Each loop that assigns itm as the object variable,
that's the variable you need to be using with UserProperties.

What I want to find, is the name of the control (which
is either AssignedTo or Assigned To).

When did controls get into this? I thought we were talking about custom
properties. Controls are a different matter altogether.

See http://www.cdolive.com/cdo10.htm for a primer on MAPI property tags. The
Redemption web site has additional information along the same lines.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Hi Sue!

I tried your sample code, but I cannot get to work. (error: Object
required: 'objItem'). What I want to find, is the name of the control
(which
is either AssignedTo or Assigned To). If I look in OutlookSpy, in one of
the
posts, and click on IMessage, the Property Tag is: 0x8209 Assigned To, so
I
know it is there... How do I get that "Property Tag" information?

Looking at your book... Page 368 (Referring to Outlook form controls)
seems
similar to what [I think] I want to do... but... I am still not able to
pull
that Control/Property Tag data.

Current Code Below:

Option Explicit

'---------------Exporting-----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim objProp
Dim objPage
Dim ItemCount
Dim objItem
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If

For Each itm in itms
Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
End If
stop
Next

'---the balance is commented out until I can find the control name
"Assigned
To"----
' For Each itm in itms
' rst.AddNew
'
' If itm.UserProperties(Assigned To) = "Assigned To" then
'
' 'Export the following items if "Assigned To"
' rst.AssignedTo = itm.UserProperties("Assigned To")
' rst.ClosedBy = itm.UserProperties("Closed By")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("Problem Priority")
' rst.OSRStatus = itm.UserProperties("Problem Status")
' rst.PhoneExtension = itm.UserProperties("Phone Extension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("Product Name")
' rst.ProductVersion = itm.UserProperties("Product Version")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
'
' Else
'
' 'Export the following items if "AssignedTo"
' rst.AssignedTo = itm.UserProperties("AssignedTo")
' rst.ClosedBy = itm.UserProperties("ClosedBy")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("OSRPriority")
' rst.OSRStatus = itm.UserProperties("OSRStatus")
' rst.PhoneExtension = itm.UserProperties("PhoneExtension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("ProductName")
' rst.ProductVersion = itm.UserProperties("ProductVersion")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
' End If
' Next
'
' rst.Close
' MsgBox ItemCount & "All OSR's exported!"

End Sub

Bill Billmire -

The only thing in the UserProperties collection are individual
UserProperty
items. Either an AssignedTo property is there or it's not. You'll have to
try to get it in order to find out:

Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
' so do something else
End If

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Thanks Sue,

What I need to do is test the name of the field... is it "Assigned To"
or
is
it "AssignedTo"; [without the space], depending on whether this is true
or
false the correct export code then gets executed. For this test, I
don't
care what the contents of the field is, only it's name. Is the "Field
Name"
in the UserProperties collection?

Thanks,

Bill Billmire -

:

I'd just write a few lines of code to iterate the UserProperties
collection.

 

[Bill Billmire]

Thanks Sue!

Sorry for the confusion... The situation was, after updating the Outlook
Form for the Bookmarks (and their constrains, no spaces, etc.). And my
desire to export the cumulative data to something [more overall manageable]
like an Access DB. I found that the (I guess the correct term is: "Property
Tags") changed and what I was exporting for the older records was "null" data
for the fields I changed as a result of the MSWord (Bookmark) constraints. I
am reading the CDO primer now...

Thanks,

Bill Billmire -

The point of the sample was to illustrate how to determine whether a
particular custom property exists. You need to provide a statement somewhere
to instantiate an object variable representing the item you want to work
with. I had no way of knowing what item that might be. Since your code below
is working with a For Each loop that assigns itm as the object variable,
that's the variable you need to be using with UserProperties.

What I want to find, is the name of the control (which
is either AssignedTo or Assigned To).

When did controls get into this? I thought we were talking about custom
properties. Controls are a different matter altogether.

See http://www.cdolive.com/cdo10.htm for a primer on MAPI property tags. The
Redemption web site has additional information along the same lines.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Hi Sue!

I tried your sample code, but I cannot get to work. (error: Object
required: 'objItem'). What I want to find, is the name of the control
(which
is either AssignedTo or Assigned To). If I look in OutlookSpy, in one of
the
posts, and click on IMessage, the Property Tag is: 0x8209 Assigned To, so
I
know it is there... How do I get that "Property Tag" information?

Looking at your book... Page 368 (Referring to Outlook form controls)
seems
similar to what [I think] I want to do... but... I am still not able to
pull
that Control/Property Tag data.

Current Code Below:

Option Explicit

'---------------Exporting-----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim objProp
Dim objPage
Dim ItemCount
Dim objItem
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If

For Each itm in itms
Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
End If
stop
Next

'---the balance is commented out until I can find the control name
"Assigned
To"----
' For Each itm in itms
' rst.AddNew
'
' If itm.UserProperties(Assigned To) = "Assigned To" then
'
' 'Export the following items if "Assigned To"
' rst.AssignedTo = itm.UserProperties("Assigned To")
' rst.ClosedBy = itm.UserProperties("Closed By")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("Problem Priority")
' rst.OSRStatus = itm.UserProperties("Problem Status")
' rst.PhoneExtension = itm.UserProperties("Phone Extension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("Product Name")
' rst.ProductVersion = itm.UserProperties("Product Version")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
'
' Else
'
' 'Export the following items if "AssignedTo"
' rst.AssignedTo = itm.UserProperties("AssignedTo")
' rst.ClosedBy = itm.UserProperties("ClosedBy")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("OSRPriority")
' rst.OSRStatus = itm.UserProperties("OSRStatus")
' rst.PhoneExtension = itm.UserProperties("PhoneExtension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("ProductName")
' rst.ProductVersion = itm.UserProperties("ProductVersion")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
' End If
' Next
'
' rst.Close
' MsgBox ItemCount & "All OSR's exported!"

End Sub

Bill Billmire -

The only thing in the UserProperties collection are individual
UserProperty
items. Either an AssignedTo property is there or it's not. You'll have to
try to get it in order to find out:

Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
' so do something else
End If

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


Thanks Sue,

What I need to do is test the name of the field... is it "Assigned To"
or
is
it "AssignedTo"; [without the space], depending on whether this is true
or
false the correct export code then gets executed. For this test, I
don't
care what the contents of the field is, only it's name. Is the "Field
Name"
in the UserProperties collection?

Thanks,

Bill Billmire -

:

I'd just write a few lines of code to iterate the UserProperties
collection.

 

[Sue Mosher [MVP-Outlook]]

You don't need and really shouldn't go to the trouble of using CDO to work
with Outlook custom properties. Did you try what I suggested to check for
the presence of a particular property?

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Thanks Sue!

Sorry for the confusion... The situation was, after updating the Outlook
Form for the Bookmarks (and their constrains, no spaces, etc.). And my
desire to export the cumulative data to something [more overall
manageable]
like an Access DB. I found that the (I guess the correct term is:
"Property
Tags") changed and what I was exporting for the older records was "null"
data
for the fields I changed as a result of the MSWord (Bookmark) constraints.
I
am reading the CDO primer now...

Thanks,

Bill Billmire -

The point of the sample was to illustrate how to determine whether a
particular custom property exists. You need to provide a statement
somewhere
to instantiate an object variable representing the item you want to work
with. I had no way of knowing what item that might be. Since your code
below
is working with a For Each loop that assigns itm as the object variable,
that's the variable you need to be using with UserProperties.

What I want to find, is the name of the control (which
is either AssignedTo or Assigned To).

When did controls get into this? I thought we were talking about custom
properties. Controls are a different matter altogether.

See http://www.cdolive.com/cdo10.htm for a primer on MAPI property tags.
The
Redemption web site has additional information along the same lines.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Hi Sue!

I tried your sample code, but I cannot get to work. (error: Object
required: 'objItem'). What I want to find, is the name of the control
(which
is either AssignedTo or Assigned To). If I look in OutlookSpy, in one
of
the
posts, and click on IMessage, the Property Tag is: 0x8209 Assigned To,
so
I
know it is there... How do I get that "Property Tag" information?

Looking at your book... Page 368 (Referring to Outlook form controls)
seems
similar to what [I think] I want to do... but... I am still not able
to
pull
that Control/Property Tag data.

Current Code Below:

Option Explicit

'---------------Exporting-----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim objProp
Dim objPage
Dim ItemCount
Dim objItem
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If

For Each itm in itms
Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
End If
stop
Next

'---the balance is commented out until I can find the control name
"Assigned
To"----
' For Each itm in itms
' rst.AddNew
'
' If itm.UserProperties(Assigned To) = "Assigned To" then
'
' 'Export the following items if "Assigned To"
' rst.AssignedTo = itm.UserProperties("Assigned To")
' rst.ClosedBy = itm.UserProperties("Closed By")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("Problem Priority")
' rst.OSRStatus = itm.UserProperties("Problem Status")
' rst.PhoneExtension = itm.UserProperties("Phone Extension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("Product Name")
' rst.ProductVersion = itm.UserProperties("Product Version")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
'
' Else
'
' 'Export the following items if "AssignedTo"
' rst.AssignedTo = itm.UserProperties("AssignedTo")
' rst.ClosedBy = itm.UserProperties("ClosedBy")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("OSRPriority")
' rst.OSRStatus = itm.UserProperties("OSRStatus")
' rst.PhoneExtension = itm.UserProperties("PhoneExtension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("ProductName")
' rst.ProductVersion = itm.UserProperties("ProductVersion")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
' End If
' Next
'
' rst.Close
' MsgBox ItemCount & "All OSR's exported!"

End Sub

Bill Billmire -

:

The only thing in the UserProperties collection are individual
UserProperty
items. Either an AssignedTo property is there or it's not. You'll have
to
try to get it in order to find out:

Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
' so do something else
End If

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


message
Thanks Sue,

What I need to do is test the name of the field... is it "Assigned
To"
or
is
it "AssignedTo"; [without the space], depending on whether this is
true
or
false the correct export code then gets executed. For this test, I
don't
care what the contents of the field is, only it's name. Is the
"Field
Name"
in the UserProperties collection?

Thanks,

Bill Billmire -

:

I'd just write a few lines of code to iterate the UserProperties
collection.

 

[Bill Billmire]

Sue,

Yes, I did... What I got back was the properties for the new/upgraded
Outlook Form. Not the older one... So if I point the "PickFolder" dialog
(in the Export Routine) to the folder containing records older than 1/11/2005
(the date I changed the form in production). The fields that changed, as a
result of Words (Bookmark Constraints), now point to different "Property
Tags" and return nothing (null). I can verify that the data still resides in
the records by using "OutlookSpy" and viewing the IMessage data presented
after selecting an "older record" and the Old Property Tags are defined as
they originally were... Is there another way to get to that older data
(resident in Property Tags) than using CDO?

Bill Billmire -

You don't need and really shouldn't go to the trouble of using CDO to work
with Outlook custom properties. Did you try what I suggested to check for
the presence of a particular property?

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Thanks Sue!

Sorry for the confusion... The situation was, after updating the Outlook
Form for the Bookmarks (and their constrains, no spaces, etc.). And my
desire to export the cumulative data to something [more overall
manageable]
like an Access DB. I found that the (I guess the correct term is:
"Property
Tags") changed and what I was exporting for the older records was "null"
data
for the fields I changed as a result of the MSWord (Bookmark) constraints.
I
am reading the CDO primer now...

Thanks,

Bill Billmire -

The point of the sample was to illustrate how to determine whether a
particular custom property exists. You need to provide a statement
somewhere
to instantiate an object variable representing the item you want to work
with. I had no way of knowing what item that might be. Since your code
below
is working with a For Each loop that assigns itm as the object variable,
that's the variable you need to be using with UserProperties.

What I want to find, is the name of the control (which
is either AssignedTo or Assigned To).

When did controls get into this? I thought we were talking about custom
properties. Controls are a different matter altogether.

See http://www.cdolive.com/cdo10.htm for a primer on MAPI property tags.
The
Redemption web site has additional information along the same lines.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Hi Sue!

I tried your sample code, but I cannot get to work. (error: Object
required: 'objItem'). What I want to find, is the name of the control
(which
is either AssignedTo or Assigned To). If I look in OutlookSpy, in one
of
the
posts, and click on IMessage, the Property Tag is: 0x8209 Assigned To,
so
I
know it is there... How do I get that "Property Tag" information?

Looking at your book... Page 368 (Referring to Outlook form controls)
seems
similar to what [I think] I want to do... but... I am still not able
to
pull
that Control/Property Tag data.

Current Code Below:

Option Explicit

'---------------Exporting-----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim objProp
Dim objPage
Dim ItemCount
Dim objItem
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If

For Each itm in itms
Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
End If
stop
Next

'---the balance is commented out until I can find the control name
"Assigned
To"----
' For Each itm in itms
' rst.AddNew
'
' If itm.UserProperties(Assigned To) = "Assigned To" then
'
' 'Export the following items if "Assigned To"
' rst.AssignedTo = itm.UserProperties("Assigned To")
' rst.ClosedBy = itm.UserProperties("Closed By")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("Problem Priority")
' rst.OSRStatus = itm.UserProperties("Problem Status")
' rst.PhoneExtension = itm.UserProperties("Phone Extension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("Product Name")
' rst.ProductVersion = itm.UserProperties("Product Version")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
'
' Else
'
' 'Export the following items if "AssignedTo"
' rst.AssignedTo = itm.UserProperties("AssignedTo")
' rst.ClosedBy = itm.UserProperties("ClosedBy")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("OSRPriority")
' rst.OSRStatus = itm.UserProperties("OSRStatus")
' rst.PhoneExtension = itm.UserProperties("PhoneExtension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("ProductName")
' rst.ProductVersion = itm.UserProperties("ProductVersion")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
' End If
' Next
'
' rst.Close
' MsgBox ItemCount & "All OSR's exported!"

End Sub

Bill Billmire -

:

The only thing in the UserProperties collection are individual
UserProperty
items. Either an AssignedTo property is there or it's not. You'll have
to
try to get it in order to find out:

Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
' so do something else
End If

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


message
Thanks Sue,

What I need to do is test the name of the field... is it "Assigned
To"
or
is
it "AssignedTo"; [without the space], depending on whether this is
true
or
false the correct export code then gets executed. For this test, I
don't
care what the contents of the field is, only it's name. Is the
"Field
Name"
in the UserProperties collection?

Thanks,

Bill Billmire -

:

I'd just write a few lines of code to iterate the UserProperties
collection.

 

[Bill Billmire]

Sue,

I modified the "Export Data Form" to include the fields from both sets of
data (old and new). I also modified the logic to use the date (instead of
the field change). Now the code correctly selects the correct data (old vs.
new) when starting the loop, and exports the data into access... With the
exception of the changed field names -- those still result in "null" data.
Based on the code below... Do you see anything that might prevent the "old"
data from exporting??? This might go back to needing to use CDO to get the
data from the "property tags"?

Code Below:

Option Explicit

'---------------Exporting-----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim objProp
Dim objDate
Dim objPage
Dim ItemCount
Dim strKeyDate
Dim MyDate
Dim MyComp
Dim objItem
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If
strKeyDate = "1/11/2005"
For Each itm in itms
objDate = Itm.SentOn
MyDate = Left(CStr(objDate),9)
' Msgbox Left(CStr(objDate),9)
MyComp = StrComp(strKeyDate, MyDate, 1)
If MyComp = -1 then
MsgBox "Exporting OLDER Items"
'stop
'Exit For
rst.AddNew

'Export the following items if "Assigned To"
rst.AssignedTo = itm.UserProperties("Assigned To")
rst.ClosedBy = itm.UserProperties("Closed By")
rst.DepartmentName = itm.UserProperties("DepartmentName")
rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
rst.FullName = itm.UserProperties("FullName")
rst.ITComments = itm.UserProperties("MISComments")
stop
Exit For
rst.OSRPriority = itm.UserProperties("Problem Priority")
rst.OSRStatus = itm.UserProperties("Problem Status")
rst.PhoneExtension = itm.UserProperties("Phone Extension")
rst.ProblemDescription = itm.UserProperties("ProblemDescription")
rst.ProductName = itm.UserProperties("Product Name")
rst.ProductVersion = itm.UserProperties("Product Version")
rst.Sent = CStr(itm.SentOn)
rst.TicketID = itm.UserProperties("TicketID")
rst.Update

Else
'MsgBox "Exporting NEWER Items"
'Stop
'Exit For
rst.AddNew

'Export the following items if "AssignedTo"
rst.AssignedTo = itm.UserProperties("AssignedTo")
rst.ClosedBy = itm.UserProperties("ClosedBy")
rst.DepartmentName = itm.UserProperties("DepartmentName")
rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
rst.FullName = itm.UserProperties("FullName")
rst.ITComments = itm.UserProperties("ITComments")
rst.OSRPriority = itm.UserProperties("OSRPriority")
rst.OSRStatus = itm.UserProperties("OSRStatus")
rst.PhoneExtension = itm.UserProperties("PhoneExtension")
rst.ProblemDescription = itm.UserProperties("ProblemDescription")
rst.ProductName = itm.UserProperties("ProductName")
rst.ProductVersion = itm.UserProperties("ProductVersion")
rst.Sent = CStr(itm.SentOn)
rst.TicketID = itm.UserProperties("TicketID")
rst.Update
End If
Next

rst.Close
MsgBox ItemCount & "All OSR's exported!"

End Sub

'----Open and Inspect Ticket Data----

Sub cmdInspect_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Set up reference to Outlook folder of items to Inspect
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to Inspect"
End If
' This would be where I would select a specific item from the folder
' and read it into the form with all fields (old & new) and inspect
' where and how the data is displayed in the appropriate corresponding
field...

End Sub

Bill Billmire -

If you add that property to the custom form (NOTE: not to the folder -- see
http://www.outlookcode.com/d/fields.htm), then republish the form, you
should be able to access the data through the UserProperties collection. If
that doesn't work, you'll need to use CDO or Redemption.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Sue,

Yes, I did... What I got back was the properties for the new/upgraded
Outlook Form. Not the older one... So if I point the "PickFolder" dialog
(in the Export Routine) to the folder containing records older than
1/11/2005
(the date I changed the form in production). The fields that changed, as
a
result of Words (Bookmark Constraints), now point to different "Property
Tags" and return nothing (null). I can verify that the data still resides
in
the records by using "OutlookSpy" and viewing the IMessage data presented
after selecting an "older record" and the Old Property Tags are defined as
they originally were... Is there another way to get to that older data
(resident in Property Tags) than using CDO?

Bill Billmire -

You don't need and really shouldn't go to the trouble of using CDO to
work
with Outlook custom properties. Did you try what I suggested to check for
the presence of a particular property?

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



Thanks Sue!

Sorry for the confusion... The situation was, after updating the
Outlook
Form for the Bookmarks (and their constrains, no spaces, etc.). And my
desire to export the cumulative data to something [more overall
manageable]
like an Access DB. I found that the (I guess the correct term is:
"Property
Tags") changed and what I was exporting for the older records was
"null"
data
for the fields I changed as a result of the MSWord (Bookmark)
constraints.
I
am reading the CDO primer now...

Thanks,

Bill Billmire -

:

The point of the sample was to illustrate how to determine whether a
particular custom property exists. You need to provide a statement
somewhere
to instantiate an object variable representing the item you want to
work
with. I had no way of knowing what item that might be. Since your code
below
is working with a For Each loop that assigns itm as the object
variable,
that's the variable you need to be using with UserProperties.

What I want to find, is the name of the control (which
is either AssignedTo or Assigned To).

When did controls get into this? I thought we were talking about
custom
properties. Controls are a different matter altogether.

See http://www.cdolive.com/cdo10.htm for a primer on MAPI property
tags.
The
Redemption web site has additional information along the same lines.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers



message
Hi Sue!

I tried your sample code, but I cannot get to work. (error: Object
required: 'objItem'). What I want to find, is the name of the
control
(which
is either AssignedTo or Assigned To). If I look in OutlookSpy, in
one
of
the
posts, and click on IMessage, the Property Tag is: 0x8209 Assigned
To,
so
I
know it is there... How do I get that "Property Tag" information?

Looking at your book... Page 368 (Referring to Outlook form
controls)
seems
similar to what [I think] I want to do... but... I am still not
able
to
pull
that Control/Property Tag data.

Current Code Below:

Option Explicit

'---------------Exporting-----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim objProp
Dim objPage
Dim ItemCount
Dim objItem
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd
function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If

For Each itm in itms
Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
End If
stop
Next

'---the balance is commented out until I can find the control name
"Assigned
To"----
' For Each itm in itms
' rst.AddNew
'
' If itm.UserProperties(Assigned To) = "Assigned To" then
'
' 'Export the following items if "Assigned To"
' rst.AssignedTo = itm.UserProperties("Assigned To")
' rst.ClosedBy = itm.UserProperties("Closed By")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("Problem Priority")
' rst.OSRStatus = itm.UserProperties("Problem Status")
' rst.PhoneExtension = itm.UserProperties("Phone Extension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("Product Name")
' rst.ProductVersion = itm.UserProperties("Product Version")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
'
' Else
'
' 'Export the following items if "AssignedTo"
' rst.AssignedTo = itm.UserProperties("AssignedTo")
' rst.ClosedBy = itm.UserProperties("ClosedBy")
' rst.DepartmentName = itm.UserProperties("DepartmentName")
' rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
' rst.FullName = itm.UserProperties("FullName")
' rst.ITComments = itm.UserProperties("ITComments")
' rst.OSRPriority = itm.UserProperties("OSRPriority")
' rst.OSRStatus = itm.UserProperties("OSRStatus")
' rst.PhoneExtension = itm.UserProperties("PhoneExtension")
' rst.ProblemDescription = itm.UserProperties("ProblemDescription")
' rst.ProductName = itm.UserProperties("ProductName")
' rst.ProductVersion = itm.UserProperties("ProductVersion")
' rst.Sent = CStr(itm.SentOn)
' rst.TicketID = itm.UserProperties("TicketID")
' rst.Update
' End If
' Next
'
' rst.Close
' MsgBox ItemCount & "All OSR's exported!"

End Sub

Bill Billmire -

:

The only thing in the UserProperties collection are individual
UserProperty
items. Either an AssignedTo property is there or it's not. You'll
have
to
try to get it in order to find out:

Set objProp = objItem.UserProperties("AssignedTo")
If objProp Is Nothing Then
MsgBox "No AssignedTo property"
' so do something else
End If

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers


message
Thanks Sue,

What I need to do is test the name of the field... is it
"Assigned
To"
or
is
it "AssignedTo"; [without the space], depending on whether this
is
true
or
false the correct export code then gets executed. For this test,
I
don't
care what the contents of the field is, only it's name. Is the
"Field
Name"
in the UserProperties collection?

Thanks,

Bill Billmire -

:

I'd just write a few lines of code to iterate the UserProperties
collection.

 

[Sue Mosher [MVP-Outlook]]

I guess you're going to need to use CDO or Redemption to get the data out.
I've never seen items in such a mess.

How did you modify the form? Did you take pains to make sure the fields are
in the form definition itself? -- check the All Fields page on the form.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Bill Billmire]

Sue,

Question... Do I need to publish this (Export Data Form) in the
Organizational Forms Library? Right now I've got it in my Personal Forms
Library, or does it make a difference in this case?

Thanks,

Bill Billmire -

 

[Sue Mosher [MVP-Outlook]]

If it's only in your Personal Forms library, only you can make full use of
it.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Bill Billmire]

Sue,

Thats OK... Once I export this data to MSAccess this utility will most
likely be archived and maybe reused as a templete for exporting other data
for another project. Do you have knowledge and insight into using CDO and
"Property Tags" to access Outlook data, If I have questions or run into
scenarios that I need assistance with?

Also, from the code at the bottom; from the point of (Open and Inspect
Ticket Data). I was trying to read in the data to the form for visual
display and testing. Beyond the ".PickFolder" function, is there a
".PickItem" function or do I need to "loop" through each item to review the
data?

Thanks,

Bill Billmire -

If it's only in your Personal Forms library, only you can make full use of
it.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

Sue,

I modified the "Export Data Form" to include the fields from both sets of
data (old and new). I also modified the logic to use the date (instead of
the field change). Now the code correctly selects the correct data (old vs.
new) when starting the loop, and exports the data into access... With the
exception of the changed field names -- those still result in "null" data.
Based on the code below... Do you see anything that might prevent the "old"
data from exporting??? This might go back to needing to use CDO to get the
data from the "property tags"?

Code Below:

Option Explicit

'---------------Exporting-----------------

Dim rst
Dim dbe
Dim wks
Dim dbs
Dim nms
Dim fld
Dim itm
Dim itms
Dim objMark1
Dim objProp
Dim objDate
Dim objPage
Dim ItemCount
Dim strKeyDate
Dim MyDate
Dim MyComp
Dim objItem
Dim objNS 'as NameSpace
Dim objFolder 'as MAPIFolder
Dim Namespace
Dim strAccessPath
Dim appAccess
Dim strFolder
Dim strDBEngine
Dim strDBName
Dim fFound

Sub cmdExport_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = Item.Application.CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "OSR.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = Item.Application.CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "OSR.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("tblOSRData")

'Set up reference to Outlook folder of items to export
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to export"
End If
strKeyDate = "1/11/2005"
For Each itm in itms
objDate = Itm.SentOn
MyDate = Left(CStr(objDate),9)
' Msgbox Left(CStr(objDate),9)
MyComp = StrComp(strKeyDate, MyDate, 1)
If MyComp = -1 then
MsgBox "Exporting OLDER Items"
'stop
'Exit For
rst.AddNew

'Export the following items if dated before 1/11/2005
rst.AssignedTo = itm.UserProperties("Assigned To")
rst.ClosedBy = itm.UserProperties("Closed By")
rst.DepartmentName = itm.UserProperties("DepartmentName")
rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
rst.FullName = itm.UserProperties("FullName")
rst.ITComments = itm.UserProperties("MISComments")
stop
Exit For
rst.OSRPriority = itm.UserProperties("Problem Priority")
rst.OSRStatus = itm.UserProperties("Problem Status")
rst.PhoneExtension = itm.UserProperties("Phone Extension")
rst.ProblemDescription = itm.UserProperties("ProblemDescription")
rst.ProductName = itm.UserProperties("Product Name")
rst.ProductVersion = itm.UserProperties("Product Version")
rst.Sent = CStr(itm.SentOn)
rst.TicketID = itm.UserProperties("TicketID")
rst.Update

Else
'MsgBox "Exporting NEWER Items"
'Stop
'Exit For
rst.AddNew

'Export the following items if date after 1/11/2005
rst.AssignedTo = itm.UserProperties("AssignedTo")
rst.ClosedBy = itm.UserProperties("ClosedBy")
rst.DepartmentName = itm.UserProperties("DepartmentName")
rst.DepartmentNumber = itm.UserProperties("DepartmentNumber")
rst.FullName = itm.UserProperties("FullName")
rst.ITComments = itm.UserProperties("ITComments")
rst.OSRPriority = itm.UserProperties("OSRPriority")
rst.OSRStatus = itm.UserProperties("OSRStatus")
rst.PhoneExtension = itm.UserProperties("PhoneExtension")
rst.ProblemDescription = itm.UserProperties("ProblemDescription")
rst.ProductName = itm.UserProperties("ProductName")
rst.ProductVersion = itm.UserProperties("ProductVersion")
rst.Sent = CStr(itm.SentOn)
rst.TicketID = itm.UserProperties("TicketID")
rst.Update
End If
Next

rst.Close
MsgBox ItemCount & "All OSR's exported!"

End Sub

'----Open and Inspect Ticket Data----

Sub cmdInspect_Click()

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.PickFolder

'Set up reference to Outlook folder of items to Inspect
Set itms = objFolder.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No OSR's to export"
Exit Sub
Else
MsgBox ItemCount & " OSR's to Inspect"
End If
For Each itm in itms

Assigned To = itm.UserProperties("Assigned To")
Closed By = itm.UserProperties("Closed By")
DepartmentName = itm.UserProperties("DepartmentName")
DepartmentNumber = itm.UserProperties("DepartmentNumber")
FullName = itm.UserProperties("FullName")
MISComments = itm.UserProperties("MISComments")
Problem Priority = itm.UserProperties("Problem Priority")
Problem Status = itm.UserProperties("Problem Status")
Phone Extension = itm.UserProperties("Phone Extension")
ProblemDescription = itm.UserProperties("ProblemDescription")
Product Name = itm.UserProperties("Product Name")
Product Version = itm.UserProperties("Product Version")
Sent = CStr(itm.SentOn)
TicketID = itm.UserProperties("TicketID")
stop
exit for
Next
End Sub

 

[Sue Mosher [MVP-Outlook]]

Do you have knowledge and insight into using CDO and

"Property Tags" to access Outlook data, If I have questions or run into
scenarios that I need assistance with?

Sure. Pretty much every Outlook developer has to know how to work with MAPI
property tags. There too many things that can only be done that way.

Also, from the code at the bottom; from the point of (Open and Inspect
Ticket Data). I was trying to read in the data to the form for visual
display and testing. Beyond the ".PickFolder" function, is there a
".PickItem" function or do I need to "loop" through each item to review
the
data?

It really depends on your application, but Outlook provides no mechanism
similar to PickFolder for prompting the user to select an individual item.
Your application, of course, could read it into a list box or other control
and take it from there.