Solved

VBA code is failing to import CSV data into a custom post form

Posted on 2004-08-20
6
496 Views
Last Modified: 2010-04-08
Hello.  I am having trouble with importing records from CSV file into a custom post form in Exchange 5.5.  The code compiles with no errors, but only produces blank custom form entries.  The code is supposed to import an array into UserProperty custom fields.  During the run, when I step through it, I can see that the objItem is equating to "NOTHING", as well are the custom fields, which explains the blank form.  I can't tell if the problem is in the item creation stage, or if it's a problem with the fields.  I'm hoping someone can shed light on this code.  Here it is:

Sub ImportProjectCSVToOutlook()
    Dim objApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objReports As MAPIFolder
    Dim objItem As Outlook.PostItem
    Dim fso As Scripting.FileSystemObject
    Dim objStream As Scripting.TextStream
    Dim strFileName As String
    Dim strLine As String
    Dim arr() As String
   
    strFileName = _
      InputBox("File to import:", "Import to Outlook")
    If strFileName <> "" Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        If fso.FileExists(strFileName) Then
            Set objStream = fso.OpenTextFile(strFileName, ForReading)
            Set objApp = CreateObject("Outlook.Application")
            Set objNS = objApp.GetNamespace("MAPI")
            Set objReports = objNS.PickFolder
            Do Until objStream.AtEndOfStream
                strLine = objStream.ReadLine
                arr = Split(strLine, ",")
                Set objItem = objReports.Items.Add("IPM.Post.Safety Incident Report")
                With objItem
                    .UserProperties("dateincident") = arr(0)
                    .UserProperties("timeincident") = arr(1)
                    .UserProperties("date:") = arr(2)
                    .UserProperties("txtinjuredemployee") = arr(3)
                    .UserProperties("txteyewitnesses") = arr(4)
                    .UserProperties("txtmanagername") = arr(5)
                    .UserProperties("dept.") = arr(6)
                    .UserProperties("txtlocation") = arr(7)
                    .UserProperties("txtdescriptionofincident") = arr(8)
                    .UserProperties("txtunsafeacts1") = arr(9)
                    .UserProperties("txtunsafeacts2") = arr(10)
                    .UserProperties("txtunsafeacts3") = arr(11)
                    .UserProperties("txtunsafecond1") = arr(12)
                    .UserProperties("txtunsafecond2") = arr(13)
                    .UserProperties("txtunsafecond3") = arr(14)
                    .UserProperties("txtpreviousinjuries") = arr(15)
                    .UserProperties("txtphysicaldisabilities") = arr(16)
                    .UserProperties("txtlenghtemployment") = arr(17)
                    .UserProperties("txtmedicaltreatment") = arr(18)
                    .UserProperties("txtoshareportable") = arr(19)
                    .UserProperties("txtlta") = arr(20)
                    .UserProperties("txtfiledby") = arr(21)
                    .UserProperties("txtrootcause") = arr(22)
                    .UserProperties("txtcorrectiveaction") = arr(23)
                    .UserProperties("txtresponsibleperson") = arr(24)
                    .UserProperties("datecompletion") = arr(25)
                    .UserProperties("txtadditionalcomments") = arr(26)
                    .Save
                End With
            Loop
            objStream.Close
        End If
    End If
   
    Set objNS = Nothing
    Set objContacts = Nothing
    Set objContact = Nothing
    Set objApp = Nothing
    Set objStream = Nothing
    Set fso = Nothing
End Sub


The form is named "Safety Incident Report", and is published in the Organization and public folders.

Thanks for any help.
Damian
0
Comment
Question by:Damian_Gardner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11858206
Damian,
    It's me again... I hope someone else gets involved, but I decided to do some testing of your code, and will share my findings with you. First off I do not have Exchange server so I did not create the post in a public folder but a private one in my PST. I also do not have a copy of your form, but I thought that might be useful for debugging as it should throw me some errors (or so I thought). Below are some notes on what happened:

    1.) It did not throw an error on not finding the custom form but rather created a new item of the standard Outlook Post. So I am thinking your set object line is working properly in that it is creating an item, however the ("IPM.Post.Safety Incident Report") may not be calling your custom form properly. To verify if it is creating the correct form on your computer put a break point on the "Loop" line and when it pauses type ".display" in the imediate window. This should open the post item that you just created an you can see if it is the correct form.
    2.) It doesn't throw an error when writing to a custom field that does not exist. I don't know why that is (perhaps someone else here can explain it) but as I said I do not have your form so it created a blank standard Outlook Post when I ran your exact code. TO make sure it was wtitng the fields I inserted the following lines into the "With objItem" block:
        .Body = arr(1)
        .Subject = arr(2)

    3.) You said above "During the run, when I step through it, I can see that the objItem is equating to "NOTHING", as well are the custom fields". When I ran the code as I mentioned I set a break point on the "Loop" line and then when I queried the value of the fields or objItem I got "". This is normal as you are not setting the subject field of the post, and apparently Outlook is not finding your custom fields.

That's all for now. Ill test it some more and post any further comments.

God bless!

