Results 1 to 5 of 5

Thread: Auto E-mail For Due Date Reminder Using Outlook

  1. #1
    Junior Member
    Join Date
    Sep 2013
    Posts
    3
    Rep Power
    0

    Question Auto E-mail For Due Date Reminder Using Outlook

    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.
    Attached Files Attached Files
    Last edited by samd007; 09-17-2013 at 08:39 AM.

  2. #2
    Junior Member
    Join Date
    Sep 2013
    Posts
    3
    Rep Power
    0
    Looks like no-one able to help me here?

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    18
    Rep Power
    0
    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

  4. #4
    Junior Member
    Join Date
    Sep 2013
    Posts
    3
    Rep Power
    0
    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.

  5. #5
    Junior Member
    Join Date
    Aug 2013
    Posts
    19
    Rep Power
    0
    Hi,

    Check below link, hope it will solve your problem


    http://www.excelfox.com/forum/f2/sen...n-status-1346/

Similar Threads

  1. Replies: 2
    Last Post: 07-02-2013, 02:36 PM
  2. Replies: 5
    Last Post: 06-11-2013, 08:15 PM
  3. Automatically send Birthday E-Mail in Outlook
    By s.ajay88 in forum Outlook Help
    Replies: 2
    Last Post: 03-12-2013, 06:28 PM
  4. Replies: 4
    Last Post: 03-01-2013, 10:48 PM
  5. Outlook Send Mail With Multiple Recipient and CC
    By noobtron in forum Excel Help
    Replies: 2
    Last Post: 10-31-2012, 07:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •