VBA to email based on cell value in column

[Bob_ipc]

Hey all! I'm looking for some help on a VBA code that will send an email any time a cell value in range A7:A50 changes to "Expires Soon". I am not well versed in VBA's and could use a helping hand please.

Thank You.

 

[Trevor G]

We would need a little more information such as: Is the email address held in the worksheet if so which column, what email system are you using i.e. Outlook or Lotus Notes as examples. What should the email contain?

 

[Bob_ipc]

Thank you for your quick response. Ok I am using Outlook, the email should be going to just my email anytime "expires soon" will show up in any cell in the column.

The "Expires Soon" will show up in column "A" any time a date in column "C", "F", "J", or "M" is close to today's date using a countif formula.

Thank you

 

[Trevor G]

Part A
Here is the code to send an email, in VBA use the Tools Menu and References then search down the list for Outlook and the version number you are using. I have commented the code.

Sub sendMail1A()
'Set the reference to use Outlook and the version you are using
'Use the Tools menu and References search the list for Outlook
Dim olOut As Outlook.Application
Dim olMail As MailItem
Set olOut = New Outlook.Application
Set olMail = olOut.CreateItem(olMailItem)
With olMail
.To = "Add your email address here"
.Subject = "Add your subject here"
.Body = "Add what text you want to show here"
.Display
End With
Set olMail = Nothing
Set olOut = Nothing
End Sub

Part B
Here is code to search column A (specific range adjust accordingly) for the text "Expires Soon". This code will currently show message boxes to identify which cells have that text. If this part seems OK then you can change the MsgBox to call the first routine.

Sub checkExpireSoon()
Dim aCell As Range, Rng As Range
Dim SearchFor As String

Set Rng = Range("A1:A25")

SearchFor = "Expires Soon"

For Each aCell In Rng
If InStr(1, aCell.Value, SearchFor, vbTextCompare) Then
MsgBox "Found in " & aCell.Address
End If
Next aCell
End Sub

 

[Bob_ipc]

Awesome thank you! will this send an email even if the program is closed? or just when it is opened? also do I need to run this as two separate VBA's? or is it possible to string them together?

 

[Trevor G]

The code as designed will only work when the workbook is open and you run the subroutines. You can combine them by calling them, also you could call them when the workbook is open as the workbook has an open event.

 

[Bob_ipc]

I got it to work as designed, however how would I be able to change it so that if I’m not in the workbook for several weeks that it will still send me an email even tho I had not opened the workbook. Is this possible?

 

[Bob_ipc]

Thank you so much for all the help, I'm sorry I was not very clear about when the email was to be sent. the workbook may be closed for an extended period of time, is it possible to have the email be sent while the workbook is closed?

 

[Michael M]

No.....the code will only be triggered from an open workbook, that holds that code !
BTW....How will the "expires soon" change if the workbook hasn't been opened ??

 

[Bob_ipc]

Thanks for the reply. "Expires Soon" changes with a formula. [=IF(COUNTIFS(G$5:AC$5,"Expire Date",G7:AC7,">="&A$6,G7:AC7,"<="&A$6+90),"Expires Soon","")]