• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

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

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
Damian_Gardner
Asked:
Damian_Gardner
  • 5
1 Solution
 
will_scarlet7Commented:
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
 
will_scarlet7Commented:
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
 
will_scarlet7Commented:
Any progress on this on?
0
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 
Damian_GardnerAuthor Commented:
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
 
will_scarlet7Commented:
So the above modification had no effect?
0
 
will_scarlet7Commented:
If this did not help you should ask the moderator to close the question and refund your points.

God bless!

Sam
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now