• 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

  • cjw

    February 27, 2009 at 11:10 pm

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

      Tracey

      July 4, 2011 at 9:24 pm

      ss

  • rizu

    March 10, 2009 at 6:20 am

    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

    vali

    March 17, 2009 at 10:18 am

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

      yayan

      May 16, 2016 at 4:23 am

      You can go to sent email history, see the tab about follow up and click add reminder.

    Manasa

    March 26, 2009 at 9:16 am

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

    Thanks

    Kerstin Z

    March 27, 2009 at 7:57 pm

    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.

    Kerstin Z

    March 27, 2009 at 8:07 pm

    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.

    dgxm

    July 14, 2009 at 9:23 pm

    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,

    Frank

    August 11, 2009 at 11:24 pm

    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

    John

    January 18, 2010 at 10:41 pm

    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!

    Ian

    February 14, 2010 at 12:02 pm

    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

    Cara

    March 1, 2010 at 3:24 pm

    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.

    Aaron

    April 15, 2010 at 8:45 pm

    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?

    ugo

    April 26, 2010 at 6:14 am

    How do i enable the developer tab on excel 2003

      Devrey

      August 9, 2011 at 9:02 am

      There is no developer tab on excel 2003. Just skip this step! 🙂

    Sandy

    June 1, 2010 at 5:52 am

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

    Srini

    June 11, 2010 at 10:06 am

    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???

    Robyn

    June 25, 2010 at 9:52 pm

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

    MColton

    July 15, 2010 at 5:53 pm

    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.

    Kim

    January 6, 2011 at 4:00 pm

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

    cowboy_neil

    March 18, 2011 at 9:02 am

    Scarlet begonias and a touch of the blues…

    Realty South

    April 3, 2011 at 10:36 pm

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

    Colin

    April 7, 2011 at 1:00 am

    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

    Nigel

    June 21, 2011 at 11:20 am

    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

      September 24, 2011 at 8:56 am

      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.

    belstaff jacket

    August 1, 2011 at 4:57 pm

    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.

    Mohammed Asique K

    August 7, 2011 at 1:33 am

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

    diesel jeans uk

    August 8, 2011 at 5:53 pm

    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.

    diesel jeans uk

    August 8, 2011 at 5:54 pm

    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.

    christian

    September 2, 2011 at 5:16 pm

    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!

    Ugg Winter Boots

    September 7, 2011 at 11:35 pm

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

    Cheap Chanel Jewelry

    September 16, 2011 at 4:48 pm

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

    Simon

    October 24, 2011 at 12:54 am

    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 …

    Yu Mcafee

    April 16, 2012 at 12:18 am

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

    Shreemanth

    July 28, 2012 at 3:27 am

    Thanks Daniel. It is of Much Help

    Mandar Samant

    August 8, 2012 at 7:48 am

    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

      Jaco

      June 13, 2014 at 1:42 am

      Hi Mandar & Daniel,

      Any solution for the Shell32.dll error message?

      Kind Regards,

    Mandar Samant

    August 8, 2012 at 8:09 am

    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.

    Ross

    August 17, 2012 at 3:08 am

    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.

    Ross

    August 17, 2012 at 3:10 am

    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.

    Peter

    April 15, 2014 at 3:48 am

    This is a fantastic job. keep it up Dan.
    what if i wanted to send to many people with the same subject and body contents but different email addresses. How do i go about it.

    Please your quick response is highly needed.

    shahbaz sarwar

    December 5, 2014 at 11:57 pm

    can we have any formula that can automatically send emails and mark it as email sent on due date. I dont want to do it manually as I have data set of about 5000 entries.

    can you help me in this regard.

    john

    May 1, 2015 at 5:45 am

    great stuff. quick question.
    I want to send email reminders automaticaly when a date from a cell rich a certain condition, for example, 30 days before due dates. How do I do that?

    Many thanks,

    Prega

    May 29, 2015 at 3:57 am

    I want to send email reminders automaticaly when a date from a cell reaches a certain value. If a due date is reached excel would automaticall send the email with certain cell contents.
    Many thanks.

    Prega

    Prega

    May 29, 2015 at 5:02 am

    Same as above

    Prega

    Finn

    September 29, 2015 at 5:48 pm

    Are you able to update this page for Excel 2013? I am getting an error message saying the sheet needs to be updated for 64-bit.

    Cheers

    Pipis

    November 8, 2015 at 8:34 pm

    Great post!! Can someone help in answering how to also add an attachment to that email, VBA code please.

    Sylvie

    January 28, 2016 at 7:45 am

    I love this file, I made my own little arrangement and everything is great.
    I do have a question, the Date column and the Sent? column both have automatic style, I’m wondering where is the function or macro or ? for that, I can’t find it anywhere?
    Anyone?

    Kayla

    May 5, 2016 at 9:22 am

    Thank you!!!!! Very excited to use this 🙂

    Claudious

    May 20, 2016 at 8:49 am

    This is very very useful. You have my day my day. May you assist on how i can highlight more than one row (a selection of rows) and send one email but to different email addresses.

    Danny

    June 10, 2016 at 5:44 am

    Hi Daniel,
    Thank you for this tip. I want to send email reminders automatically when a date from a cell reaches a certain value. If a due date is reached excel would automatically send the email with certain cell contents. Kindly advise/post in this page so that all can understanding.
    Many thanks again!!
    Danny

    Delroy

    July 6, 2016 at 4:32 am

    How do i upgrade this for a 64bit system? the error below is what i got when i try to run the VBA…. Please help, Thanks

    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

    Toya Goldsmith

    August 4, 2016 at 4:55 am

    Excellent ideas – I loved the facts , Does someone know if I can grab a sample a form form to use ?

Leave a Reply

%d bloggers like this: