Solved

Bulk Edit Custom Contact Fields Outlook 2007

Posted on 2011-02-16
35
796 Views
Last Modified: 2012-05-11
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
Comment
Question by:brokerexecutives
  • 20
  • 15
35 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 34909133
Hi, brokerexecutives.

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

Author Comment

by:brokerexecutives
ID: 34909304
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
 
LVL 76

Expert Comment

by:David Lee
ID: 34909340
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
 

Author Comment

by:brokerexecutives
ID: 34909353
@BlueDevilfan I agree with you. How do I do that?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34909468
Do you send the emails out through Outlook?  If so, is this via a mail merge?
0
 

Author Comment

by:brokerexecutives
ID: 34909485
Yes. I sent them via mail merge.
0
 

Author Comment

by:brokerexecutives
ID: 34909564
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
 

Author Comment

by:brokerexecutives
ID: 34909668
@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
 
LVL 76

Expert Comment

by:David Lee
ID: 34910492
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
 

Author Comment

by:brokerexecutives
ID: 34910507
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
 
LVL 76

Expert Comment

by:David Lee
ID: 34938680
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
 

Author Comment

by:brokerexecutives
ID: 34940447
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
 

Author Comment

by:brokerexecutives
ID: 34940500
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
 
LVL 76

Expert Comment

by:David Lee
ID: 34956768
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
 

Author Comment

by:brokerexecutives
ID: 34956840
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
 
LVL 76

Expert Comment

by:David Lee
ID: 34956885
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
 

Author Comment

by:brokerexecutives
ID: 34957002
I did. I've attached the picture below of what it looks like.  txt
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 76

Expert Comment

by:David Lee
ID: 34957044
That's the name of the control, not the underlying property.  Look at the Value tab.  
0
 

Author Comment

by:brokerexecutives
ID: 34957052
Here is the Value Tab. Does that look right? v
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34957079
Yes.  For the other property, does Choose Field say Number of Attempts?
0
 

Author Comment

by:brokerexecutives
ID: 34957087
Yes. It does.  n
0
 

Author Comment

by:brokerexecutives
ID: 34957163
Here's a video I uploaded to better show you what I might be doing wrong.

Youtube
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34957314
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
 

Author Comment

by:brokerexecutives
ID: 34957343
I specified a category of "SOI" and ran into a 438 error code. I've attached a video.

Youtube-Error Code
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34957370
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
 

Author Comment

by:brokerexecutives
ID: 34957385
The contact only has an address in field one of the contact. Is that what you mean?

 o o3
0
 

Author Comment

by:brokerexecutives
ID: 34957397
I also tried emailing directing from the contact which fills in the contact data.  It still shows an error.  odata
0
 

Author Comment

by:brokerexecutives
ID: 34957408
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
 
LVL 76

Expert Comment

by:David Lee
ID: 34957467
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
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 34957527
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
 

Author Comment

by:brokerexecutives
ID: 34957539
I think that just fixed it! OMG! I'll running a couple more test, but by golly, I think we have it!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34957545
Cool.
0
 

Author Comment

by:brokerexecutives
ID: 34957569
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
 

Author Closing Comment

by:brokerexecutives
ID: 34957571
Works like a charm! David knows his code! Thank you!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 34957629
You're welcome!  Happy I could help out.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now