Excel Automatic Email on due date

Hi everyone

I hope all are fine and reading my message in the best of their health.. I am stuck with a job to do on various dates where i have to email those candidates whome progress reports are due, which due dates are mentioned in the sheet.

Can some body please help me to come up with a excel automatic mail solution that an email should be generated via outlook without opening the file or even if needed opening the file. The email should contain

Dear Student

Your progress report / proposal defense is due on (due date),

regards,


this email should be generated and sent before one month and one copy on the date on which it is due.. the cc list also needed to be grabbed from somewhere, where cc of this email should go.

I shall be very thankful if somebody can look the attached file and help

or if somebody can help me to edit this code according to my requirement i shall be obliged

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

or if somebody can help me to edit this code according to my requirement i shall be obliged

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

Hi Toofani,

i did not challange this issue for myself yet, but i think "Ron de Bruin" created some great codes that might could help you.

In think the particular topic for you = http://www.rondebruin.nl/win/s1/outlook/bmail9.htm
otherwize if that one should not help, check his whole list of codes on sending emails with outlook at: http://www.rondebruin.nl/win/s1/outlook/mail.htm

Regards
Philippe