Send E-mail & Comment based on Cell Value

BluOx

New Member
Joined
Aug 18, 2014
Messages
42
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! :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.

Code:
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

BluOx

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

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,270
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top