Import user-defined fields from Outlook to Access Dynamic import ?

[Guest]

I want to loop through all "User properties".index and use
Name and Value to name the columns in table tblInbox and insert the value in
that column of this table dynamically. I have over 200 user-defined OL fields
to import so there may be too much coding for each item.

So, can I loop in .Index and use .Name and .Value to do this ?

My snippet is below.
Any suggestions.

Thanks in advance
Chuck


My Snippet is as follows:
Sub ImportInboxFromOutlook()
On Error GoTo Error_Handler

Dim strFName, strLName As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblInbox")

Dim MyPos, importcount As Integer

' Set up Outlook objects.
Dim ol As New Outlook.Application
Dim olns As Outlook.NameSpace
Dim cf As Outlook.MAPIFolder
Dim m As Outlook.MailItem
Dim objItems As Outlook.Items
Dim Prop As Outlook.UserProperty
Dim objImportedFolder As MAPIFolder ' Move action
Dim iNumMsgs As Integer


Set olns = ol.GetNamespace("MAPI")
Set cf = olns.GetDefaultFolder(olFolderInbox)
Set objItems = cf.Items
'Screen.MousePointer = 11 'Make hourglass
'On Error GoTo ErrorHandler_Folder 'if the folder is not available then...
Set objImportedFolder = cf.Folders("21st Century") 'Move
action
On Error GoTo Error_Handler 'reset error once past 'folder' check

importcount = 0
iNumMsgs = objItems.Count
' MsgBox "iNumMsgs" & ": " & iNumMsgs, vbOKOnly
If iNumMsgs > 0 Then
For i = 0 To iNumMsgs

If TypeName(objItems(i)) = "MailItem" Then
Set m = objItems(i)
MyPos = InStr(1, m.Subject, "21st Century Grant") 'Is this mail
item for the 21st Century grant
If (MyPos) > 0 Then
rst.AddNew
rst!SenderName = m.SenderName
rst!Subject = m.Subject
rst!SentOn = m.SentOn
rst!To = m.To

' Get user-defined fields
strFName = m.UserProperties("NameFirst")

strLName = m.UserProperties("NameLast")
rst!NameLast = m.UserProperties("NameLast")
rst!StudentID = m.UserProperties("txtStudentID")

 

[John Nurick]

Hi Chuck,

I can't see why you shouldn't do something like

For Each Prop In m.UserProperties
On Error Resume Next
rst.Fields(Prop.Name).Value = Prop.Value
Select Case Err.Number
Case 0
'All is well
Case 3265 'object does not exist in this collection
'Field not found in recordset
Case 3421 'Data type conversion error
'Prop.Value is wrong data type for recordset field
Case Else
'Some other error
End Select
On Error Goto 0
Next 'Prop

 

[Guest]

Thanks for the structure. The rst.Fields(Prop.Name).value is a new structure
to me. I'll give it a try on Wed. coming.
Also, can I import Prop.Name to cteate a new table. Obviously, I will do
this once and then populate this table with form fields. Do you have any
snippets for this table creation piece.

Thanks,
Chuck

 

[John Nurick]

Hi Chuck,

For a one-off I'd probably just do something like this (pseudo code) to
get a textfile containing a list of the field names and types ,
especially as I'm not familiar with the Outlook object model

Dim strFN As "C:\Temp\PropNames.Text"
Dim objM as Outlook.MailItem
Dim objP as Outlook.UserProperty
Open strFN For Output As #1

Set objM = a MailItem containing all the custom properties you want
For Each objP in objM.Properties
Write #1, objP.Name, objP.Type 'and perhaps other stuff
Next

Close #1
Set objM = Nothing

Then I'd open the file in a text editor or word processor, and edit it
(with generous use of search and replace) to create a SQL DDL query that
I could execute to create the table (see "CREATE TABLE statement" in
Access help).