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

Bulk Edit Custom Contact Fields Outlook 2007

I have designed a form in Outlook 2007 for my contacts. The custom fields I created are as follows:

1) Date Field
2) Two Digit Text Field

My end goal is to be able to bulk update all selected contacts with new dates or by changing the two digit text field created. The date field is used for date of last contact and the text field is used for number of attempts I've tried to reach that contact. It would be great to be able to hit Shift and mass select all the contacts I want to update, but I can't find any possible way to update these fields all at once. Any help would be great.
outlook11.jpg
0
brokerexecutives
Asked:
brokerexecutives
  • 20
  • 15
1 Solution
 
David LeeCommented:
Hi, brokerexecutives.

Do you mean you want to select them and then be prompted for values for those two fields?  
0
 
brokerexecutivesAuthor Commented:
Yes..I think. I'm wanting to mass change the fields. So for example, select all the contacts that need to be updated, then the change the value for the field, "Date of Last Contact" or "Number of Attemps". The names of the values I guess are not important but does that make sense?

What I'm doing is mass emailing all my contacts, then updating each contact's, "Date of Last Contact"/Value or date. The problem is, it's taking WAY TOO long to update each one individually.
0
 
David LeeCommented:
How are you sending the mass emails?  I ask because it'd be easier and more efficient to automatically update the contacts as the emails go out rather than design a manual process that you have to run.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
brokerexecutivesAuthor Commented:
@BlueDevilfan I agree with you. How do I do that?
0
 
David LeeCommented:
Do you send the emails out through Outlook?  If so, is this via a mail merge?
0
 
brokerexecutivesAuthor Commented:
Yes. I sent them via mail merge.
0
 
brokerexecutivesAuthor Commented:
I think I see where you're going with this. I selected "add fields" to my Phone List view and added, "Sent". I'm not sure if this is the right field for displaying the date of last email sent because it's showing the dates wrong. As you can see below, it's showing, "Sent"-09/15/2010 10:31AM. Whereas, these leads were not emailed on that date.
outlook12.jpg
0
 
brokerexecutivesAuthor Commented:
@BlueDevilFan:I think this will work. Is this where you were going with this?

Below you'll see Date of Last Contact and Modified values. The Modified is what is updated by Outlook automatically when an email is sent out...I think. The Date of Last Contact is the field I've had to manually update each time. As you can see, the date are almost all the same. Please let me know if this is what you had in mind or if I'm reading this information correct regarding date of last email sent/modified.
outlook-modified.jpg
0
 
David LeeCommented:
My idea is to use a bit of code to accomplish this as the messages are sent.  The code would fire each time a message is sent.  It would find the associated contact and update both fields.  That would make this automatic.
0
 
brokerexecutivesAuthor Commented:
I'm not familiar with code. I understand basic HTML coding and use dreamweaver quite often. If you post and code, I would need step-by-step instructions. Thank you.
0
 
David LeeCommented:
Here's the code.  Follow these instructions to add it to Outlook.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
5.  Edit the code as needed.  I included comment lines wherever something needs to or can change
6.  Click the diskette icon on the toolbar to save the changes
7.  Close the VB Editor
8.  Click Tools > Trust Center
9.  Click Macro Security
10. Set Macro Security to "Warnings for all macros"
11. Click OK
12. Close Outlook
13. Start Outlook.  Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.

For this to work the template mail message will have to have a certain category set.  The category can be anything you want it to be.  If we didn't use a category, then the code would fire for every email, not just your mass mailings.  Of course if you want the code to fire for every message, then you can edit it and remove that requirement.  You'll also need to adjust the names of the two user-defined properties you're using to keep track of the information.
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    'Change the category name as needed'
    Const TGT_CAT = "MassMail"
    Dim olkContact As Outlook.contactItem, olkProp As Outlook.UserProperty
    '--- If the item being sent as an email ---'
    If Item.Class = olMail Then
        '--- If the item being sent is a member of the target category ---'
        If InStr(1, Item.Categories, TGT_CAT) Then
            '--- Get the contact assocaited with the recipient email address ---'
            Set olkContact = Session.GetDefaultFolder(olFolderContacts).Find("[Email1Address] = '" & Item.Recipients.Item(1).Address & "'")
            '--- If a matching contact was found ---'
            If TypeName(olkContact) <> "Nothing" Then
                '--- Update the Last Contact property ---'
                Set olkProp = olkContact.UserProperties("Date of Last Contact") '<- Change the property name as needed'
                olkProp.Value = Now
                '--- Update the Attempts property ---'
                Set olkProp = olkContact.UserProperties("Number of Attempts")   '<- Change the property name as needed'
                olkProp.Value = Int(olkProp.Value) + 1
                '--- Save the changes ---'
                olkContact.Save
            End If
        End If
    End If
    Set olkContact = Nothing
    Set olkProp = Nothing
End Sub

Open in new window

0
 
brokerexecutivesAuthor Commented:
Thanks BDF. I did insert the code as you directed. I need help on the next step, which is, "For this to work the template mail message will have to have a certain category set.  The category can be anything you want it to be."

I created a category called, "Date of Last Contact X" and I assigned it to an incoming email and an outgoing one. I then tried looking for that new category in one of my contacts but could not find anything related to the last email date sent. What am I not doing right? I appreciate all you've done so far, thank you for very much. step 1 step2
0
 
brokerexecutivesAuthor Commented:
Okay. I understand now what the code means so I changed it to category, "SOI". After I changed, I restarted outlook and it prompted me to "enable or disable Macros". I "enabled". Then I fired off an email to a contact then checked that contact fields to see if they were updated. Nothing... What am I doing wrong?
outlookmissstep.jpg
0
 
David LeeCommented:
Sorry to be slow getting back to you.  

