Send E-mail & Comment based on Cell Value

[BluOx]

Hello,

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! ) 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
            Range("L48").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

 

[BluOx]

Anybody have any idea's? I think I've hit a bit of a roadblock....