+ Reply to Thread
Results 1 to 21 of 21

send Reminder Email if cell value reached by selective case through outlook mail

  1. #1
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    send Reminder Email if cell value reached by selective case through outlook mail

    Hello Experts,

    i need to send reminder email automatically if due date reaches before 30 days and 15 days by conditional formatting dates reaches the color in expiry date column. i checked the code before posting this of rondebruin but problem is i have two expiry date. Each employee having two cards if any one cards expiry then send reminder email to the employee and cc to others and date of email send to be register. pls refer the attachment for more information. by the way i am using excel & outlook 2010 & 2013.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    please any one help to solve this ...

  3. #3
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    What part do you need help with? Checking the expiration date, or sending the email?

    For checking the expiration date, I would do something like this (assuming that the expiration date is in B2).

    Please Login or Register  to view this content.
    Here is some basic code for the "15" days email. The 30 days email would be setup basically the same, but the contents or email recipients could be changed as needed.
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    HI SyracuseWolvrine,

    Thanks for your reply and idea about code of 15 and 30 and i tried it but i cant make it in one macro to run and how to display the headers to get all the expiry date. if possible can u upload a sample file which i attachment what i need requirement i mentioned.

  5. #5
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Removed this post - I couldn't get the attachment to work. See my reply below.

    I've re-uploaded your file with the code to run the 15 day check for the 1st card. Hopefully from this, you can piece together the rest of what you need.

    You will need to make sure Outlook is running when you try this. Also, you will need to make sure that the reference to the Outlook object is selected in the VBA Editor > Tools > References
    Last edited by SyracuseWolvrine; 05-31-2017 at 02:56 PM.

  6. #6
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Actually added attachment this time
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    SyracuseWolvrine ,

    super actually what i need you made it has table format and i trying myself modify your code to add 30 days too but i am not success its getting all the mails sending even more than 30 days too and how could i add a color in html table in outlook mail as same as my excel sheet and after sending mail to register the date in column I & J can u please try to solve this problem please.....

  8. #8
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    sathishsusa,

    For the 30 day reminder, I would copy the sub "Check15SendEmail" into a new subroutine, and rename it to something like "Check30SendEmail" ... all the changed lines are below

    Please Login or Register  to view this content.
    To add a date in Column I, add the following line before the "End If"

    Please Login or Register  to view this content.
    The code that generates (and fills in) the HTML Table is this line ... if you'd like to change background colors, you will probably need to include a bgcolor tag inside one of the < td> tags ... see http://html.com/attributes/td-bgcolor/ for more info

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Thanks so much for this code and i will try it to modify what you said and i let you know thank for your effort and taken your valuable time .

  10. #10
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

  11. #11
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail


  12. #12
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Hi ,
    i tried with your code with separate module its working fine but i am failure to make it one module its not working and after getting register of sending mail again if i run the module its again sending to same client please my urge to solve this problem please help me.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Anyone please solve this problems it will be highly appreciate to me!

  14. #14
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Sorry, I've been a bit busy the past few days. I'll try to take another look today or tomorrow.

  15. #15
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Hi SyracuseWolvrine,

    Happy to see you.. i understood you are bit busy Many Thanks..

  16. #16
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Hi sathishsusa,

    Here is a file that will, I think, do what you want.
    • 2 Subroutines
    • 1st one will check Driver Cards, for 15/30 days, and will send a reminder if one has not already been sent
    • 2nd one will check License, for 15/30 days, and will send a reminder if one has not already been sent
    • Buttons to trigger each email

    Disclaimer: Outlook must be open before starting either process.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Hi SyracuseWolvrine ,

    Wonderful Thanks a lot its working perfect you really made my day Thank you very much

  18. #18
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    I forgot one thing - The 2nd time you run the macro, any person who is within 15 days will have a 30 day email generated for them. This is because of the way the loop checks the dates - Each line is checked for (within 15 days AND no 15 day email sent) ... if anyone meets that criteria, it will send that email and move on to the next line. If not, it'll check for (Within 30 days AND no 30 day email sent), send that email, and move on.

    So, the first time you run it, it'll generate the 15 day emails. The next time, it'll see that the 15 day email has already been sent and skip ahead to check for the 30 day email.

    You can get around this by adding a date in the "Date 30 day email was sent" column. It can be any date, since the macro is only looking to see if there's something in that cell.

  19. #19
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    Yes you are right first its sending of 15 days email then second time i run its sending the 30 days email but why it should search and send first 30 days and 15 days email any loop that want to change or you have any idea to modify the code.

  20. #20
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    It's only an issue the first 2 times you ever run the macro.

    Assuming you run the macro every day

    Day 1: People who are within 15 days will get the 15 day email. This is correct.

    Day 2: People who are within 15 days will not get the 15 day email, because they got it yesterday. This is also correct. The tool would generate a 30 day email, which is correct, but you probably don't want to send, because you don't want them getting 2 emails in 2 days.

    Hence why I recommend putting fake dates in the "Date 30 day email was sent" for anyone who is within the 15 day window within the day or 2 that you start using the tool. That will prevent the 30 day email from being sent.

  21. #21
    Forum Contributor
    Join Date
    12-14-2016
    Location
    UAE
    MS-Off Ver
    MS OFFICE 2016 & 2019
    Posts
    154

    Re: send Reminder Email if cell value reached by selective case through outlook mail

    haa ok i got it.. thanks a lot and take your valuable time my friend

+ 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] Auto Mail reminder from Outlook
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-21-2016, 03:04 AM
  2. How to send Automated Email when specific cell value reached due date
    By grlinks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-15-2016, 12:22 AM
  3. Reminder mail from excel to outlook
    By burc67 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-03-2016, 09:19 AM
  4. excel reminder mail to outlook
    By burc67 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-03-2016, 08:30 AM
  5. Set outlook to send email when due date is reached
    By Camps1 in forum Excel General
    Replies: 5
    Last Post: 10-09-2014, 06:38 AM
  6. Macro to send birthday reminder via outlook email
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-01-2014, 10:29 AM
  7. Send reminder mail from excel using outlook
    By arunsny in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2011, 09:22 PM

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