Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Batch Updating of Custom Fields

I have created several custom fields such as initial mail date, second mail date, etc., which I would like to be able to update on a batch basis. For instance, when we do a mail campaign, I would like to be able to select 1000 names and update the inital mail date field with the date of the mailing. Is there a simple way to do this?
  • 2
1 Solution
Updating mail items in batch mode can be done very simply with a small Visual Basic program (or a VBA procedure if you are running Outlook 2000).

For example, I've created a new post form (based on the standard mail item) called IPM.Post.CustomMail.

In this form, I have added a custom field called usrScore (Number).

Then, I have published the new form in my Personal Forms Library.

For my test code, I've sent some mails by using my new form (New -> Choose form -> Personal Forms Library -> Custom Mail).

OK, let's go for the VB part now:

Create a new project, remove the default form and add a new module.
Paste the following code :

Option Explicit

Public Sub main()
  MsgBox GetSumFromOutlook, vbOKOnly, "Sum of Score Field"
End Sub

Public Function GetSumFromOutlook() As Long
  Dim olApp As Outlook.Application
  Dim olNameSpace As Outlook.NameSpace
  Dim olFolder As Outlook.MAPIFolder
  Dim olItem As Object
  Dim lngSum As Long
  ' Create a new instance of the Outlook application object
  Set olApp = New Outlook.Application
  ' Get the default MAPI namespace
  Set olNameSpace = olApp.GetNamespace("MAPI")
  ' Get the default Inbox folder
  Set olFolder = olNameSpace.GetDefaultFolder(olFolderInbox)
  ' Loop through mail items
  For Each olItem In olFolder.Items
      ' Compute the sum of usrScore
      If olItem.FormDescription = "CustomMail" Then
        lngSum = lngSum + olItem.UserProperties("usrScore")
        ' Reset the score
        olItem.UserProperties("usrScore") = 0
        ' Update the item
      End If

  ' Destroy references
  Set olItem = Nothing
  Set olFolder = Nothing
  Set olNameSpace = Nothing
  Set olApp = Nothing

  ' Return the result
  GetSumFromOutlook = lngSum
End Function

Press F5 to run the project.

The function GetSumFromOutlook will compute the sum of all usrScore fields and replace the current value.

Drop me a line if you want further explanations.
altschulerAuthor Commented:
brice123 - Thank you very much for your response. I tried to contact you to follow-up but was not able to get direct contact information from your profile.

I am actually trying to update a custom contact form. The contact form has the name of the company, contact person, and initial mail date, second mail date, etc. Can the code that you suggested be modified for this purpose? Alternatively, is there a different method to update these fields? (btw, I am using outlook 2000) Thanks agian for your assistance.
altschulerAuthor Commented:
brice123 - Thank you very much for your response. I tried to contact you to follow-up but was not able to get direct contact information from your profile.

I was able to accomplish the task with the following code.

Sub UpdateDateFields2()
 Dim objContactsFolder As Outlook.MAPIFolder
   Dim objContacts As Outlook.Items
   Dim strDateSelect As String
   Dim strDate As Date
   Dim objContact As Object
   Dim iCount As Integer

   ' Specify which contact folder to work with
   Set objContactsFolder = Outlook.ActiveExplorer.CurrentFolder
   Set objContacts = objContactsFolder.Items

   ' Prompt for field and date
   strDateSelect = InputBox("Enter the field to update.")
   strDate = InputBox("Enter date.")

   iCount = 0

   ' Process the changes
   For Each objContact In objContacts
      If TypeName(objContact) = "ContactItem" Then
        objContact.UserProperties.Find(strDateSelect) = strDate
        iCount = iCount + 1
      End If
   MsgBox "Number of contacts updated:" & Str$(iCount)

   ' Clean up
   Set objContact = Nothing
   Set objContacts = Nothing
   Set objContactsFolder = Nothing
End Sub

Thanks again for your assistance.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now