Solved

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

Posted on 2004-08-19
9
216 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
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 …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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