Did I use the correct user-define property names (i.e. "Date of Last Contact" and "Number of Attempts")?  I assume that the category was set before the items were sent.  Correct?
0
 
brokerexecutivesAuthor Commented:
Don't worry BDF. I don't know anyone else that can help so take your time. Here's the form I created. Those fields look right to me.  w
0
 
David LeeCommented:
There's a difference between the name of a control and that of a property.  Controls show information and allow input.  They do not store information.  Properties are just the reverse.  They store information but do not display it or enable editing.  Look at the properties for those two controls.  What are they bound to?
0
 
brokerexecutivesAuthor Commented:
I did. I've attached the picture below of what it looks like.  txt
0
 
David LeeCommented:
That's the name of the control, not the underlying property.  Look at the Value tab.  
0
 
brokerexecutivesAuthor Commented:
Here is the Value Tab. Does that look right? v
0
 
David LeeCommented:
Yes.  For the other property, does Choose Field say Number of Attempts?
0
 
brokerexecutivesAuthor Commented:
Yes. It does.  n
0
 
brokerexecutivesAuthor Commented:
Here's a video I uploaded to better show you what I might be doing wrong.

Youtube
0
 
David LeeCommented:
Thanks for the video.  That helps.  Before clicking Send on the test email be sure to assign the message to the category you specified in the code.
0
 
brokerexecutivesAuthor Commented:
I specified a category of "SOI" and ran into a 438 error code. I've attached a video.

Youtube-Error Code
0
 
David LeeCommented:
Ok, the error is on this line

Set olkContact = Session.GetDefaultFolder(olFolderContacts).Find("[Email1Address] = '" & Item.Recipients.Item(1).Address & "'")

My first guess is that your contact doesn't have the address you're sending from in the first address field.  Please check that.
0
 
brokerexecutivesAuthor Commented:
The contact only has an address in field one of the contact. Is that what you mean?

 o o3
0
 
brokerexecutivesAuthor Commented:
I also tried emailing directing from the contact which fills in the contact data.  It still shows an error.  odata
0
 
brokerexecutivesAuthor Commented:
Correction. When I open a contact and click on "Email" then select the category like the above, I receive no error code; however, I do not see the properties updating.
0
 
David LeeCommented:
I'm not really clear on what you mean in the last post.  

The screen in Outlook21.jpg looks correct.

The line that's causing the error is designed to find a contact where the address in the first email address slot matches the address that the message is addressed to.  If the message is addressed to "someone@gmail.com", then the code is searching for a contact with "someone@gmail.com" in the first email address field.  In the screenshot in outlookcontactfield2.jpg the address the message is being sent to is "david@homedoingwork.com".  The code would be searching for that address in a contact.  

So, there are two possibilities.

1.  The search is not finding a matching contact, which is causing the error.
2.  The search is unable to search at all because the folder I told it to search (i.e. the default contacts folder) does not exist.

The first possibility is far more likely that the second possibility.
0
 
David LeeCommented:
Replace the code you have now with the version below.  I added several error checking test to it to help figure out what's going on.
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    'Change the category name as needed'
    Const TGT_CAT = "MassMail"
    Const MACRO_NAME = "Item Send"
    Dim olkFolder As Outlook.Items, olkContact As Outlook.ContactItem, olkProp As Outlook.UserProperty
    '--- Turn off error handling ---'
    On Error Resume Next
    '--- If the item being sent as an email ---'
    If Item.Class = olMail Then
        '--- If the item being sent is a member of the target category ---'
        If InStr(1, Item.Categories, TGT_CAT) Then
            '--- Get the Cotnacts folder ---'
            Set olkFolder = Session.GetDefaultFolder(olFolderContacts).Items
            '--- Was the contacts folder found? ---'
            If TypeName(olkFolder) = "Nothing" Then
                '--- No ---'
                MsgBox "Could not find the Contacts folder", vbCritical + vbOKOnly, MACRO_NAME
            Else
                '--- Yes ---'
                '--- Get the contact associated with the recipient email address ---'
                Set olkContact = olkFolder.Find("[Email1Address] = '" & Item.Recipients.Item(1).Address & "'")
                '--- Was a matching contact found? ---'
                If TypeName(olkContact) = "Nothing" Then
                    '--- No ---'
                    MsgBox "Could not find a contact with the address " & Item.Recipients.Item(1).Address & " in the first email address slot.", vbCritical + vbOKOnly, MACRO_NAME
                Else
                    '--- Yes ---'
                    '--- Update the Last Contact property ---'
                    Set olkProp = olkContact.UserProperties("Date of Last Contact") '<- Change the property name as needed'
                    olkProp.Value = Now
                    '--- Update the Attempts property ---'
                    Set olkProp = olkContact.UserProperties("Number of Attempts")   '<- Change the property name as needed'
                    olkProp.Value = Int(olkProp.Value) + 1
                    '--- Save the changes ---'
                    olkContact.Save
                End If
            End If
        End If
    End If
    On Error GoTo 0
    Set olkFolder = Nothing
    Set olkContact = Nothing
    Set olkProp = Nothing
End Sub

Open in new window

0
 
brokerexecutivesAuthor Commented:
I think that just fixed it! OMG! I'll running a couple more test, but by golly, I think we have it!
0
 
David LeeCommented:
Cool.
0
 
brokerexecutivesAuthor Commented:
Confirmed! It has worked! Very kewl. Very nice. Thank you. I owe. Seriously. Ask and you shall receive my friend. You saved my fingers from carpal tunnel. One last question about the coding and I'll gladly post the question again for you to answer. In fact, I'll just do that right now.
0
 
brokerexecutivesAuthor Commented:
Works like a charm! David knows his code! Thank you!
0
 
David LeeCommented:
You're welcome!  Happy I could help out.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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