Solved

Is It Possible to Add Option When Sending Email In Outlook To Automatically Trigger A Reminder?

Posted on 2013-05-22
15
819 Views
Last Modified: 2013-05-28
I am having trouble finding any information on this because I am unsure of how to word what I am looking for in a clear, concise manner and google isn't really helping.

Ideal scenario:

When I compose an email in outlook, I would like to click a checkbox (or somehow otherwise select the option) to automatically have a reminder pop up if I don't receive a reply to that email within 3 days. A box should pop up and say "You have not received a reply to this email in 3 days. Please follow up with insured."

An alternate way of accomplishing this would be if (again) I could select the option when composing the email and have it be moved from the sent folder to an "ATTENTION" folder if I do not receive a reply to that email within 3 days.

Something that should make it a bit easier is that there will only be one recipient for each email, so I won't have to deal with tracking replies from multiple recipients.

Is this possible? I feel like there has to be a way with VBA, and I feel like I could figure out some of it - but I don't really know where to start with 1. tracking a reply to an email and 2. adding a dropdown/checkbox option to apply the rule to an email when I compose a new email.

If this was VBA code, it would be relatively easy to integrate because I am already using a userform interface to pick/complete outlook templates to send out.

Thanks!
0
Comment
Question by:Animaldrummer04
  • 7
  • 4
  • 4
15 Comments
 
LVL 34

Expert Comment

by:Michael-Best
Comment Utility
FollowUp.cc allows you to create reminders by specifying the time interval you like to be reminded to follow up within the BCC field.
If I entered OneWeek@followup.cc, you receive a reminder a week later to follow up on that email.

http://www.followup.cc/


Boomerang has similar capabilities within the gmail web client.

http://www.boomeranggmail.com/
0
 

Author Comment

by:Animaldrummer04
Comment Utility
I checked out followup.cc, but I don't think that's what I am looking for because it doesn't have the capability to only remind you if you don't receive a reply to the email. Also, I need more than 20 reminders a month and I don't want to pay for the service.

Boomerang looks awesome, but the outlook version is a free 30 day trial and then you have to purchase it.

I know that there has to be a way to integrate this with VBA for free, or some other free solution. I mean, I don't know, but there really should be. It doesn't seem that difficult.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, Animaldrummer04.

As I suspect you're aware, Outlook does not have this capability built in.  Yes, it's possible to do this using VBA, but with a couple of caveats.  Outlook uses a unique ID to track conversations.  The code would key on this ID to determine that a response is the response you're looking for.  If the client responds with a new message instead of replying to your message, then their response will have a different tracking ID and won't be seen by the code as being the response you're looking for.  Another potential problem is if the client responds with a question or generic response (e.g. "I'll check on that and get back to you") instead of with the information you need.  The code will see their initial response and cancel the reminder you want.  Finally, this will only work for message you send/receive in Outlook.  It won't work for messages you send from OWA or a mobile device.

Beyond those concerns the code shouldn't be too difficult.  You'll indicate which messages you want to track, more about that in a minute, and the code will create a reminder.  As messages arrive the code will check to see if the conversation ID of the newly arrived message matches the conversation ID of any of the messages you're waiting for.  If it does, then it will cancel the reminder.  If no response is received prior to the reminder time, then you'll get the pop up reminder.  

There are several ways you could indicate that you want to track a message that don't involve userforms or custom Outlook forms.  For example, you could include something in the subject line or message body (e.g. /wfr for "wait for response").  The same code that creates the reminder when you send would strip out the /wfr so the recipient never sees it.  You could also use a category or even use Outlook's built-in flag capability.  If the message has a flag, then create a reminder on the way out.

I can create the code.  I'll need to know what version of Outlook you're using and how you want to indicate which messages to create reminders for.
0
 

Author Comment

by:Animaldrummer04
Comment Utility
Thanks, BlueDevilFan. To address some of your points:

It's rare enough that I receive an email response that just says "I'll check and get back with you" that It's not an issue to go in and manually create a reminder for it if that happens. What I am combating right now is shooting off an email and most of the time never getting a response. Also, in my experience I do not have an issue with insured's responding with new emails instead of clicking "reply", so that too could be dealt with on a manual basis.

I don't check my work email on my phone (i need some space), but I do have Outlook on my personal laptop connected to my work server. It seems as if everything syncs - obviously not any VBA code, but emails and reminders sync. So if i have the VBA script on both machines (my personal laptop and my work computer), once I send the email and the script creates a reminder, that reminder should sync to the other machine, correct?

