Automate e-mail notification and macro + include cell values

[david91]

Hi,

I am currently working on a sheet, where I wish to achieve the following:

• Whenever one or multiple items with status (Column E) In Progress and Awaiting info (ONLY!), has been in the sheet for exactly 7 or 14 days, I receive an e-mail notification which lists the items in the e-mail body (Column H - cell value)
• In the e-mail body, I only want the cells in Column H, where the Cells shows that the function is TRUE (ergo, shows value > 0 and is not blank) (in the example below, only H2 and H4 to appear in the e-mail).
• E-mail should only be sent if any of the Cells in Column H is NOT blank.
• Only one e-mail to be sent per day max. Eg. if I open the workbook in the morning, and the e-mail get's sent out, it will not be sent again if someone opens the file evening.
• The macro to automatically run once I open the workbook (e-mail to also send by itself once opened, but only once per day!)

I have the following sheet (name: "E-mail test") (let's take 09-May-2018 as today's date):

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Comp. Nr[/TD]
[TD]Comp. Name[/TD]
[TD]Comp type[/TD]
[TD]Date rec'd[/TD]
[TD]Status[/TD]
[TD]14 days[/TD]
[TD]7 days[/TD]
[TD]E-mail body[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]101[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]02-May-18[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]101 A 1 - 7 Days[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]102[/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]20-Apr-18[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]103[/TD]
[TD]C[/TD]
[TD]2[/TD]
[TD]25-Apr-18[/TD]
[TD]Awaiting info[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]103 C 2 - 14 Days[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]104[/TD]
[TD]D[/TD]
[TD]1[/TD]
[TD]01-May-18[/TD]
[TD]In Progress[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]105[/TD]
[TD]E[/TD]
[TD]3[/TD]
[TD]17-Nov-17[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please note that Column F-H are all hidden columns with the following functions which works perfectly:

• F: =IF(E2="Completed","",IF(AND(D2>0,OR(E2="Awaiting info",E2="In progress")),IF(TODAY()-D2=14,"14",""),""))
• G: =IF(E2="Completed","",IF(AND(D2>0,OR(E2="Awaiting info",E2="In progress")),IF(TODAY()-D2=7,"7",""),""))
• H: =IF(OR(F2="14",G2="7"),CONCATENATE(A2," ",B2," ",C2," ","-"," ",F2,G2," ","days"),"")

So far, I have the following code in VBA:

Dim xRg As Range​

Private Sub Worksheet_Change(ByVal Target As Range)​

On Error Resume Next​

If Target.Cells.Count > 1 Then Exit Sub​

Set xRg = Intersect(Range("H2:H6"), Target)​

If xRg Is Nothing Then Exit Sub​

If IsNumeric(Target.Value) And Target.Value > 0 Then​

Call Mail_small_Text_Outlook​

End If​

End Sub​

Sub Mail_small_Text_Outlook()​

Dim xOutApp As Object​

Dim xOutMail As Object​

Dim xMailBody As String​

Set xOutApp = CreateObject("Outlook.Application")​

Set xOutMail = xOutApp.CreateItem(0)​

xMailBody = "Please note that the following item(s) has been pending for (more than) 7 or 14 days," & vbNewLine & _​

"and requires action or follow up:"​

On Error Resume Next​

With xOutMail​

.To = "email@email.com"​

.CC = ""​

.BCC = ""​

.Subject = "ACTION REQUIRED! Pending items"​

.Body = xMailBody​

.Send 'or use .Display​

End With​

On Error GoTo 0​

Set xOutMail = Nothing​

Set xOutApp = Nothing​

End Sub​

I am now stuck with my goals listed above, and would appreciate all help I could get.
Please let me know if further clarifications are needed.

Many thanks.