+ Reply to Thread
Results 1 to 12 of 12

Send Email when value less than help! :confused::confused:

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    81

    Send Email when value less than help! :confused::confused:

    Hi Guru's

    Ok so i have found a vba thats perfect for my needs and works ...Sort of....

    If a cell value is less than 3 it does send an email however my problem is i need it to send a email when M6 who's formula is ='Parts List'!L5 shows a value of less than 3 which for some reason this VBA refuses to do? infact it will only send an email if a standard cell shows a figure and not the result of a formula so what am i doing wrong?

    I also cannot just reference cell L5 on the parts list tab either as that is a formula too =Stock!K6-'Parts List'!K5 ..... the formulas work fine and simply display the value in numeric form but as mentioned the below VBA just wont work when referencing a formula result?

    PLEASE HELP!!!

    HTML Code: 

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,667

    Re: Send Email when value less than help! :confused::confused:

    .
    This works here :

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    81

    Re: Send Email when value less than help! :confused::confused:

    thanks for the reply , as before your code also work if the cell has a specific number entered but as m6 is the result of a formula then again nothing happens and no mail is opened.

    This is driving me nuts

    There must be a way to make this work

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,667

    Re: Send Email when value less than help! :confused::confused:

    I placed a formula in M6 ... =A1

    Then whenever I changed the Value in A1 it changed the value in M6 to match. So long as the value in A1 was less than 3, it worked here.

    The email macro is located in a regular module.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    81
    HTML Code: 
    This was my idea too but as mentioned M6 is a formula cell so i tried placing in O6 =M6 and then changing the line in the VBA to O6 alas it still doesn’t work for sending an email?! It’s so annoying!

    It seems you can only send an email for a value or range and not for a cell who’s results are a formula
    Last edited by JON_ROCKS; 01-17-2021 at 09:22 AM.

  6. #6
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    81

    Re: Send Email when value less than help! :confused::confused:

    Have a look at the attached file below, i have taken your spreadsheet and changed it to how i need it to give you a better idea of the problem.
    Attached Files Attached Files

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,667

    Re: Send Email when value less than help! :confused::confused:

    See if this change is what you are seeking :
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    81

    Re: Send Email when value less than help! :confused::confused:

    Now we are getting somewhere!!!!

    Logit you are a genius!!

  9. #9
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,667

    Re: Send Email when value less than help! :confused::confused:

    "Logit you are a genius!!"

    I slept at a Holiday Inn Express last night. *smile*

  10. #10
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    81

    Re: Send Email when value less than help! :confused::confused:

    Ok.... so new issue...

    I want to add this for multiple rows m6 m7 m8 etc... the =IF(('Parts List'!L5)<3,'Parts List'!L5,"") argument gives me the desired results but the email box will only show for the original M6.

    I adjusted the same argument moving down one cell each time so: =IF(('Parts List'!L6)<3,'Parts List'!L6,"") =IF(('Parts List'!L7)<3,'Parts List'!L7,"") etc...

    I created the following as a test

    HTML Code: 
    HTML Code: 

    now im sure i am doing something stupid but question is what?! and why is only m6 triggering the email?

  11. #11
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    81

    Re: Send Email when value less than help! :confused::confused:

    ARGHHH This is driving me MAD!! nothing works after M6 when it comes to sending an email!!

  12. #12
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    81

    Re: Send Email when value less than help! :confused::confused:

    Offered a solution as such below but i keep getting a 424 error?

    Please Login or Register  to view this content.

+ 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. Replies: 1
    Last Post: 10-03-2012, 10:32 PM
  2. [SOLVED] Macro executes but not giving results :confused::confused::confused:
    By Ganesh7299 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2012, 10:49 AM
  3. Confused by Add-ins
    By ions in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2006, 01:48 PM
  4. Confused
    By Karmen in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 02-24-2006, 04:27 PM
  5. confused,please help
    By shrutikhurana in forum Excel General
    Replies: 1
    Last Post: 02-03-2006, 08:35 AM
  6. Help!!!!im So Confused
    By james1bo in forum Excel General
    Replies: 5
    Last Post: 08-19-2005, 03:05 AM
  7. [SOLVED] I'm Confused........................................
    By Jonathan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-04-2005, 08:06 AM

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