Using Outlook's built-in flag capability seems to be the smoothest integration. How would that work? Would I just select any follow-up flag (tomorrow, next week, no date, etc) and if the vba code saw that the message was flagged it would replace the flag with the custom code-defined one that looks for the email reply? I would like the reminder for all emails set at 2 days. 2 business days would be best, but if VBA can't distinguish business days from days then days work as well.

Thanks for your help!

Oh, I have Outlook 2010 on my work computer, Outlook 2013 on my personal. It's more important that it work for 2010, so if it's too much work making it compatible for both versions then just 2010 is great.

I already have a VBA userform for selecting and populating certain often-used templates (i attached a screenshot of the userform). Even though I want to be able to apply this email-tracking reminder functionality any time I compose a new email, my end goal is to adapt it so that when the adjuster selects an email template from the drop down, they can also select a box if they want a reminder set up for the email and how many days out they want that reminder (again, only to be triggered if a response isn't received).

I think that once I see how the code is written I won't have any trouble adapting it to the userform function that I want. Thanks again for your help!
screenshot.jpg
0
 
LVL 34

Expert Comment

by:Michael-Best
Comment Utility
by: Animaldrummer04Posted on 2013-05-23 at 01:01:18ID: 39188173

Free is a perspective of the user...
What saves you in the long run can be considered as free.
0
 

Author Comment

by:Animaldrummer04
Comment Utility
Right, but this function is a matter of convenience; none of my colleagues use anything like this so it's not expected. I don't expect that this is going to get me a raise or get me fired, so by my perspective free = money does not change hands. :)
0
 
LVL 34

Expert Comment

by:Michael-Best
Comment Utility
A small investment in software can be considered productive or cost effective.
This can be considered as free in the long run.
A small cost to oneself may lead to promotion or better conditions in ones company.
0
Do email signature updates give you a headache?

Do you feel like all of your time is spent managing email signatures? Too busy to visit every user’s desk to make updates? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today!

 

Author Comment

by:Animaldrummer04
Comment Utility
I have been playing around with FollowUpThen and it's so so close to being what I need. It's very similar to followup.cc, but has the additional feature of automatically canceling the reminder if the recipient replies within the time limit of the reminder. If they don't reply, then both you and the recipient get the reminder. The caveat to this is that you have to put the reminder email address in the CC field (not BCC), and if the recipient doesn't "reply all" then the reminder never gets canceled because it doesn't realize the email was replied to.

1.

I want this process to be transparent to the recipient, so I don't really want the recipient getting a reminder or seeing the address in the CC field (the address in the CC field is very minor, though)

2.

I don't want to have to rely on the recipient sending "reply to all" for my reminder system to work. I think that might be ok if I was emailing the same people all the time, but my entire interaction with each recipient is about 3 emails so by the time they catch on our interaction is over.
I was looking into trying the possibility of just Bcc-ing the reminder (so it will send me a reminder no matter if the insured responds or not), and then when I receive the reminder some rule or code would check a folder to see if that email was replied to - and if so immediately move the reminder to the trash before I see it in the inbox. I feel pretty confident at this point with my Excel VBA skills, but every part of what I just described I wanted to do felt over my head code-wise. Not sure if i'm just making it more difficult than it has to be or not.
0
 
LVL 34

Expert Comment

by:Michael-Best
Comment Utility
"Not sure if i'm just making it more difficult than it has to be or not. "

Yes...
0
 

Author Comment

by:Animaldrummer04
Comment Utility
Michael - I've already stated that I do not want to spend any money to accomplish this, so if that's what you're implying by indicating that I am making it more difficult than it has to be that's not helpful. If that's not what your implying and you know of a better way to accomplish what I am trying to do for free - still not helpful as no amplifying information was given.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Animaldrummer04,

I've run into a snag and want to get your input on how you'd like to proceed.  The snag is that the ConversationID property, which I'd planned to use to identify replies, isn't reliable for messages going outside of Exchange.  The ID on the response is different from that on the sent message.  That leaves two possibilities.  First, we can use the conversation topic.  Its value is consistent, but it isn't unique.  A message's conversation topic is its subject.  Unless all the messages you send have unique subject lines, then the topic isn't a reliable way to find the message it's replying to.  Second, I can generate a unique code and insert it in the body of the message.  So long as the recipient doesn't delete or alter that code, then I can key on it to find the parent message.  I can hide this code in the message body if the message is in HTML or Rich text format.  However, should any of your customers get their messages in plain text format, then they'd see the code.  I could include a warning asking them not to alter or delete the code, but I can't prevent them from doing that.  A variation on this approach would be to use a transparent gif image instead of embedding the code in the body of the message.  The gif's img tag would include the tracking number.  That would prevent the recipient from ever seeing the code even if they read messages in plain text, but the image would probably be lost if they replied in plain text.  Without the image the code wouldn't be able to determine the message the reply is to.  I think that putting the tracking code in the body of the message has the best chance for success.  How would you like to proceed?
0
 

