Tech Support Guy banner
Status
Not open for further replies.

Excel Generate emails based on conditions of cell

1K views 8 replies 2 participants last post by  OBP 
#1 ·
I have some excel knowledge but am not too proficient in VBA. I am looking to generate emails based on the any of the conditions I have specified below for column G "Current Approved to Date"

Conditions:
1. =AND(($G9<Today()),($I9=""))
2. =AND(((J9-G9>3)),($I9="Applied for Ext."))
3. =AND((J9>G9),($I9="Active"))

If any of these conditions are met I would like an email to be generated.
(SIMILAR VBA) https://forums.techguy.org/threads/solved-automatic-email-alerts-using-excel.710581/page-2
 

Attachments

#5 ·
Yes there is an And and Or statements in VBA the syntax is like this

If (Cells(i, 4) > Date - 8) And (Cells(i, 4) <Date +1) Then

You can have as many Ands between the If and the Then as needed and you can extend the code on to the next line using the _ symbol.

That code would find any date between the current day and 7 days ago in row "i" ("i" is a counter) and column "D", you can use most mathematical symbols in VBA code and you can also use Range() instead of Cells().
 
#8 ·
Also, what if I have (3) "if...then" statements that need to be checked?

Would I need this part of the code after each statement?:
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
toList = Cells(i, 6)
eSubject = "Engineer " & Cells(i, 2) & " job list " & Cells(i, 1)
eBody = "Hello All, " & Chr$(13) & Chr$(13) & "Engineer " & Cells(i, 2) & " Is at customer " & Cells(i, 3) & " in " & Cells(i, 4) & " is " & Cells(i, 5) & Chr$(13) & Chr$(13) & "Many Thanks"
On Error GoTo Err_email
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top