+ Reply to Thread
Results 1 to 9 of 9

Automatic Outlook Email Based on Date in range of Cells HELP!!!!

  1. #1
    Registered User
    Join Date
    01-14-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    6

    Automatic Outlook Email Based on Date in range of Cells HELP!!!!

    Hi all,

    This is my first post on the forum.

    I have been trying to put this together for sometime by trying to alter bits of code I have picked up from different forums but to due to my lack of knowledge in coding I haven't been successful, hopefully someone will be able to help.

    Basically I have a range of expiry dates in Column E. I am trying to get outlook to automatically send an email once the dates in row E enter the current month, with the body of the email being something like "Reminder: (equipment name in column A) is due to expire on (date in row E).

    So for instance if the date in E5 was 25-03-16 and the equipment in A5 was 500kg Chain Hoist. The email would read "Reminder 500kg Chain Hoist is due to expire on 25-03-16 please check equipment register for further information.

    I have attached the spreadsheet.

    Hope someone can help. This has been frustrating me for sometime. To the point where I am trying to learn code whilst juggling 3 kids under 3!!!!!!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Automatic Outlook Email Based on Date in range of Cells HELP!!!!

    Hi, Try this...

    Note: The toWhom = "" is where you put in who you want the email to go to
    Note: I have just brought it up to display the email, to send the email you will need to replace .Display with .Send

    Please Login or Register  to view this content.
    Last edited by spitfireblue; 03-01-2016 at 11:15 PM.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automatic Outlook Email Based on Date in range of Cells HELP!!!!

    Hello benharley84,

    Welcome to the Forum!

    Here is a fully automated version. It will automatically send the reminders whenever a cell in column "E" is changed or the worksheet is activated. Once sent, a date stamp is entered in column "H". This not only marks when the reminder was sent but also prevents multiple reminders from being sent.

    Two new columns have been added to the worksheet: columns "H" and "I". The former column holds the reminder date stamp. The latter column holds the email recipient's address. Reminders are sent only when the date is in the current month, no reminder has been sent, and an email address is in column "I".

    ThisWorkbook Code
    Please Login or Register  to view this content.
    Module1
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    01-14-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    6

    Re: Automatic Outlook Email Based on Date in range of Cells HELP!!!!

    All,

    Thanks for your help. Really appreciate it. I suppose its easy when you know how!!

  5. #5
    Registered User
    Join Date
    01-14-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    6

    Re: Automatic Outlook Email Based on Date in range of Cells HELP!!!!

    Sorry to trouble you again. I had the spreadsheet working fine today on my desktop at work.

    When I have tried it on my laptop at home. I am getting this error?
    Attached Images Attached Images

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automatic Outlook Email Based on Date in range of Cells HELP!!!!

    Hello benharley84,

    Which versions of Windows and Office are running on your laptop?

  7. #7
    Registered User
    Join Date
    01-14-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    6

    Re: Automatic Outlook Email Based on Date in range of Cells HELP!!!!

    Windows 10

    Outlook 2013

    does the code vary if the versions are different?

  8. #8
    Registered User
    Join Date
    01-14-2016
    Location
    uk
    MS-Off Ver
    2010
    Posts
    6
    Hi leith

    Don't suppose you've had any luck with this?

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Automatic Outlook Email Based on Date in range of Cells HELP!!!!

    Helo benharley84,

    l am sorry for the late response. When it rains, it pours - especially here in California.

    The problem with the original code was a missing period. Here is the corrected code and workbok.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Automatic Outlook mail due date reminder based on Excel file
    By iamitp in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-18-2019, 05:51 PM
  2. [SOLVED] Email data using outlook based on a cell range
    By mnunnley in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-14-2016, 12:55 PM
  3. Automatic email notification based on due date
    By sarsmelt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-09-2014, 08:13 AM
  4. Automatic email notification based on due date
    By sarsmelt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2014, 10:41 AM
  5. Create an Outlook Email and or Task based on a due date.
    By appzattak in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2013, 05:30 PM
  6. Automatic email from excel based on due date
    By AmishJPatel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-06-2012, 04:58 PM
  7. Automatic Email Alerts based on due date in Excel Via Outlook
    By lcmerten in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-24-2011, 03:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1