Auto E-mail For Due Date Reminder Using Outlook

**samd007** · 09-17-2013, 08:24 AM

I am facing a very specific problem.
I had made one excel to send automatic due date reminder mails via Groupwise (made with the help of your forums only).
The problem I am facing is as below:

[Also note, problem is not whether Groupwise or Outlook, problem is its generating multiple emails for each due item]

This is generating multiple emails and even the items not due are generating blank mails.

I would like to generate only one mail with different items different expiration dates.
After that group the identical addressees and compose one message with all the relevant data.

Right now its sending 3 mails as:

Mail 1: Please take notice of the following expiration date(s):

-Equipment A Job aaaaa expiration date : 19-Aug-12 -394 days.

-Equipment A Job aaaaa OVERDUE : 19-Aug-12 -394 days.

Sent at 17-Sep-13 11:11:04 AM

Mail 3: Please take notice of the following expiration date(s):

-Equipment C Job cccccc expiration date : 19-May-13 -121 days.

-Equipment C Job cccccc OVERDUE : 19-May-13 -121 days.

Sent at 17-Sep-13 11:11:04 AM

And the ones not due, going blank, i.e: the mail body (Mail 2) text reads:

"Please take notice of the following expiration date(s):

Sent at 17-Sep-13 11:11:04 AM"

What I wanted was a single mail with following in the mail body (Example):

" Please take notice of the following expiration date(s):

-Equipment A Job aaaaa expiration date : 19-Aug-12 -394 days.

-Equipment A Job aaaaa OVERDUE : 19-Aug-12 -394 days.

-Equipment C Job cccccc expiration date : 19-May-13 -121 days.

-Equipment C Job cccccc OVERDUE : 19-May-13 -121 days.

Sent at 17-Sep-13 11:11:04 AM"

Is this possible? Please check my VBA code & kindly help me resolve this.

**samd007** · 09-20-2013, 06:05 AM

Looks like no-one able to help me here?

**aju.thomas** · 09-20-2013, 03:35 PM

Hi,

please insert a column after column D (OverDue days) and put the formula to updating the overdue days (=TODAY()-D2)

Please find the code.

Code:

Option Explicit
Sub email_outlook()
    Dim due_date As Date
    Dim row_cnt As Integer
    Dim outapp, outmail, Mail_body, job As String
    Dim source As Range
    Dim cell As Range
    Set outapp = CreateObject("Outlook.Application")
    Set outmail = outapp.CreateItem(0)
    due_date = Format(Now(), "DD-Mmm-YY")
    Cells(1, 1).AutoFilter Field:=5, Operator:=xlFilterValues, Criteria1:="<=0" 'Array(0, "<=0")
    row_cnt = Cells(1).End(xlDown).Row 'ActiveSheet.UsedRange.Rows.Count
    Mail_body = "Please take notice of the following expiration date(s):"
    Set source = Range("A2:A" & row_cnt).SpecialCells(xlCellTypeVisible)
    For Each cell In source
        job = "Equipment Job " & cell.Value & " expiration date : " & cell.Offset(0, 3).Value & " - " & Abs(cell.Offset(0, 4).Value) & " Overdue days."
        Mail_body = Mail_body & vbNewLine & job
    Next cell
    Mail_body = Mail_body & vbNewLine & "Send at " & Now()

    With outmail
        .to = "test"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = Mail_body
        .Send
    End With
End Sub

**samd007** · 09-23-2013, 05:14 AM

I think my query was mis-understood. My problem is not related to calculating the overdue days. Its very well calculated. My problem is - rather than sending separate emails for each item, wanted one single consolidated mail.