Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-08-19
9
Medium Priority
?
223 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 750 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Changing a few Outlook Options can help keep you organized!
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

610 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