Solved

Cannot successfully use VBA code to create a new custom form item in a public folder

Posted on 2004-08-19
9
218 Views
Last Modified: 2010-04-08
Hello.  I am having trouble with VBA code I want to use to import CSV data into a custom form in an Exchange public folder.  What happens is that the code runs, but creates blank new items in the public folder.  When stepping through the run, I can see that the array is being populated correctly, but it seems that the "objItem" is not creating anything to accept the values for the userpropertiy fields. Specifically, the objItem equates to "nothing", which I think might be the problem.  If someone could look at my code and see if anything looks incorrect, I would be grateful.  Here's the code:

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


By the way - the "Safety Incident Report" form is published to the Organizational Forms, as well as copied to the public folder.


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
  • 4
9 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11846097
What if you created the form in a private folder, then after saving it copied it to the public folder?
0
 

Author Comment

by:Damian_Gardner
ID: 11846221
That would work, but it is a post form, not a message.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11846350
Hmm... Not sure what to suggest... I have not worked with post forms.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Damian_Gardner
ID: 11846395
Ok.  Does the syntax look accurate to you, as far as the Set objItem line?  Any reason you can think of why it would equate to "Nothing"?

Thanks
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11846446
I really don't know. I've always usd the .CreateItem method for creating new outlook items, but that was for emails and contacts, so when it comes to a post I'm not sure. You could try changing it to:

    Set objItem = objReports.CreateItem("IPM.Post.Safety Incident Report")

and see if it makes a difference
0
 

Author Comment

by:Damian_Gardner
ID: 11850934
Didn't work.  Thanks for the suggestion though.
0
 

Author Comment

by:Damian_Gardner
ID: 11854428
By the way - is this question dead, or will someone else possibly respond?

Thanks
0
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 250 total points
ID: 11854522
I wouldn't say it is dead, but a little old and  if you really want more participation you should probably post a pointer question so that it once again shows up on the front of the list.

Sorry I could not help.

God bless!
0
 

Author Comment

by:Damian_Gardner
ID: 11854543
Will do.

Thanks much.
0

Featured Post

Industry Leaders: 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

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
Read this checklist to learn more about the 15 things you should never include in an email signature.
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 …
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…

749 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