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!

This entry was posted in Excel, Outlook, Reader Question and tagged , , , , , , . Bookmark the permalink.

40 Responses to How To Create A Reminder Email For Outlook Email From Excel

  1. cjw says:

    Daniel – Thanks for this info! I use Outlook Track-It to track emails. It's been great.

  2. rizu says:

    hi, i was following ur page, when i go to more controls i couldnt find the " microsoft date and time picker control". what will b the next step i can do

  3. vali says:

    Hello
    I want to send email reminders automaticaly when a date from a cell rich a certain condition.
    Please help

  4. Manasa says:

    Hi,
    I want to send email reminders automatically when a date from a cell reaches a certain condition.
    Please help.

    Thanks

  5. Kerstin Z says:

    This works well. However, it has slowed down the other macro loops I have created. I need to send out emails if someone has been holding onto a packet instead of approving it and passing it along. I created a macro that loops through my data and identifies if they have been holding on to the packet for 5+ days or 10+ days. Is there any way I can use the send email macro without slowing things down? I'd love to send you a copy of what I have so you could take a look at it and make it go faster.

  6. Kerstin Z says:

    I just restarted my computer. That helped some. So, I don't know if the email macro would cause my other macro loops to slow down.

  7. dgxm says:

    Hello Vali,

    Have you found the answer to your question yet. Actually I have a similar question I want to solve. I want to send email reminders automaticaly when a date from a cell rich a certain condition, for example, 30 days before due dates.

    Many thanks,

  8. Frank says:

    Hello,

    Great article…easy to follow. Question: How would I get the email to include my Outlook signature? I would like it to be part of the pre-canned text at the end of the email window that pops up when you click send email. Please advise. Thanks in advance!

    -Frank

  9. John says:

    Does anyone know how to do the same thing but instead of the email reminder bring up the outlook appointment? Please email me at nicesimpleguy1@yahoo.com

    Thanks!

  10. Ian says:

    Does this work in MS Excel Version 2003? If it doesnt does anyone know how to do the same thing in Excel 2003?
    Regards
    Ian

  11. Cara says:

    How can the custom body message be a hyperlink in both the excel cell and the email? It transfers into the email as plain text.

  12. Aaron says:

    Daniel,
    First let me say thank you for your wonderfully easy to follow instructions: I have more than impressed my boss this week, making it easier for her now to send out reminder emails!!

    What i would like to know is, is it possible through the macro editing function to format (mainly size, colour. bold, underline) selected text such is part of this: (& vbCrLf & vbCrLf & ” And here is some more precanned text in the macro AFTER the Body stuff.”) so that it appears in the email formatted?

  13. ugo says:

    How do i enable the developer tab on excel 2003

  14. Sandy says:

    how do i insert the contents as the excel grid (tabular format ) in the mail?

  15. Srini says:

    Hi,
    This is an awesome macro script. Thank you for sharing this macro. Can i know how to add another mail ID in bcc list???

  16. Robyn says:

    This is great. I was able to manipulate it and my spreadsheet now works like a charm. Thank You!

  17. MColton says:

    Thanks for this. It definitely puts me a step in the right direction. I do have a question though, is there a way for me to send multiple emails from the same line? I can figure out how to customize this all except for this last detail.

  18. Kim says:

    This post and the information and file has saved my life. Thank you so much for sharing it.

  19. cowboy_neil says:

    Scarlet begonias and a touch of the blues…

  20. Realty South says:

    Hi! Your article rocks and is really a very good understand!?

  21. Colin says:

    Brilliant information however i need the ability to click on the send email buttn once to send to all listed.

    Is there a way to send more than one at a time? i tried highlight over to columns but it didnt work for me, I could be sending out anywhere between 100 and 200 emails a time.

    Thanks for your help

    Colin

  22. Nigel says:

    Hi Daniel,

    First of all thank you for this! This was pretty much what I was looking for and it was very well written and easy to follow especially for a VBA novice like myself.

    I ran into a error though when trying to run this because I am running Office 2010 on a 64 bit machine which is resulting in a Shellexecute command error.

    I tried replacing your

    Private Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    With

    f VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    #Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    #End If

    Which gets rid of the error but now outlook isn't responding at all. Just to reiterate I'm a novice so I'm not sure what I could be doing wrong. Other than what I stated I've changed nothing in your code.

    • Narendra says:

      Hi, Nigel,
      I too have Office 10, When you download the example zip file and extract it, save it first as macro enabled Excel worksheet and then follow other instructions. Everything works perfectly. I am still looking for automating the Send email part. In case someone has figured it out please post it here.

  23. The Belstaff jacket Classic Tourist Trophy is the official name of this Belstaff leather Outlet, however it will commonly be referred to as the ‘Trialmaster’ because of its resemblance to the 50’s and 60′s model. The Belstaff coat is also re-enforced with doubled material at the shoulder and elbow points. Fot those who prefer to wear this Belstaff Mens Jackets for casual rather than on the bike, removing the protectors couldn’t be simpler–a quick unzip of the pockets and the Belstaff Womens Blouson is as good as one built soley for fashion.Welcome to Buy Belstaff jackets online store.

  24. Mohammed Asique K says:

    Great Article………Thank you very,….This is what i am searching for………………………

  25. Contrasted against the grand gowns offered by other couturiers, wonens diesel jeans look casual modern even today.Why were these diesel uk jeans considered so shocking? The idea that they look equally chic on men and women. When it comes to diesel online , diesel jeans reflecting the subtle evolutions of this hardy fashion perennial through few decades. For a woman, diesel jeans sale is an indispensable garment with which she finds herself in fashion, because it is about style, not fashion. Fashions come and go, but style is forever. diesel skinny jeans is a popular choice among young people, for Most of current diesel jeans discount are designed to meet consumers' demand.

  26. Contrasted against the grand gowns offered by other couturiers, wonens diesel jeans look casual modern even today.Why were these diesel uk jeans considered so shocking? The idea that they look equally chic on men and women. When it comes to diesel online , diesel jeans reflecting the subtle evolutions of this hardy fashion perennial through few decades. For a woman, diesel jeans sale is an indispensable garment with which she finds herself in fashion, because it is about style, not fashion. Fashions come and go, but style is forever. diesel skinny jeans is a popular choice among young people, for Most of current diesel jeans discount are designed to meet consumers' demand.

  27. christian says:

    Thank you for this article. That’s all I can say safadsecu. You most definitely have made this blog into something special. You clearly know what you are doing, you’ve covered so many bases.Thanks!

  28. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts.thanks

  29. Then there are the classics brand name children's clothing, like Carter's and Buster Brown.

  30. Simon says:

    Hi Dan !!!
    This is very interesting and informative for me !!!
    However, I need your help on this spread sheet. I do not use outlook for email services, instead I use Lotus Notes. Could you please help me, how this macro can be configured to lotus notes …

  31. Yu Mcafee says:

    You know have a visual flag that the item is due or past due and action needs to be taken!

  32. Shreemanth says:

    Thanks Daniel. It is of Much Help

  33. Mandar Samant says:

    Hi Daniel,
    Thank you for the file. I am using it on the Excel 2011 on Mac Os and unfortunately can not send email as Shell32.dll not found.. is there any solution to workaround this?

    Please help…
    Best,
    Mandar

  34. Mandar Samant says:

    Hi Daniel, is there any way to use this script or variant for the Excel on Mac OS X, I tried to run it but it expects the Shell32.dll and hence the request.

    best,
    mandar.

  35. Ross says:

    HI,

    Where can I download the excel template to How To Create A Reminder Email For Outlook Email From Excel with all the headings, ie subject, reminder on, all day event, etc.

    Thanks

    Ross.

  36. Ross says:

    HI,

    Please ignore the previous post.

    Where can I download the excel template to How To Create An outlook calender Reminder From Excel with all the headings, ie subject, reminder on, all day event, etc.

    Thanks

    Ross.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>