Link to home
Start Free TrialLog in
Avatar of brokerexecutives
brokerexecutivesFlag for United States of America

asked on

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.
Avatar of David Lee
David Lee
Flag of United States of America image

Hi, brokerexecutives.

Do you mean you want to select them and then be prompted for values for those two fields?  
Avatar of brokerexecutives


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.
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.
@BlueDevilfan I agree with you. How do I do that?
Do you send the emails out through Outlook?  If so, is this via a mail merge?
Yes. I sent them via mail merge.
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.
@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.
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.
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.
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 ---'
            End If
        End If
    End If
    Set olkContact = Nothing
    Set olkProp = Nothing
End Sub

Open in new window

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. User generated image User generated image
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?
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?
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.  User generated image
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?
I did. I've attached the picture below of what it looks like.  User generated image
That's the name of the control, not the underlying property.  Look at the Value tab.  
Here is the Value Tab. Does that look right? User generated image
Yes.  For the other property, does Choose Field say Number of Attempts?
Yes. It does.  User generated image
Here's a video I uploaded to better show you what I might be doing wrong.

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.
I specified a category of "SOI" and ran into a 438 error code. I've attached a video.

Youtube-Error Code
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.
The contact only has an address in field one of the contact. Is that what you mean?

 User generated image User generated image
I also tried emailing directing from the contact which fills in the contact data.  It still shows an error.  User generated image
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.
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 "", then the code is searching for a contact with "" in the first email address field.  In the screenshot in outlookcontactfield2.jpg the address the message is being sent to is "".  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.
Avatar of David Lee
David Lee
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think that just fixed it! OMG! I'll running a couple more test, but by golly, I think we have it!
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.
Works like a charm! David knows his code! Thank you!
You're welcome!  Happy I could help out.