Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-20
6
Medium Priority
?
499 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 2000 total points
ID: 11882603
Any progress on this on?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
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 …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

688 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