+ Reply to Thread
Results 1 to 11 of 11

Send E-Mail if cell contains certain value

  1. #1
    Registered User
    Join Date
    05-23-2017
    Location
    BMS
    MS-Off Ver
    2007
    Posts
    7

    Send E-Mail if cell contains certain value

    I have got a spreadsheet with the following structure and many rows:

    Column A: Last name
    Column B: First name
    Column C: Further information
    Column D: Expiration date
    Column E: Days remaining (Formula: today - expiration date)
    .....
    Column J: E-mail-address
    Column K: E-mail content

    Now I want an e-mail (reminder) to be sent to the e-mail-address in J3 if the value in E3 (remaining days) reaches a certain value (e.g. 90 days). This E-Mail should just contain the text of K3.
    Equal process for all the single rows...

    Ideally the mails should be sent automatically if the document gets opened and the certain value (e.g. 90 days) has been reached.

    Many thanks in advance!

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Send E-Mail if cell contains certain value

    Now I want an e-mail (reminder) to be sent
    For Clarity; do you want an email sending as a reminder, or are you looking for an email with a reminder set in the calendar, or do you just want a reminder scheduled to the calendar?
    Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back

    Please also mark your thread as solved once it has been.

  3. #3
    Registered User
    Join Date
    05-23-2017
    Location
    BMS
    MS-Off Ver
    2007
    Posts
    7

    Re: Send E-Mail if cell contains certain value

    I just want an email to be sent, a reminder in the calendar is not necessary.

    By the way I am using Outlook 2007 and Excel 2007.

  4. #4
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Send E-Mail if cell contains certain value

    Try this code pasted in ThisWorkbook in Visual Basic (untested):

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-23-2017
    Location
    BMS
    MS-Off Ver
    2007
    Posts
    7

    Re: Send E-Mail if cell contains certain value

    Thank you for your answer, but the code is not working.

    I pasted it in ThisWorkbook, enabled macros, saved it... but nothing happened
    Or do I have to consider anything else to trigger the email?

  6. #6
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Send E-Mail if cell contains certain value

    Please upload your sample workbook with the code in place and I'll will troubleshoot for you.

  7. #7
    Registered User
    Join Date
    05-23-2017
    Location
    BMS
    MS-Off Ver
    2007
    Posts
    7

    Re: Send E-Mail if cell contains certain value

    I attached the spreadsheet with sample data.

    Would be great if you could set this up
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Send E-Mail if cell contains certain value

    Try this code. I made a mistake on the Set WS line which I've now amended, I've also added another if condition.

    Currently this will suggest an email for values in column E less than or equal to 90. You can change this as you see fit.
    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Send E-Mail if cell contains certain value

    Here applied in your example workbook.

  10. #10
    Registered User
    Join Date
    05-23-2017
    Location
    BMS
    MS-Off Ver
    2007
    Posts
    7

    Re: Send E-Mail if cell contains certain value

    Thank you so much, that's what I was looking for!

    Now I just got two further questions
    1) Is it possible that the email pop-up just appears once and not anymore after the email was sent?
    2) Can I insert several of these macros in one sheet? (for 180, 90, 60, 30, 0 days)

    So my intention is that I get a pop-up every time a cell in the column "remaining days" reaches one of the limits 180, 90, 60, 30 and 0 days.
    And if the email for the limit of 180 days was sent, the pop-up doesn't appear again before the limit of 90 days is reached.

    Thanks in advance!

  11. #11
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Send E-Mail if cell contains certain value

    We adjust the logic to all be completed in the one line of code for the different limits.

    This can also be set to Equal rather than less than so that the reminder only pops up when they each limit. That would save the need to add in a part to confirm whether the email has already been sent, however I could add something in for this however I would need to have an extra field added to the table.

+ 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. Send e-mail based on cell value
    By Dietcoke1953 in forum Excel General
    Replies: 2
    Last Post: 05-20-2017, 08:14 PM
  2. How to Send mail based on the values in a particular cell
    By sugaprasad in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2014, 06:01 AM
  3. Replies: 1
    Last Post: 03-11-2014, 12:24 PM
  4. Send to mail recipient, 1 worksheet, 2 cell ranges containing e-mail addresses
    By caladai in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-28-2013, 02:26 PM
  5. How to e-mail selected row and use e-mail address in a cell to send e-mail from excel
    By syedalamgir in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-27-2010, 02:15 AM
  6. Send mail to various addresses dependent upon cell value
    By Safecracker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2009, 04:10 PM
  7. [SOLVED] Automatically Send Mail From Cell
    By Suzanne in forum Excel General
    Replies: 1
    Last Post: 05-25-2005, 12:06 PM

Tags for this Thread

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