• Curran@CurranOmniMedia.com

How To Create A Reminder Email For Outlook Email From Excel

How To Create A Reminder Email For Outlook Email From Excel

How many times have I been asked this – let me count the ways . . .

How can I set up an excel document with suspense dates and get alerts from Outlook to remind me when they are due?

I built an Excel project management spreadsheet that uses start and end dates to track tasks. One field shows remaining days to complete the task. My question, is there a way to have excel utilize my Outlook to send me an email once a task is within 3 days of completion or past due? (I do not have the funds to license all my staff with Microsoft Project)

And a dozen more in the comments of various articles. Here’s the deal gang, while this sounds like an excellent idea – automatic Outlook reminders from an Excel spreadsheet – its not.

If you approach this with VBA code that says “Send an email to Outlook when . . .” that “When” statement has the potential to really screw some things up. First off, the spreadsheet needs to be open for the code to run. What happens when you go on vacation for two weeks and come back and open the file? What if you just forget to open the actual file because you are relying on an automatic reminder? How many pop-up meail windows are you going to get? How do you know everyone was emailed that needed to be emailed?

This is a case where automation sounds fantastic but in my opinion its really not a good idea.

Soapbox aside . . . Of course I have figured out an alternative for you.

I think the original script for this came from Martin Green over at FontStuff.com, or I found it in a discussion board. Disclaimer: I have modified the script to bend it to my will but I want to give props to whoever wrote the original core script. OK, props given – lets solve this mystery!

Do you want Excel to alert you of an approaching due date? Do you want to be able to send an automatic email to a person related to an item in that spreadsheet? Try it this way:

1. Download this file: Outlook eMail from Excel. (17k zip)
It is the example we will work from. I am going to show you a few tricks and then leave you to your own devices. Copy the pieces that work for your unique situation, ignore the ones you don’t need.

2. We are going to be working in developer mode so make sure you know how to enable it in Excel. The directions are in this posting.

3. Open up the spreadsheet you downloaded and follow along… Oh, and the spreadsheet has a Macro in it that you have to enable. Its safe, Trust me. I used to work for the Government.

So what are we looking at?

excel-outlook1

These columns can be rearranged anyway you see fit for your unique project. In this example we have people’s names, an email subject, an imaginary due date, an “email sent?” column, the person’s email address and a custom body message.

Click on any cell in a row to select it and then click the SEND MAIL button at the top of the spreadsheet. I’ll choose row 6, “Bill Jones”, the email generated looks like this:

excel-outlook2

What happened? The email address was filled, the subject line of the email was filled in and the custom body message was put in the body between two other lines of text.

Light-bulbs going off yet?

Where did those other lines of text in the body come from? A Macro. Let’s take a look at that macro so you can customize it.

Choose the DEVELOPER tab in Excel and click Macros (Again, the directions for enabling the Developer tab are in this posting.)

excel-outlook3

With the Macro box open select SendEmail and Edit.

excel-outlook4

Here is the code that does the magic.

There are 3 lines of code you need to be aware of so you can modify them if need be:

  1. Email = Cells(ActiveCell.Row, 10)
  2. Subj = Cells(ActiveCell.Row, 4)
  3. Msg = Msg & “Dear ” & Cells(ActiveCell.Row, 1) & “,” & vbCrLf & vbCrLf & “Here is some precanned text before the BODY info in the spreadsheet. ” & vbCrLf & vbCrLf & Cells(ActiveCell.Row, 13) & vbCrLf & vbCrLf & ” And here is some more precanned text in the macro AFTER the Body stuff.”

Line 1 says “Get the email address from Row 10 of the active cell.” This is a little misleading, its actually column 10 or “J”.

Line 2? Get the email subject from row (column) 4 or “D”

And Line 3? Prints “Dear” and whatever in is column 1 or “A”, and then the precanned text before and after whatever in in the spreadsheet in column 13 or “M”.

If you change around the columns in your spreadsheet, simply change the numbers to correspond to the data’s new location! You can even add more information in the email from your spreadsheet – the magic term is “Cells(ActiveCell.Row, ????)

Not too bad! Not too complicated either.

Now lets take a look at the date information back in the spreadsheet:

excel-outlook5

In the top right hand corner of the spreadsheet is Today’s Date, Excel formula being “=TODAY()

The dates in column “F” are “Due Dates” I entered in manually. Maybe your spreadsheet will calculate these cells from other data you have. The color change is based on a “Rule” that is comparing the date we entered to today’s date. You could just as easily give yourself a heads-up by referencing a date in the future instead of today’s date “=Today () +7″, etc.

