(500pts 4 success) Use vba to modify a user defined field for an Outlook email

Sub DoChanges()
ChangeItem "TempFiled", "NewValue"
End Sub

Sub ChangeItem(StrFieldName As String, StrValue As String)
Dim oFolder As MAPIFolder
Dim oNameSpace As Outlook.NameSpace
Dim oItem As Object
Dim oContact As Outlook.ContactItem
Dim oMailItem As Outlook.MailItem
Dim oProperty As Object
Dim LngNextEmail As Long
Dim oAttachment As Outlook.Attachment
Dim iRec As Long
Set oNameSpace = Outlook.GetNamespace("Mapi")
Set oFolder = oNameSpace.PickFolder
For Each oItem In oFolder.Items
  If oItem.UserProperties(StrFieldName) Is Nothing Then
     oItem.UserProperties.Add StrFieldName, olText
   End If
   oItem.UserProperties(StrFieldName) = "NewValue"
   oItem.Save
Next
End Sub

Great! the 500 points are yours.

Now, since you seem to be pretty good at outlook/vba, perhaps I can trouble you for a few follow up questions.  (If you would like, I would be glad to give you more points for these in a "points forMichaelDS" posting.)
----------------------------
Important question #1.  I'm quite new to this and wonder if you could explain how to make this subroutine available to the whole office.  I think I have to put the vba module onto our server, perhaps at G:\myPrograms\Shared\xxxxx*.*.  Do I then go to everybody's outlook and add the program reference?
----------------------------
Less important question #2.  Currently, my subroutine asks for the data fields with 3 separate inputbox statements.
Can you show me how to get all 3 in a single dialbox with a Cancel/Ok button that defaults to OK.