Author Comment

by:Animaldrummer04
Comment Utility
That's disappointing about the ConversationID, that seems like the easiest/most reliable way as long as it was consistent outside of Exchange. I am curious though as to how Outlook threads email replies received from outside of Exchange? Maybe it doesn't thread those replies and I just never noticed. Huh.

I agree with you, the best way forward at this point would be to hide the unique ID within the HTML code in the message body. Most people just hit reply, so deleting it shouldn't be an issue. I like the idea of the warning to not alter or delete the code.

Just a thought, not understanding how ConversationID is generated or where it is attached to the email - Would it be possible to put the code wherever the ConversationID is located in the email? That seems pretty safe from being deleted. Not sure if that's even remotely possible, but just a thought.

Thanks for your help, I'm interested to see the code and figure out how you accomplish this.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
Comment Utility
"I am curious though as to how Outlook threads email replies received from outside of Exchange?"

Me too.

"Would it be possible to put the code wherever the ConversationID is located in the email? "

I'm not clear on what you mean.  ConversationID is a property of each message.  It is system assigned and read only.  I can't alter it or generate my own code.  It's also not visible in Outlook's interface, so I can't put the code I plan to generate next to it.

Here's the solution I propose.  Before we get to the code, let me take a minute and describe how it works.  When you want to wait for a response you'll include "/wfr" in the subject line.  When you send the message the code will look for that character string in the subject.  If it doesn't find it, then nothing happens and the message goes on its way.  However, if the script does find that string, then it generates a unique tracking code and inserts it into the body of the message.  If the message is in HTML format, then the tracking code won't be visible because I've inserted it as white text on a, presumably, white background.  If the message is in plain-text format, then the tracking code will be visible.  Either way, I've included text asking the reader not to delete the tracking code.  The last step before sending the message on its way is for the script to delete "/wfr" from the subject line so the recipient never sees it.  With the tracking code inserted the message is sent.  The last step in the sending process is for the code to set a reminder on the email.  As requested I've written the code to set a reminder of 2 business days.  Note that while the script will skip over Saturdays and Sundays it's not clever enough to avoid holidays.  

The message has now been sent and the script has set a reminder on the sent message.  If the recipient doesn't respond within those two days, then the reminder will fire indicating to you that the recipient has not replied to your message.  That brings us to replies.  The script will check every message you receive looking for a tracking code.  The tracking code is the code generated by the script when you send a message with "/wfr" in the subject line.  If the script does not find a tracking code, then nothing happens.  When the script does find a tracking code, then it searches your Sent Items folder for a message with the same tracking code.  If it finds one, then it clears the reminder from that message.  The result should be that you only get a reminder for messages that have not been replied to in two business days.  

I've performed a very limited test of the process and it appears to work as described.  You should test it yourself to make certain it works properly in your environment before depending on it.

A couple of notes about things that will break the process.

1.  Moving a message that you're waiting for a response to out of Sent Items will cause the process to fail.
2.  Saving a sent item in any folder other than Sent Items will cause the process to fail.
3.  If the recipient edits or deletes the tracking code, or the text around it, then the process will fail.
4.  The tracking code itself is number representing the year-month-day-hour-minute-second the message was sent.  If you somehow manage to send more than one message within a second, then those messages would have identical tracking codes.  A response from either recipient would clear both reminders.

The code itself comes in two parts.

Part 1.

Follow these instructions to add the code to Outlook.

1.  Start Outlook
2.  Press ALT+F11 to open the VB Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  Right-click on Class Modules, select Insert > Class Module
5.  In the Properties panel click on Name and enter clsWaitForReply
6.  Copy the code from the code snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included a comment wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

Private WithEvents olkApp As Outlook.Application
Private olkFld As Outlook.MAPIFolder

Private Sub Class_Initialize()
    Set olkFld = Session.GetDefaultFolder(olFolderSentMail)
    Set olkApp = Outlook.Application
End Sub

Private Sub Class_Terminate()
    Set olkApp = Nothing
    Set olkFld = Nothing
End Sub

