Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-08-20
6
Medium Priority
?
501 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
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
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…
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…

876 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