1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Automatic Email on due date

Discussion in 'Business Applications' started by toofanii, Oct 19, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. toofanii

    toofanii Thread Starter

    Joined:
    Oct 19, 2014
    Messages:
    31
    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
     

    Attached Files:

  2. toofanii

    toofanii Thread Starter

    Joined:
    Oct 19, 2014
    Messages:
    31
    or can someone just help me to edit this code, which is exactly what i want but with minor adjustment needed, as it is considering 7 days before the due date... and it is reading some data only


    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
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, welcome to the forum.
    I assume that you have not take the time to search this frum.
    This type of request has been asked many times with many excellent solutions .
    I suggest you try that and we're still here if you still need further assistance
     
  4. toofanii

    toofanii Thread Starter

    Joined:
    Oct 19, 2014
    Messages:
    31
    I have found some solutions but cant edit them as per my requirrmrnt thts why needed a help frm expert as i hav pasted a code also which is as per my req just minor adjustments needed
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    The code is not correct.
    The line 'toDate = Replace(Cells(i, 3), ".", "/")' should be a date .... but is reads 'ITP/F06/101'

    I think you should explain better and illustrate what you require.
    The file you have attached contains actual names and data, You should remove it and replace it with dummy data en non-linked data since I have to access to the data you refer to.
     
  6. toofanii

    toofanii Thread Starter

    Joined:
    Oct 19, 2014
    Messages:
    31
    Sir there are three column one student name second due date third email.. which shud b referred in the code.. i shared actual file because i need the accurate code for my particular file.. now what i want is

    In the due date column the code auto generate emails before one month to all those names whose dates are due..and send mails by outlook without opening along with cc to me and some other emails i specify in a file . The same process is needed when the due date actually arrives.. the same mail will go again .. and if possible the result in fourth column tht this person is emailed on date

    The code which i hav shared is extracted frm another file present on the forum but.its working is as per my req .. thts why shared here

    The file i shared is actually a part of my other workbook which updates frm other sheets
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I'm sure you know which column is the correct one.

    The vba code you posted is not correct but I'm sure you can correct it.

    I've attached the file with the macro
    All you have to do is make sure that you set the correct column in the correct place.

    The macro works (but now with the layout you mention)

    Instead of cells(lrow, 4 you can place the column letter, e.g. Cells(lrow, "D") for column D, etc.
     

    Attached Files:

  8. toofanii

    toofanii Thread Starter

    Joined:
    Oct 19, 2014
    Messages:
    31
    Sir i hav seeen ur code on another thread.. it was a vbs with schedular

    Cant it be the one for this file..

    How will this code run ?? On opening file .. nd how cn i check or verify it .. im not good with macros nd vba
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    If you indicate wcich are the columns and so I will correct the code.

    The trigger is up to you.

    If you, you can add a button on the ribbon or the QAT directly from your screen, and select macros and the macro you need.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Here is what I hope you want.

    There is a snapshot of one of the mail messages on the sheet to show the result.
     

    Attached Files:

  11. toofanii

    toofanii Thread Starter

    Joined:
    Oct 19, 2014
    Messages:
    31
    Right now im away frm the pc.. i give u a format of the email

    Dear (student name) your progress report is due in (due date)

    We look forward to see you with your progress report within the due date.


    Now there r column header by student name and due date .. these are the columns to use

    Two mails.will be sent at different stages

    1 One month before the due date / month
    2 On due date

    Both email.r needed to.be reported in two columns.. date of 1st sent mail and date of second sent mail

    The trigger.is needed to be auto by a vbs run with schedular.. your help is needed for tht too

    These email should also be sent to a specified list whi ch includes my emaill and some other .. i hope u got my point
     
  12. toofanii

    toofanii Thread Starter

    Joined:
    Oct 19, 2014
    Messages:
    31
    I wil check it tomorrow thanks in advance
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I'm sure you can solve this last part :)
     
  14. toofanii

    toofanii Thread Starter

    Joined:
    Oct 19, 2014
    Messages:
    31
    How to rep u :)
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I just hope it works like you require, we're here to try and help te best we can.
    The forum is always happy to receive any donation you can spare.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1135748

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice