Send email when any cell in a specific column reads below a certain number...

[afihavok]

Hey All,

I'm completely new to messing with any of this, so I really appreciate your patience and help! I'm attempting to set up a spreadsheet to track maintenance/warranties on gear at work. Basically we have a column for each item, type of warranty, length of warranty, and days left. At this point I have it set up to email me when the value of a specific cell in the Days Left column (column H) reaches 90 (90 days of maintenance left, time to renew). What I'm trying to do is get it to send an email when any cell value in column H hits 90. I've pasted below what I have so far. Instead of "H4" I'm trying to figure out how to say "anything in column 4".

Thanks!

Private Sub Workbook_Open()
Dim w As Worksheet, c As Comment
For Each w In ThisWorkbook.Worksheets
Select Case w.Range("H4").Value
Case Is <= 90
Set c = w.Range("H4").Comment
If c Is Nothing Then
send_mail w.Range("H4").Value, w.Range("I6").Value
ElseIf c.Text <> w.Range("H4").Value & " day reminder sent." Then
send_mail w.Range("H4").Value, w.Range("I6").Value
End If
End Select
Next w
Set c = Nothing
Set w = Nothing
End Sub
Private Function send_mail(numdays As Variant, eaddress As String)
Dim O, m
Set O = CreateObject("Outlook.Application")
Set m = O.CreateItem(0)
With m
.To = eaddress
.CC = ""
.BCC = ""
.Subject = "Maintenance Warning"
.Body = "There are one or more items with less than 90 days of maintenance left."
.Display 'or use .Display
End With
Set m = Nothing
Set O = Nothing
End Function

[Logit]

.
I already had something like this in my folder.

Paste this into a Routine Module :

Please Login or Register  to view this content.

Paste this into the Sheet Level module where the <=90 will appear in Column H:

Please Login or Register  to view this content.

[afihavok]

I really appreciate your help! I couldn't get the email to pop up when I first tried but I admittedly haven't spent much time on it. I'll keep working and let you know how it goes. Thanks again.

[Logit]

.
Look at this attached

[afihavok]

Thanks for the quick reply. Is there a way to get it to display the email when the workbook is opened? I had to run the macro to get it to pop up. (Changed email address in module.)

[Logit]

.
Paste this into the ThisWorkbook Module :

Please Login or Register  to view this content.

[afihavok]

Okay, I'm slowly getting there! It's now popping up the email but doing it every time, regardless of what I have in the range. I changed it to H2:H5 just for testing and put 1000 in each of those cells.

[Logit]

.
I've been exploring how to check (when the workbook is opened) if there is a value in Col H less than 90. Guess my old mind is tired. Haven't got there yet.

Understand this is not a professional endeavor for me ... just a hobby. I'll keep pluggin' away.

By the way, I didn't fully test that last file I sent. There was an error in it. Try this one.

[Logit]

.
Paste this macro in the ThisWorkbook module :

Please Login or Register  to view this content.

Paste this in the Routine Module below the email macro

Please Login or Register  to view this content.

Review the attached.

[afihavok]

That did it! I can't tell you how much I appreciate your help. I owe you a beer or 12. I'll let you know how the final product turns out. Thanks again!

[Logit]

.
Great ! Glad to help.

You'll need to keep the beer with all the bad stuff happening in Tx right now.