FREE Omaha Steaks-How can I update this Outlook field when replying to a contact?

I will give away FREE OMAHA STEAKS valued at $60. to whomever can give me the correct code below. I am a man of my word. Gift certificate will be delivered to programmer within two weeks of working code being provided.

Below is code that updates "Date of Last Contact" and "Number of Attempts" each time an email is sent to a contact within Outlook. What I would like to add is code that would update the property, "Client Last Replied" with the current date, when I reply to a client from an email they sent. If it can be done without having to reply to an email they send then fine but I think what fires off the code is when I send out an email. So it would make sense to only fire off the code when I send an email or reply to an email from a contact in my Outlook. Below is the form I'm using which may or may not be relevant and the code that I'm using which I received from BlueDevilFan.  Client Last Replied
brokerexecutivesAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:
Tested in isolation and incorrerctly entered in your code!

Corrected
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
                if len(item.conversationindex) > 44 then
                    Set olkProp = olkContact.UserProperties("Client Last Replied")   '<- Change the property name as needed'
                    olkProp.Value = now
                end if
                '--- Save the changes ---'
                olkContact.Save
            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:
Here's the code for the first part.

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
    On Error GoTo 0
    Set olkFolder = Nothing
    Set olkContact = Nothing
    Set olkProp = Nothing
End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I would imagine:

Chris
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
                Set olkProp = olkContact.UserProperties("Date of Last Contact")   '<- Change the property name as needed'
                olkProp.Value = format(today, "mmm dd yyyy")
                '--- Save the changes ---'
                olkContact.Save
            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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
Correction below ... same assumption on my part though so simply see if it does it.

Chris
rivate Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
                Set olkProp = olkContact.UserProperties("Date of Last Contact")   '<- Change the property name as needed'
                olkProp.Value = format(today, "mmm dd yyyy")
                '--- Save the changes ---'
                olkContact.Save
            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
 
Chris BottomleySoftware Quality Lead EngineerCommented:
To get ahead of the curve ... if it doesn't work ... can you select the field instead of number of attempts and use the value for that control in place of the string "Date of Last Contact" that I used.

BTW - no steaks necessary and probably against the terms anyway!

Chris
0
 
brokerexecutivesAuthor Commented:
Hi Chris,

Thanks for your help. I tried it, but it's not updating, "Client Last Replied" when I reply to a contact. Here's what my code looks like.
 a
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
In your earlier post you showed the picture of the form properties with "Number of Attempts"highlighted ... can you repeat this but for the properties of "Date of Last Contact"?

Chris
0
 
brokerexecutivesAuthor Commented:
Sure, but I should have been more clear. I want the value/property "Client Last Replied to update with the date of when I reply to an email sent by a contact.  I need the "Client Last Replied" updated. Both Date of Last Contact and Number of Attempts updates just fine when I fire off an email to a contact. Here's a video of all the values: Youtube_values in form
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Oops cut paste error

Chris
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
                Set olkProp = olkContact.UserProperties("Client Last Replied")   '<- Change the property name as needed'
                olkProp.Value = format(today, "mmm dd yyyy")
                '--- Save the changes ---'
                olkContact.Save
            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:
No. It's still only updating "Date of Last Contact" and "Number of Attempts".
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
BAck to can you provide an image of the Client Last Replied properties

Chris
0
 
brokerexecutivesAuthor Commented:
Here are two pictures of Display & Value of the "Client Last Replied" properties.

 c1 c2
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Prhaps I was trying to be too clever.  The name appears correct so I have regressed to simply using teh full timestamp rather than formatting it.

Chris
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
                Set olkProp = olkContact.UserProperties("Client Last Replied")   '<- Change the property name as needed'
                olkProp.Value = now
                '--- Save the changes ---'
                olkContact.Save
            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:
It works but not the way I need it. It's updating exactly like, "date of last contact" which is anytime I send an email to a contact. However, I need it to only update when I'm hit, "Reply" from an email a contact sent.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I am not aware that we can explicitly identify reply ... we can differentite from a new message by checking teh conversationindex ... as below if that helps the functionality?

Chris
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
                if item.conversationindex <> "" then
                    Set olkProp = olkContact.UserProperties("Client Last Replied")   '<- Change the property name as needed'
                    olkProp.Value = now
                end if
                '--- Save the changes ---'
                olkContact.Save
            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:
No, it's still updating when a new email is sent to the contact.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
A new email does not have a value for the conversationindex ... therefore a new email should not trigger the block.  Can I confirm what you mean by a new email?

Chris
0
 
brokerexecutivesAuthor Commented:
Sure. New email. Select "New">input the clients email address, subject...., send. Whereas, reply would be: Client emails me> I then hit reply to that contact then send.
0
 
brokerexecutivesAuthor Commented:
I can create a video what I'm doing. Would that help?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
No that wouldn't help ... I really cannot begin to understand how a new mail will have a non blank index.  Are you sure you are running the latest code?

Chris
0
 
brokerexecutivesAuthor Commented:
Here's a picture of the code. What if we were to record when the date when a reply arrived in my inbox instead of me hitting reply for the code to work?

 yourcode received
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Ah haven't monitored during a send as such so a small addition to see if that is it

Chris
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
                if item.conversationindex <> "" and item.conversationindex <> item.entryid then
                    Set olkProp = olkContact.UserProperties("Client Last Replied")   '<- Change the property name as needed'
                    olkProp.Value = now
                end if
                '--- Save the changes ---'
                olkContact.Save
            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
 
Chris BottomleySoftware Quality Lead EngineerCommented:
No, whilst that is indeed the issue ... it isn't the solution.  still looking.

Chris
0
 
brokerexecutivesAuthor Commented:
Okay, well that code didn't work. I'll await your solution. Thanks
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
A bit of testing in isolation so I believe this may be it ... honest!

Chris
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    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
        '--- 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
                if len(item.conversationindex > 44 then
                    Set olkProp = olkContact.UserProperties("Client Last Replied")   '<- Change the property name as needed'
                    olkProp.Value = now
                end if
                '--- Save the changes ---'
                olkContact.Save
            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:
CONFIRMED! AWESOME! I owe you some steaks! haha. Very nice. Very happy. I didn't see anywhere in the terms that do not allow for "Gifts". Feel free to shoot me your information via email asap. daniel at brokerexecutives.com. I'll be more than happy to get you taken care of. BIG SMILES OVER HERE. You have no idea how much more productive you've made my company.
0
 
brokerexecutivesAuthor Commented:
You're a genius! Thanks a million.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Very glad it helped ... though I have no idea how it could be so useful i'm just glad to have got there in the end!

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.