Solved

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

Posted on 2004-08-20
6
486 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
  • 5
6 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
Comment Utility
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
Comment Utility
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
Comment Utility
Any progress on this on?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Damian_Gardner
Comment Utility
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
Comment Utility
So the above modification had no effect?
0
 
LVL 15

Expert Comment

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

God bless!

Sam
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Use email signature images to promote corporate certifications and industry awards.
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
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: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now