Send E-mail & Comment based on Cell Value


New Member
Aug 18, 2014

Awhile back one of your users (Chris Mack I believe) was super patient with me and my limited VBA skills and helped me develop some macros for a to add/remove lines to track some paint emissions limits. That portion is working great but I've found a need to add another 'feature' because people aren't checking the totals page like they should. So here I am again....

I have a totals tab called Yearly VOC Totals, it has a total field for all 12 months (Cells L48-L59) and a yearly total located at L44. What I would like to have happen is when the totals get to >= 75%, >= 95% or >= 100% (I could also use a number range which is how it's set now) of the limit (there are also cells with the current % in M48-M49 & M44) a specific e-mail is sent to specific individuals (every month it will be the same people) with the current % and the month that went over. Additionally I would like a comment added to the cell stating the date the e-mail was sent and prevent another e-mail from being sent until it reaches the next threshold. Ideally this would all be done when the spreadsheet is closed (something I actually figured out how to do! :LOL:) and the user won't have to click send (it would be even better if I could spoof a from address but that's a nicety not necessity). Below you'll find the code I have so far, it's by no means complete as I've been trying to patch together what little VBA knowledge I have and the vast internet options.

I certainly appreciate any help or better options to accomplish this. :)

*Note: I'm aware that I have it set to .display instead of .send I did that so I don't spam myself with e-mails during testing. I also haven't created the code for 95%, 100% as I would rather only have one location to change recipients if that's possible.

Sub January_Status_75()
        Dim c As Comment
        ActiveWorkbook.Sheets(Array("Yearly VOC Totals")).Select
            If Cel.Value >= 1249.5 And Cel.Value <= 1582.69 Then GoTo send_January_75
            If Cel.Value >= 1582.7 And Cel.Value <= 1599.99 Then GoTo send_January_95
            If Cel.Value >= 1666 Then GoTo send_January_100
        End If
    End Sub
     Sub send_January_75(numdays As Variant, eaddress As String)
        Dim O, m
        Set O = CreateObject("Outlook.Application")
        Set m = O.CreateItem(0)
        With m
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "VOC Emissions Status"
            .Body = "You are at or have exceeded 75% of the EPA monthly emissions limit for January."
            .display 'or use .Display
        End With
        Set m = Nothing
        Set O = Nothing
    End Sub

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.


New Member
Aug 18, 2014
Anybody have any idea's? I think I've hit a bit of a roadblock....

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...