Automatic outlook email on due date

[ash2017]

Hi ,

I am bit new to VBA/ Macros.
I am working on a tracker, where if the tasks/work not delivered, an email will be sent.

In column F there is Yes / No options. If selected no and delivery date is today an email will be sent mentioning the Log No. Employee and Date of receipt to a specific email address.

Excel attached.
Thank you

[kasan]

Hi,
You didn't show where you will keep e-mail adresses, so I got them in column G.
This should work with Outlook (if Outlook is opened).

Please Login or Register  to view this content.

[ash2017]

I found one post in another forum. My expectation is bit different. I want all the rows where the due date in column E is today will be sent to one email address. Since I am bit new to macros. I need help to modify the code.

Below code I copied from that forum post, not modified.
Module 1:

Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String

With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With

Sheets(1).Select
lRow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 2 To lRow
toDate = Replace(Cells(i, 3), ".", "/")
If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 7 Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

toList = Cells(i, 4) 'gets the recipient from col D
eSubject = "Project " & Cells(i, 2) & " is due on " & Cells(i, 3)
eBody = "Dear " & Cells(i, 1) & vbCrLf & vbCrLf & "Please update your project status."

On Error Resume Next
With OutMail
.To = toList
.CC = ""
.BCC = ""
.Subject = eSubject
.Body = eBody
.bodyformat = 1
'.Display ' ********* Creates draft emails. Comment this out when you are ready
.Send '********** UN-comment this when you are ready to go live
End With

On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Cells(i, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
End If
Next i

ActiveWorkbook.Save

With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub

This workbook code:
Private Sub Workbook_Open()
eMail
End Sub

[ash2017]

I am not sure if it is allowed to paste other forum link. My apology if it is against forum rule. below link-

https://forums.techguy.org/threads/a...-file.1129238/

[ash2017]

[QUOTE=kasan;4599537]Hi,
You didn't show where you will keep e-mail adresses, so I got them in column G.
This should work with Outlook (if Outlook is opened).

Please Login or Register  to view this content.

[/QUOTE

Thank you
Email address will be in 1 specific cell and there will be one 1 email address. Can you please advice where to insert this code. I already have another code in Sheet 1.

[ash2017]

[QUOTE=ash2017;4599559]

Hi,
You didn't show where you will keep e-mail adresses, so I got them in column G.
This should work with Outlook (if Outlook is opened).

Please Login or Register  to view this content.

[/QUOTE

Thank you
Email address will be in 1 specific cell and there will be one 1 email address. Can you please advice where to insert this code. I already have another code in Sheet 1.

I have included the recipient email address in the code and it's working. Is there any code to include email address, email body text from sheet 2 and the email contents are in table as in the excel, maybe html format.