Private Sub olkApp_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim olkMsg As Object, strCod As String
    If Item.Class = olMail Then
        Set olkMsg = Item
        If InStr(1, olkMsg.Subject, "/wfr") > 0 Then
            With olkMsg
                .Subject = Replace(.Subject, "/wfr", "")
                strCod = CreateTrackingCode()
                Select Case .BodyFormat
                    Case olFormatHTML
                        .HTMLBody = .HTMLBody & "<br><span style=""color:white"">Please do not change or delete the following code.<br>|Tracking Code: " & strCod & "|</span>"
                    Case Else
                        .Body = .Body & vbCrLf & "Please do not change or delete the following code." & vbCrLf & "|Tracking Code: " & strCod & "|"
                End Select
                .BillingInformation = strCod
                .ReminderTime = TwoDaysFromNow()
                .ReminderSet = True
                .Save
            End With
        End If
    End If
End Sub

Private Function TwoDaysFromNow() As Date
    TwoDaysFromNow = DateAdd("d", 2, Now)
    Do While True
        Select Case Weekday(TwoDaysFromNow)
            Case vbSaturday, vbSunday
                TwoDaysFromNow = DateAdd("d", 1, TwoDaysFromNow)
            Case Else
                Exit Do
        End Select
    Loop
End Function

Private Sub olkApp_NewMailEx(ByVal EntryIDCollection As String)
    Dim arrEID As Variant, varEID As Variant, olkItm As Object
    arrEID = Split(EntryIDCollection, ",")
    For Each varEID In arrEID
        Set olkItm = Session.GetItemFromID(varEID)
        If olkItm.Class = olMail Then
            ProcessResponse olkItm
        End If
    Next
End Sub

Private Sub ProcessResponse(olkMsg As Outlook.MailItem)
    Dim olkCol As Outlook.Items, olkItm As Outlook.MailItem, strCod As String
    strCod = FindString(olkMsg.Body, "\|Tracking Code: [0-9]*\|")
    If strCod <> "Not found" Then
        strCod = Replace(strCod, "|", "")
        strCod = Replace(strCod, "Tracking Code: ", "")
        Set olkCol = olkFld.Items.Restrict("[BillingInformation] = '" & strCod & "'")
        If olkCol.Count > 0 Then
            For Each olkItm In olkCol
                If olkItm.ReminderSet Then
                    olkItm.ReminderSet = False
                    olkItm.Save
                End If
            Next
        End If
    End If
    Set olkCol = Nothing
    Set olkItm = Nothing
End Sub

Private Function CreateTrackingCode() As String
    CreateTrackingCode = Format(Now, "yyyymmddhhnnss")
End Function

Function FindString(strText As String, strFind As String) As String
    Dim objRegEx As Object, colMatches As Object, objMatch As Object
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = False
        .Global = True
        .Pattern = strFind
        Set colMatches = .Execute(strText)
    End With
    If colMatches.Count > 0 Then
        Set objMatch = colMatches.Item(0)
        FindString = objMatch.Value
    Else
        FindString = "Not found"
    End If
    Set objRegEx = Nothing
    Set colMatches = Nothing
    Set objMatch = Nothing
End Function

Open in new window


Part 2.

Follow these instructions to add the code to Outlook.

1.  Start Outlook
2.  Press ALT+F11 to open the VB 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 File and select Options
9.  When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
10. Click Macro Settings
11. Select either of the two bottom settings (i.e. "Notifications for all macros" or "Enable all macros (not recommended; potentially dangerous code can run)".  The choice of which to chose is up to you.  If you select "Notifications", then you'll be prompted at times to enable macros. If you pick "Enable all" then there's a chance that a malicious macro could run.  It's a question of how much risk you want to assume.
12. Click Ok until the dialog-boxes have all closed
13. Close Outlook
14. 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.


Dim objWFR As clsWaitForReply

Private Sub Application_Startup()
    Set objWFR = New clsWaitForReply
End Sub

Private Sub Application_Quit()
    Set objWFR = Nothing
End Sub

Open in new window

0
 

Author Comment

by:Animaldrummer04
Comment Utility
Thank you very much for this, Today was crazy and I did not have time to implement this. I am hoping tomorrow will be less crazy; I'll let you know how it works!
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
You're welcome.  I'll be here if you have any questions or run into any problems.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

We are happy to announce a brand new addition to our line of acclaimed email signature management products – CodeTwo Email Signatures for Office 365.
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
In this video we show how to create a Distribution Group in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >>…
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…

728 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

10 Experts available now in Live!

Get 1:1 Help Now