Sam
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11858296
Here is a suggestion that I came across while looking into creating a custom form:
'*****************************
Sub ImportProjectCSVToOutlook()
    Dim objApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objReports As MAPIFolder
    Dim objItem As Outlook.PostItem
    Dim objProp As Object
    Dim fso As Scripting.FileSystemObject
    Dim objStream As Scripting.TextStream
    Dim strFileName As String
    Dim strLine As String
    Dim arr() As String
   
    strFileName = _
      InputBox("File to import:", "Import to Outlook")
    If strFileName <> "" Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        If fso.FileExists(strFileName) Then
            Set objStream = fso.OpenTextFile(strFileName, ForReading)
            Set objApp = CreateObject("Outlook.Application")
            Set objNS = objApp.GetNamespace("MAPI")
            Set objReports = objNS.PickFolder
            Do Until objStream.AtEndOfStream
                strLine = objStream.ReadLine
                arr = Split(strLine, ",")
                Set objItem = objReports.Items.Add("IPM.Post.Safety Incident Report")
                    Set usrProp = objItem.UserProperties.Add("dateincident", olDateTime)
                    Set usrProp = objItem.UserProperties.Add("timeincident", olDateTime)
                    Set usrProp = objItem.UserProperties.Add("date:", olDateTime)
                    Set usrProp = objItem.UserProperties.Add("txtinjuredemployee", olText)
                    Set usrProp = objItem.UserProperties.Add("txteyewitnesses", olText)
                    Set usrProp = objItem.UserProperties.Add("txtmanagername", olText)
                    Set usrProp = objItem.UserProperties.Add("dept.", olText)
                    Set usrProp = objItem.UserProperties.Add("txtlocation", olText)
                    Set usrProp = objItem.UserProperties.Add("txtdescriptionofincident", olText)
                    Set usrProp = objItem.UserProperties.Add("txtunsafeacts1", olText)
                    Set usrProp = objItem.UserProperties.Add("txtunsafeacts2", olText)
                    Set usrProp = objItem.UserProperties.Add("txtunsafeacts3", olText)
                    Set usrProp = objItem.UserProperties.Add("txtunsafecond1", olText)
                    Set usrProp = objItem.UserProperties.Add("txtunsafecond2", olText)
                    Set usrProp = objItem.UserProperties.Add("txtunsafecond3", olText)
                    Set usrProp = objItem.UserProperties.Add("txtpreviousinjuries", olText)
                    Set usrProp = objItem.UserProperties.Add("txtphysicaldisabilities", olText)
                    Set usrProp = objItem.UserProperties.Add("txtlenghtemployment", olText)
                    Set usrProp = objItem.UserProperties.Add("txtmedicaltreatment", olText)
                    Set usrProp = objItem.UserProperties.Add("txtoshareportable", olText)
                    Set usrProp = objItem.UserProperties.Add("txtlta", olText)
                    Set usrProp = objItem.UserProperties.Add("txtfiledby", olText)
                    Set usrProp = objItem.UserProperties.Add("txtrootcause", olText)
                    Set usrProp = objItem.UserProperties.Add("txtcorrectiveaction", olText)
                    Set usrProp = objItem.UserProperties.Add("txtresponsibleperson", olText)
                    Set usrProp = objItem.UserProperties.Add("datecompletion", olDateTime)
                    Set usrProp = objItem.UserProperties.Add("txtadditionalcomments", olText)
                With objItem
                    .UserProperties("dateincident") = arr(0)
                    .UserProperties("timeincident") = arr(1)
                    .UserProperties("date:") = arr(2)
                    .UserProperties("txtinjuredemployee") = arr(3)
                    .UserProperties("txteyewitnesses") = arr(4)
                    .UserProperties("txtmanagername") = arr(5)
                    .UserProperties("dept.") = arr(6)
                    .UserProperties("txtlocation") = arr(7)
                    .UserProperties("txtdescriptionofincident") = arr(8)
                    .UserProperties("txtunsafeacts1") = arr(9)
                    .UserProperties("txtunsafeacts2") = arr(10)
                    .UserProperties("txtunsafeacts3") = arr(11)
                    .UserProperties("txtunsafecond1") = arr(12)
                    .UserProperties("txtunsafecond2") = arr(13)
                    .UserProperties("txtunsafecond3") = arr(14)
                    .UserProperties("txtpreviousinjuries") = arr(15)
                    .UserProperties("txtphysicaldisabilities") = arr(16)
                    .UserProperties("txtlenghtemployment") = arr(17)
                    .UserProperties("txtmedicaltreatment") = arr(18)
                    .UserProperties("txtoshareportable") = arr(19)
                    .UserProperties("txtlta") = arr(20)
                    .UserProperties("txtfiledby") = arr(21)
                    .UserProperties("txtrootcause") = arr(22)
                    .UserProperties("txtcorrectiveaction") = arr(23)
                    .UserProperties("txtresponsibleperson") = arr(24)
                    .UserProperties("datecompletion") = arr(25)
                    .UserProperties("txtadditionalcomments") = arr(26)
                    .Save
                End With
            Loop
            objStream.Close
        End If
    End If
   
    Set objNS = Nothing
    Set objContacts = Nothing
    Set objContact = Nothing
    Set objApp = Nothing
    Set objStream = Nothing
    Set fso = Nothing
End Sub
'*****************************
Notice the block where it adds all the "UserProperties" to the newly created item befor going through the with block to set their values. I'm not sure that I got all your data types correct.

God bless!

Sam.
0
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 500 total points
ID: 11882603
Any progress on this on?
0
SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

 

Author Comment

by:Damian_Gardner
ID: 11882667
No.  I actually had to add the records manually, since it didn't seem like it was going to get resolved.  I'm going to close the question out.  I appreciate your efforts Sam.

Thanks,
Damian
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11882716
So the above modification had no effect?
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11882786
If this did not help you should ask the moderator to close the question and refund your points.

God bless!

Sam
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Changing a few Outlook Options can help keep you organized!
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question