Closed Thread
Results 1 to 18 of 18

Email to notify expiry date

  1. #1
    Registered User
    Join Date
    06-08-2008
    Posts
    10

    Email to notify expiry date

    Hello

    I am looking for help on solving this, I am looking for a way to be able to send an email notification 30 days before the contract expires.
    I have attached a sample list. Is there anyway of doing this using a macro or through VBA?

    thank you !
    Attached Files Attached Files

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    Hi Glimmer, well here's a shot at it...

    I borrowed some code to send e-mail from Microsoft Outlook from Ron deBruin's site (http://www.rondebruin.nl/sendmail.htm). Please see attached spreadsheet.

    I added a command button to your worksheet (although that's optional, you could just as easily use a key combination or run it manually). The code to check each cell in column B for the expiry date, as well as the e-mail code, is shown below. Let us know if you need further assistance.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-08-2008
    Posts
    10
    Hello Paul

    Thank you. Just what I was looking for.
    just a slight issue- it sends out an email every time i click the send mail button to even the ones below 30days. is it possible to send it wen the date difference is exactly 30days?
    Also is there a way it would send out the mail without opening the worksheet ?

    thank you so much !

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    Hi G,

    To send it to those where the difference is exactly 30 days, change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    I don't see how you could run an Excel macro without Excel being open. You could schedule a task in Windows to open the Excel file at a certain time of day, and then in the Excel workbook apply the previous code to the Workbook_Open event, though.

  5. #5
    Registered User
    Join Date
    06-08-2008
    Posts
    10
    Hello Paul

    thank you. Scheduling a task in windows sounds good. I didnt quite understand the workbook_openevent bit. Does that mean once I have added the macro to the excel sheet and set up the task in windows to open the file daily it will automatically email out ?

    thanks

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    To insert this code into the Workbook_Open event:

    1. Copy all of the code shown below. I adjusted it slightly to always reference the sheet named "Sheet1". If your sheet name is different, change any references to Sheet1 to your sheet name.
    2. Open the VBA Editor in your workbook (ALT+F11).
    3. Double-click the "ThisWorkbook" entry in the left-hand column (the one beneath 'VBAProject (your_workbook_name_here)'.
    4. Paste the code below into the right-hand pane.
    5. Save and close the workbook.
    6. The next time you open the workbook (either manually or via Task Scheduler), the code should run immediately (as long as your macro security is set to low). You may want to put in a test row to make sure it runs.
    Please Login or Register  to view this content.
    Hopefully that works for you.

  7. #7
    Registered User
    Join Date
    06-08-2008
    Posts
    10

    Smile

    thank you paul for such a detailed and step by step solution. that was very helpful.

  8. #8
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    My pleasure, glad to help out.

  9. #9
    Registered User
    Join Date
    06-08-2008
    Posts
    10
    Hello Paul

    I have tested it out and it works very well. thank you once again ! If I wanted to add another email address in col E to be notified where would I have to make a change in the code ? I tried few things but it didnt really work.

    thank you

  10. #10
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    You should be able to make the following change (see code in red):
    Please Login or Register  to view this content.
    Semi-colons are typically valid as e-mail address separators, however if your default is something else (a comma, for example), change the semi-colon to that character.

  11. #11
    Registered User
    Join Date
    06-08-2008
    Posts
    10
    Hello Paula

    thank you so much

  12. #12
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    Quote Originally Posted by Glimmer View Post
    Hello Paula
    I suppose I've been called worse.

    Glad I could help out again.

  13. #13
    Registered User
    Join Date
    06-08-2008
    Posts
    10
    oops Paul, sorry that was a typo !

  14. #14
    Registered User
    Join Date
    08-16-2010
    Location
    Jaipur, India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Email to notify expiry date

    Hi Paul,

    This code worked great for me.Thanks a lot!!
    Though I have modified it a bit and wish to modify a little more.

    Could you suggest on following:
    1. Can this code be made to work - without starting the outlook in background? - as I see this code should work without starting the outlook - but it doesn't seem to work for me.
    2. If I wish to use my SMTP mail server , is there any provision in the VBA to code for SMTP send mail - like we do in code?
    3. If I wish to consolidate some data - based on that expiry date criteria and then put into an Excel sheet and then mail the sheet itself as a report - would it be possible? I guess - Yes but I don't knwo how to do it.

    Many Thanks
    Naveen

  15. #15
    Registered User
    Join Date
    03-08-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Email to notify expiry date

    Hi Paul,

    This code worked great for only one cell not for all expired cell. i want auto email genrate all expired contract nos after open the sheet. hear attached my sheet, so please help me for solve this problem,

    thanks and regards,
    Shri.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    10-18-2011
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Email to notify expiry date

    paul...did a great job...this is what i was searching for days...gr8 job..really nice attachment..how to move that send mail button to some corner..

  17. #17
    Registered User
    Join Date
    03-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Email to notify expiry date

    Hi Paul, Thank you for the code it is working for me I am having problem with date format , (excel 2010) Day, Month Year, it will send with 2/11/2012 or 1/04/2012 or 10/03/2012, but will need send 26/02/2012 do you have any ideas,

    Thankyou
    Kind Regards
    Darrel

  18. #18
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Email to notify expiry date

    Please take a moment to read the forum rules and then start a new thread. If another member's thread is relevant to your own question, post a link to it in your new thread.

    (Also, try searching the forum for the VBA "Format" command.)

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Send email when are date past
    By Hyperbole in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-19-2010, 10:05 AM
  2. Email once date reached on spreadsheet
    By djfatboyfats in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-13-2008, 10:47 AM
  3. Advanced Timesheet
    By DaKohlmeyer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-28-2008, 04:49 PM
  4. looping issues
    By Marcus Gee in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-09-2007, 11:42 PM
  5. Variable day expiry date
    By sujittalukder in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-28-2007, 08:38 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