myComment = InputBox("Enter your comment:", , oItem.UserProperties("mycomment"))
myPublication=InputBox("Enter the publication:", , oItem.UserProperties("mypublication))
myCoworker=InputBox("Enter the publication:", , oItem.UserProperties("mycoworker"))
......... blah blah blah
   oItem.UserProperties("myComment") = myComment
   oItem.UserProperties("myPublication") = myPublication
   oItem.Save
-----------------------------
Least important question #3. When one of these e-mails gets moved to a different folder, the newly defined fields do not show up in Field chooser. Do you know of anyway to solve that minor problem.

I am just stepping out for a bit. It is 5:00 p.m. here. I will post you some code and proposed solutions overnight.

Thanks.

Question on. If you want the code to live in outlook you must put it in each person version, however you could run this form a Small VB routine or even an other container with VBA. I work with a number of consultants and I often package routines within Excel. (Yeah, I know it sounds a bit off the wall, but it works quite well) They can carry a workbook with code for various things and they do not need to do an install, do not need a big coding environment, etc. Practically speaking I would code it in VB

Are you working with VB6? I ask as I can drop a shell of a project to a download location?

The fields are folder dependant and you will need to create the same field in the target folder. Once the field is created the fields will move witht he items if they are move. Thus create the field for all target folders once and you can move the items around with out concern (Just tested this).

Please let me know if posting a VB Routine will work for you?

In the following, I renamed your "ChangeCategory" to be ChangeUserFields.

Also, you should know that I am only installing on 6 other computers, so initial installation is not the issue.  The important thing is that they all use the same code. I don't want to have to got to all 6 computers every time I make a change.

Your excel idea made sense, but I kneed more info. I moved the code to Test.xls, so it disappeared from the Outlook macro's list. I then opened outlook vba and tried to add tools.xls to reference list, but got message "Can't add a reference to the specified file".

What am I missing ?  Perhaps you are suggesting that install a ChangeUserFieldsStub on each of my 6 outlook clients?   this would resolve my need for a single copy of the code.
   sub ChangeUserFieldsStub()
     .... magic code to set xlapp to "TEST.xls"...
      xlapp.run("ChangeCategory")
   end sub


sorry, all I have are the MS Office VBAs. Even worse, that's all I've ever used. I believe the syntax is the same, but the IDE and libraries are better, so perhaps it would be an option, but I'm in a hurry.

By the way, I have also done some testing, and it appears that fields stay with the items in a very robust manner. I set Publication to "NewsweeK" and moved the message to a folder that did not have the field. Then copied it to my desktop, then copied back to a shared folder that did not have the field. With a different machine and user, I used field chooser to create the field on the folder.  Voila! "newsweek" was still there.  Slick!

Also, I think I'll just use a form to enter the three fields.  But, I'll get sick and tired of always creating a dialog box just to enter 3 data fields.  Have you ever run across a subroutine like input box that will ask for arbitrary number of fields.  Something like
   dim s as string, c as string, w as string
   if vbOK = myInputBox (array("enter subscription", "enter comment", "enter coworker"), Array(s, c, w)) then .....

My FTP is over booked and I need to get in and clean it out

Here is the code that make up a form in Excel. You need to create the following

Combo boxes
cboFieldOne
cboFieldTwo
cboFieldThree

TextBoxes
txtFieldOne
txtFieldTwo
txtFieldThree

Lables
lblMessage
lblFiedlOne
LblFieldTwo
LblFieldThree

CommandButtons
cmdProcess
cmdClose

You must also make a reference to Outlook. This can be done from with a module by using the Tool / Reference from the menu

You can also add A module that contains a single function

Code in a module
Option Explicit

Sub OutlookUpdate()
frmOutookFields.Show vbModal
End Sub

Code behind the form

Option Explicit

Dim AFieldNames(0 To 2) As String
Dim aFieldData(0 To 2) As String

Private Sub cmdClose_Click()
Me.Hide
Unload frmOutookFields
Set frmOutookFields = Nothing

End Sub


Private Sub cmdProcess_Click()
Me.lblMessage.Caption = "Processing..."
AFieldNames(0) = Me.cboFieldOne.Text
AFieldNames(1) = Me.cboFieldTwo.Text
AFieldNames(2) = Me.cboFieldThree.Text
aFieldData(0) = Me.txtFieldOne
aFieldData(1) = Me.txtFieldTwo
aFieldData(2) = Me.txtFieldThree
If Len(Me.txtFieldOne) > 0 _
  Or Len(Me.txtFieldTwo) > 0 _
  Or Len(Me.txtFieldThree) > 0 Then
  DoProcess
Else
  MsgBox "Nothing To Do"
End If
Me.lblMessage.Caption = ""
End Sub

Private Sub UserForm_Activate()
Me.lblMessage.Caption = ""
Me.cboFieldOne.Clear
Me.cboFieldOne.AddItem "mycomment"
Me.cboFieldOne.AddItem "mypublication"
Me.cboFieldOne.AddItem "mycoworker"
Me.cboFieldOne.Text = "mycomment"

Me.cboFieldTwo.Clear
Me.cboFieldTwo.AddItem "mycomment"
Me.cboFieldTwo.AddItem "mypublication"
Me.cboFieldTwo.AddItem "mycoworker"
Me.cboFieldTwo.Text = "mypublication"

Me.cboFieldThree.Clear
Me.cboFieldThree.AddItem "mycomment"
Me.cboFieldThree.AddItem "mypublication"
Me.cboFieldThree.AddItem "mycoworker"
Me.cboFieldThree.Text = "mycoworker"

Me.txtFieldOne.SetFocus

End Sub

Sub DoProcess()
Dim oOutlook As Outlook.Application
Dim oFolder As Outlook.MAPIFolder
Dim oItem As Outlook.MailItem
Dim oNameSpace As Outlook.NameSpace
Dim fWeOpenedOutlook As Boolean
Dim oActiveExp As Outlook.Explorer
Dim oSel As Outlook.Selection
Dim i As Long
On Error GoTo ProcError
Set oOutlook = New Outlook.Application
If oOutlook.ActiveExplorer Is Nothing Then
  fWeOpenedOutlook = True
Else
  fWeOpenedOutlook = False
End If
If Not fWeOpenedOutlook Then
  Set oActiveExp = Outlook.ActiveExplorer
  For Each oItem In oActiveExp.Selection
    For i = 0 To 2
      If Len(aFieldData(i)) > 0 Then
        If oItem.UserProperties(AFieldNames(i)) Is Nothing Then
          oItem.UserProperties.Add AFieldNames(i), olText
        End If
      oItem.UserProperties(AFieldNames(i)) = aFieldData(i)
      oItem.Save
      End If
    Next
  Next
Else
 Set oNameSpace = oOutlook.GetNamespace("MAPI")
 Set oFolder = oNameSpace.PickFolder
 If MsgBox("You are about to Process " & oFolder.Items.Count & " Items", vbOKCancel, "Add Fields to folder") = vbOK Then
   For Each oItem In oFolder.Items
     For i = 0 To 2
      If Len(aFieldData(i)) > 0 Then
        If oItem.UserProperties(AFieldNames(i)) Is Nothing Then
          oItem.UserProperties.Add AFieldNames(i), olText
       End If
      oItem.UserProperties(AFieldNames(i)) = aFieldData(i)
      oItem.Save
      End If
    Next
   Next
 End If
End If
Set oItem = Nothing
Set oFolder = Nothing
Set oNameSpace = Nothing
If fWeOpenedOutlook Then
  oOutlook.Quit
End If
Set oOutlook = Nothing

Exit Sub
ProcError:
  Select Case Err
  Case Else
     MsgBox "Error: " & Err & " " & Error, vbInformation, "Do Processing"
     Resume Next
  End Select
End Sub

Thanks. Its a tad more complicated than I had hoped, so it will take me a while to absorb it.

let's drop everything else, and focus on the only important thing left:

Your excel idea made sense, but I need more info. I moved the ChangeUserFields code to Test.xls, so it disappeared from the Outlook macro's list. I then opened outlook vba and tried to add tools.xls to reference list, but got message "Can't add a reference to the specified file".

What am I missing ?  Perhaps you are suggesting that install a ChangeUserFieldsStub on each of my 6 outlook clients?   this would resolve my need for a single copy of the code.
   sub ChangeUserFieldsStub()
     .... magic code to set xlapp to "TEST.xls"...
      xlapp.run("ChangeCategory")
   end sub

by the way, I'll give the points by the end of the day, no matter what!

Sorry for being unclear. The excel package is a bit like a stand alone program.

Lets to this step wise.

Open Excel

Click on Tools/Macor/Visual basic Editor

In the code window clcik on

Insert/UserForm

Insert/Module

Click on the form

Rename it from UserForm to frmOutookFields

Now add the contorls as named


Combo boxes
cboFieldOne
cboFieldTwo
cboFieldThree

TextBoxes
txtFieldOne
txtFieldTwo
txtFieldThree

Lables
lblMessage
lblFiedlOne
LblFieldTwo
LblFieldThree

CommandButtons
cmdProcess
cmdClose

go to the code view of the form and past in the code for the form.


Option Explicit

Dim AFieldNames(0 To 2) As String
Dim aFieldData(0 To 2) As String

Private Sub cmdClose_Click()
Me.Hide
Unload frmOutookFields
Set frmOutookFields = Nothing

End Sub


Private Sub cmdProcess_Click()
Me.lblMessage.Caption = "Processing..."
AFieldNames(0) = Me.cboFieldOne.Text
AFieldNames(1) = Me.cboFieldTwo.Text
AFieldNames(2) = Me.cboFieldThree.Text
aFieldData(0) = Me.txtFieldOne
aFieldData(1) = Me.txtFieldTwo
aFieldData(2) = Me.txtFieldThree
If Len(Me.txtFieldOne) > 0 _
  Or Len(Me.txtFieldTwo) > 0 _
  Or Len(Me.txtFieldThree) > 0 Then
  DoProcess
Else
  MsgBox "Nothing To Do"
End If
Me.lblMessage.Caption = ""
End Sub

Private Sub UserForm_Activate()
Me.lblMessage.Caption = ""
Me.cboFieldOne.Clear
Me.cboFieldOne.AddItem "mycomment"
Me.cboFieldOne.AddItem "mypublication"
Me.cboFieldOne.AddItem "mycoworker"
Me.cboFieldOne.Text = "mycomment"

Me.cboFieldTwo.Clear
Me.cboFieldTwo.AddItem "mycomment"
Me.cboFieldTwo.AddItem "mypublication"
Me.cboFieldTwo.AddItem "mycoworker"
Me.cboFieldTwo.Text = "mypublication"

Me.cboFieldThree.Clear
Me.cboFieldThree.AddItem "mycomment"
Me.cboFieldThree.AddItem "mypublication"
Me.cboFieldThree.AddItem "mycoworker"
Me.cboFieldThree.Text = "mycoworker"

Me.txtFieldOne.SetFocus

End Sub

Sub DoProcess()
Dim oOutlook As Outlook.Application
Dim oFolder As Outlook.MAPIFolder
Dim oItem As Outlook.MailItem
Dim oNameSpace As Outlook.NameSpace
Dim fWeOpenedOutlook As Boolean
Dim oActiveExp As Outlook.Explorer
Dim oSel As Outlook.Selection
Dim i As Long
On Error GoTo ProcError
Set oOutlook = New Outlook.Application
If oOutlook.ActiveExplorer Is Nothing Then
  fWeOpenedOutlook = True
Else
  fWeOpenedOutlook = False
End If
If Not fWeOpenedOutlook Then
  Set oActiveExp = Outlook.ActiveExplorer
  For Each oItem In oActiveExp.Selection
    For i = 0 To 2
      If Len(aFieldData(i)) > 0 Then
        If oItem.UserProperties(AFieldNames(i)) Is Nothing Then
          oItem.UserProperties.Add AFieldNames(i), olText
        End If
      oItem.UserProperties(AFieldNames(i)) = aFieldData(i)
      oItem.Save
      End If
    Next
  Next
Else
 Set oNameSpace = oOutlook.GetNamespace("MAPI")
 Set oFolder = oNameSpace.PickFolder
 If MsgBox("You are about to Process " & oFolder.Items.Count & " Items", vbOKCancel, "Add Fields to folder") = vbOK Then
   For Each oItem In oFolder.Items
     For i = 0 To 2
      If Len(aFieldData(i)) > 0 Then
        If oItem.UserProperties(AFieldNames(i)) Is Nothing Then
          oItem.UserProperties.Add AFieldNames(i), olText
       End If
      oItem.UserProperties(AFieldNames(i)) = aFieldData(i)
      oItem.Save
      End If
    Next
   Next
 End If
End If
Set oItem = Nothing
Set oFolder = Nothing
Set oNameSpace = Nothing
If fWeOpenedOutlook Then
  oOutlook.Quit
End If
Set oOutlook = Nothing

Exit Sub
ProcError:
  Select Case Err
  Case Else
     MsgBox "Error: " & Err & " " & Error, vbInformation, "Do Processing"
     Resume Next
  End Select
End Sub


goto the Code Module and paste in

Option Explicit

Sub OutlookUpdate()
frmOutookFields.Show vbModal
End Sub


Now on the Tools Menu/Click on References and browse until you find your outlook Type Library.

Check the box beside this

it will be listed under Microsoft Outlook #.# Object Library

Now under the Debug menu click on compile VBAProject

if all goes well things will compile. You can now save the workbook with a sensible name such as Outlook-up.xls

Now you can run the code form the workbook Tools/Macro/Run.

Now unfortuately we have moved the code out of the outlook windows, well out of the outlook application altogether, however, we have made it complete transportables. You could also dump the code behind the form into outlook and run it from there if you wanted.

You could use exactly the same process as outlined above within outlook. You can create a user form, and create the controls. or in the Excel VBA Editor you can select the form and then from the File Menu you can export the form. In out look VBA you can import the form now the code is avaialbe in out look just define a sub that calles the form.

Please let me know if all this makes sense.

My apologies, but, our interesting discussion made me lose sight of my original requirements, which was to update a SELECTION of e-mails inside of outlook.  We went astray in the original post when you said "I use the pickfolder but that difference should not make a difference".  

So, a stand alone solution will not solve the problem. (it’s a shame because you obviously spent a long time giving me such excellent details, and yes it made total sense.)

I'm awarding points now,  but any further thoughts you have will be appreciated.   I am rephrasing my remaining question and posting it at.

" Share single outlook module with 6 users"
https://www.experts-exchange.com/questions/20796172/500pts-4-success-Share-single-outlook-module-with-6-users.html

My code will handle both. if you look at it it checks to see it there is an active explorer. if there is it runs against this, rather than prompting for a folder. Thus if outlook is open and the there are selected records in the outlook window then the code runs against these, If outlook is not open it operates against and selected folder. Thus the code will work as is.

The test to see if outlook is open when code begins is to check for an active explorer I set a flag so that I know if we need to clean up, but then the code uses the following code to use the active explorer. Which is what you want


If Not fWeOpenedOutlook Then
  Set oActiveExp = Outlook.ActiveExplorer
  For Each oItem In oActiveExp.Selection
    For i = 0 To 2
      If Len(aFieldData(i)) > 0 Then
        If oItem.UserProperties(AFieldNames(i)) Is Nothing Then
          oItem.UserProperties.Add AFieldNames(i), olText
        End If
      oItem.UserProperties(AFieldNames(i)) = aFieldData(i)
      oItem.Save
      End If
    Next
  Next
Else

If you put the form in out look (I have tested this) you get a dialogue box that show there fields and a place for the values for three fields and when you process it changes the fields in the marked records. I believe this is what you need it to to.?

One other questions. Do you have VB, can you use VB (Well actually 2 quesitons). If so, you might be able to put the snubs for the code inoutlook and then use modules else where for some of your coding.

I would be interested in know how things work out.

Just had a thought on makeing the code more general.

there are currenly three combo boxes with three text boxes. thise are fields and values for the field. You could read both Field names and Values form a file when the Userform loads. Thus thus the code would work for upto three fields and allow the user to select 1, 2 or 3 fields and and these could be selected from a list of any number of fields. Thus if you end up adding 10 fields you could just change the text file that holds the field list and posibly the response list. would this do what you need?

Wow, you are way ahead of me on this outlook to windows stuff.  I'll try it out tonight, but right now I've got to do some other stuff.  

By the way, the general input box is NOT very important to me and is only worth 100 points, so don't spend time on it unless the idea interests you.  If you are interested, I was looking for a way to pass the textual names from the calling program. Thus UserForm_Activate()
would not hardcode "my publication" but would get it from the caller.  

When called by: if vbok =  myInputBox (array("enter publication", "comment", "name"), Array(s, c, w), array(initialS, initialC, initialW) then .....
It would display the titles as in a dialog that looked like this.  If the 3rd array was empty, the dddd values would be empty, otherwise they would be initialized.  
enter publication  dddddddddddd
comment  dddddddddddddddddd
name ddddddddddddddddddddd
Perhaps a maximum array size of 10?  perhaps a standard title length so the title boxes line up like this?
enter publication dddddddddd
          comment dddddddddd
                name ddddddddd

With something like that, I would never have to code dialog boxes.

Bye for now

I have just completed proto-type for an add-on to a contact manager for a phone suppurt group. This has a main form with a drill-down through three combo boxes that leads to a call type. there are many call types and each has a different set of fields that need to be responsed to. What I did in my prototype was to make an array of lables and input controls on the form. In VBA you must be a bit more reliant on name. I did a little test and thigs work with consistent names. The idea was that if the user are handling Call type1 they have five fields, three of which are manditory and two which are optional. for Call type 2 then may need for required fields and one optional field. etc, etc. The provlem was that at the time the form is loaded there is no way to know how many fields nor their context. Once the drill down is complete we know what type of call they are handling. Now the fields are populated and made visible. the save routine (Yes it is data centric) then uses the Captions of the Labels and the Values in the fields to determine what information needs to be written out to document the call. Seems this idea might work for you.

Thus by creating Label1 to Label5 TextBox1 to TextBox5

And a little sub that turns off and on the visibility of the controls you can display the controls or hide them, it would not be a big deal to change their location as well. this would allow that you could have a form that reads info from a text file based on some criteria and that this determines then number, type and location of controls.

Some Sample code follows, just to give you an idea. I am using a combo box to turn off and on Fields but this could be the result of reading a file. passing a parameter, etc. etc.

HTH


Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Select Case Me.ComboBox1.Text
Case "One"
  ShowThese 1
Case "Two"
  ShowThese 2
Case "Three"
  ShowThese 3
Case "Four"
  ShowThese 4
Case Else
  ShowThese 99
End Select
End Sub

Private Sub UserForm_Activate()
Me.ComboBox1.Clear
Me.ComboBox1.AddItem "One"
Me.ComboBox1.AddItem "Two"
Me.ComboBox1.AddItem "Three"
Me.ComboBox1.AddItem "Four"
End Sub

Sub ShowThese(lngValue As Long)
Dim i As Long
Dim strTextName As String
Dim strLabelName As String
For i = 1 To 4
  strTextName = "TextBox" & Trim$(CStr(i))
  strLabelName = "Label" & Trim$(CStr(i))
  If i <= lngValue Then
     Me.Controls(strTextName).Visible = True
     Me.Controls(strLabelName).Visible = True
  Else
     Me.Controls(strTextName).Visible = False
     Me.Controls(strLabelName).Visible = False
  End If
Next
End Sub

Haven't read your 10:30 est post yet, but I've great news to report!
That worked O.K. (see small problem below).
I then added the following into an outlook module and put it on an outlook toolbar button. Turns out the .xls file name has to be 8.3 format, but after that, it worked like a charm! When I get to work, I'll  move it to a network drive, and test to see if multiple people can use it at the same time.  I expect success. How many more points do you want?  this is great so name your price!

Public Sub callXls()
Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Close
    .Workbooks.Open "C:\aaa Libraries\my Excel Library\outlook.xls"
   
    .Run "outlook.xls!OutlookUpdate"
    .Workbooks.Close
End With
Set xlApp = Nothing


End Sub

----- small problem with your code --------------------------
I did eactly what you said, and got "error 13: type mismatch" at the
for each oItem statment
 Set oActiveExp = Outlook.ActiveExplorer
        ' the error comes from oAcitiveexp.select
   For Each oItem In oActiveExp.Selection
    For i = 0 To 2
      If Len(aFieldData(i)) > 0 Then

so, I changed it to dim oitem as object instead of as outlook.mailitem and it worked. (added oitem to watch list and saw its type was explorer/explorer.   Any comments?

I'll start reading your drill down code but things are getting really busy at work AND at home.  Hate to leave this subject when your helping me so much and I'm making such great progress, but you probably won't hear from me for a day or two.

your variable form idea looks like exactly what I want.  I won't do it now  since I already have the application nearly finished.  But the next project I'll give it a shot. I might also drop another note on experts-exchange to see if anybody has seen something like that in a library.  To me it seems like an idea somebody must have thought of and implemented before.

Dont you thing it is strange that we have to go through such efforts to replace a simple inputbox?

Thanks again for all your help, its been a real pleasure.

I'm going to send you 200 extra points for all your help.  And, if you want to cherry pick some more, your answer will probably be the best for qustion 20796172.

 Share single outlook module with 6 users"
https://www.experts-exchange.com/questions/20796172/500pts-4-success-Share-single-outlook-module-with-6-users.html

pick up extra points at
https://www.experts-exchange.com/questions/20796419/Well-deserved-poiints-for-MichaelDS.html

MichaelDS, Don't forget to pick up extra points.