Back to the rule:

  1. Highlight cell F4
  2. Select Conditional Formatting
  3. Select Manage Rules

excel-outlook6

And there is the rule that says “Blank Cells are just white” and “If the value of this cell (the date of this cell) is less than or equal to Today’s Date in R1 – color me reddish.”

You know have a visual flag that the item is due or past due and action needs to be taken!  Again, change this to whatever suits your need! Make it red, make it green – your choice!

The red X in the sent column is just another “food for thought” column. A reminder to yourself that you actually acted on the due or overdue item. So let’s look at this from the beginning on a typical work day:

  1. Come in to the office.
  2. Have Coffee
  3. Chit Chat
  4. Check Facebook
  5. Etc.
  6. Open Spreadsheet
  7. Look at the DATE column. Is anything highlighted that doesn’t have a Sent notification?
  8. Yes? Click a cell in that row and click the SEND EMAIL button.
  9. Send the email
  10. Put an X in the Sent box!
  11. Check Facebook

That’s pretty labor unintensive! Seriously? 3 Steps

  1. Look at the DATE column. Is anything highlighted that doesn’t have a Sent notification?
  2. Yes? Click a cell in that row and click the SEND EMAIL button.
  3. Put an X in the Sent box!

I am looking forward to see how all of you will improve upon this simple concept. This has been a hot topic in the comments section. There is no one size fits all solution but I hope that by taking this simplistic approach I save a lot of people a lot of time.

It doesn’t have to be complicated, and you don’t need to be a programmer!

63 Comments

  • CharlieG

    August 9, 2016 at 9:54 am

    I need a CC field on the VB code please.

  • Nyuke

    August 11, 2016 at 11:37 am

    This is good. Many thanks.
    How can I send the email by highlighting more than one Row.

    Asad Saghir

    August 25, 2016 at 10:00 pm

    Hey Daniel,

    Hope you are doing good. I need your help in creating email alert from excel to outlook. Actually we are using Web SMS portal for sending bulk SMS and i want to create an alert in excel sheet that whenever my SMS count will less than 50,000 an email should be sent on my email ID regarding low balance. Please Help.

    Regards,
    Asad Saghir

    Saj

    November 20, 2016 at 7:36 am

    Really simple and useful guide. Many Thanks

    sathappan kasi

    February 15, 2017 at 3:00 am

    i tried this ,the color changes but when i click send email ,its not working

    pls advise

    rahul

    April 3, 2017 at 6:47 am

    please solve my issue , iam getting an error
    compile error
    sub or function not defined
    please solve the issue

    this is the code iam using

    Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Email = Cells(ActiveCell.Row, 10)

    Subj = Cells(ActiveCell.Row, 4)

    Msg = “”
    Msg = Msg & “Dear ” & Cells(ActiveCell.Row, 1) & “,” & vbCrLf & vbCrLf & “Here is some precanned text before the BODY info in the spreadsheet. ” & vbCrLf & vbCrLf & Cells(ActiveCell.Row, 13) & vbCrLf & vbCrLf & ” And here is some more precanned text in the macro AFTER the Body stuff.”

    ‘Replace spaces with %20 (hex)
    Subj = Application.WorksheetFunction.Substitute(Subj, ” “, “%20″)
    Msg = Application.WorksheetFunction.Substitute(Msg, ” “, “%20”)

    ‘Replace carriage returns with %0D%0A (hex)
    Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, “%0D%0A”)

    ‘Create the URL
    URL = “mailto:” & Email & “?subject=” & Subj & “&body=” & Msg

    ‘Execute the URL (start the email client)
    ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

    ‘Wait two seconds before sending keystrokes
    ‘Application.Wait (Now + TimeValue(“0:00:02”))
    ‘Application.SendKeys “%s”
    End Sub

    Wes

    October 2, 2017 at 2:04 pm

    Hello, I would like to add another control button to this sheet. well… 3 more buttons: each will send an email for a different date approaching the expiration date (60 day, 30 day, 15 day, 7 day). Is there a simple way to copy the code and then paste/modify/attach to new button? Thanks

    Gina Ladea

    November 4, 2017 at 2:36 am

    Thank you very much for the code, it’s very usefull.
    But i have a problem when i put greek letters inside quotes ” “.
    The greek letters don’t apear in the email message.
    Thank you in advance.

Leave a Reply

%d bloggers like this: