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
Solved

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

Posted on 2004-08-19
9
217 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
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

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.

Question has a verified solution.

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

Outlook Free & Paid Tools
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

809 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