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 Send Email Reminders From Excel to Outlook When Triggered?

Discussion in 'Business Applications' started by Adrian888, Apr 23, 2016.

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

    Adrian888 Thread Starter

    Joined:
    Apr 22, 2016
    Messages:
    30
    Hi All,

    I am new to VBA and although there are many links in the forum regarding the topics of using Excel to send Email reminders to Outlook, my requirement requires an additional option which i do not know how to program to make it work. I hope I can be assisted.

    I am currently using Outlook & Excel 2010, Windows 7.

    Using the attached test example, I have created a spreadsheet which is used daily. It requires a reminder email to be automatically sent out ONLY if the following is triggered.

    Row H (Send Reminder) must show YES, then it will only send on the date shown on Row G (Due Date). However, if Row H shows NO, it will not send even though Row G has Due Dates.

    The body of the reminder message would say:

    Subject: Reminder

    The project assigned to you under reference number, "cell D3" in the name of "from cell E3" for the confirmation date of "from cell N3" is now G3 - C3 days old.

    If this has been completed, please ignore.
     

    Attached Files:

  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,416
  4. Adrian888

    Adrian888 Thread Starter

    Joined:
    Apr 22, 2016
    Messages:
    30
  5. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    So from your workbook I have written the following code.

    Just a question

    What is meant to trigger this event?

    will it be

    1) When the workbook is open
    2) when something changes on the workbook
    3) after a period of time say every two hours

    Dim uRange
    Dim lRange
    Dim BCell As Range
    Dim iBody As String
    Dim iTo As String
    Dim iSubject As String
    Dim DaysOverdue

    Sub SetEmailParams()

    Set uRange = Sheet1.Range("H3")
    Set lRange = Sheet1.Range("H" & Rows.Count).End(xlUp)

    iBody = Empty
    iSubject = Empty
    iTo = Empty

    For Each BCell In Range(uRange, lRange)

    If BCell.Value = "YES" Then

    If DateDiff("d", Format(Now(), "dd/mm/yyyy"), Format(Range("G3"), "dd/mm/yyyy")) <= 0 Then

    DaysOverdue = DateDiff("d", Format(BCell.Offset(0, -5)), Format(BCell.Offset(0, -1)))

    iTo = BCell.Offset(0, -5).Value
    iSubject = "Reminder"
    iBody = "The project assigned to you under reference number " & BCell.Offset(0, -4) & " in the name of " & BCell.Offset(0, -3) & " for the confirmation date of " & BCell.Offset(0, 6) & " is now " & DaysOverdue & " days old."

    SendEmail
    End If

    End If

    Next BCell

    End Sub

    Private Sub SendEmail()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
    .To = iTo
    .CC = ""
    .BCC = ""
    .Subject = iSubject
    .Body = iBody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Display 'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    End Sub
     
  6. Adrian888

    Adrian888 Thread Starter

    Joined:
    Apr 22, 2016
    Messages:
    30
    Thanks DA for your assistance. Greatly appreciated.

    The trigger would be when the workbook is opened.
     
  7. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Hi Adrian,

    No worries mate anytime!

    I have attached the file with the code, but will also paste the code below for everyone elses benefit too. Hope this helps.

    When you open the workbook (you may have to enable macros) it will go through the list and start to generate emails if 'Send Reminder' is set to 'YES' and the due date has past the 'Assigned Date'

    *****NOTE****

    Currently this code will generate and DISPLAY the emails, if you want them to automatically send then please change '.Display' to '.Send'

    Module Code
    Code:
     Dim uRange
    Dim lRange
    Dim BCell As Range
    Dim iBody As String
    Dim iTo As String
    Dim iSubject As String
    Dim DaysOverdue
    
    Public Sub SetEmailParams()
    
    Set uRange = Sheet1.Range("H3")
    Set lRange = Sheet1.Range("H" & Rows.Count).End(xlUp)
    
    iBody = Empty
    iSubject = Empty
    iTo = Empty
    
    For Each BCell In Range(uRange, lRange)
    
    If BCell.Value = "YES" Then
    
    If DateDiff("d", Format(Now(), "dd/mm/yyyy"), Format(Range("G3"), "dd/mm/yyyy")) <= 0 Then
    
    DaysOverdue = DateDiff("d", Format(BCell.Offset(0, -5)), Format(BCell.Offset(0, -1)))
    
    iTo = BCell.Offset(0, -6).Value
    iSubject = "Reminder"
    iBody = "The project assigned to you under reference number " & BCell.Offset(0, -4) & " in the name of " & BCell.Offset(0, -3) & " for the confirmation date of " & BCell.Offset(0, 6) & " is now " & DaysOverdue & " days old."
    
    SendEmail
    End If
    
    End If
    
    Next BCell
    
    End Sub
    
    Private Sub SendEmail()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    With OutMail
    .To = iTo
    .CC = ""
    .BCC = ""
    .Subject = iSubject
    .Body = iBody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Display 'or use .Send to automatically send without displaying
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    End Sub
    
    
    Code for 'ThisWorkbook"

    Code:
    Private Sub Workbook_Open()
        SetEmailParams
    End Sub
    
     

    Attached Files:

  8. Adrian888

    Adrian888 Thread Starter

    Joined:
    Apr 22, 2016
    Messages:
    30
    It works perfectly. May i trouble you for one more item. Some kind of record in the spreadsheet row showing when the date/time it was sent, please.
     
  9. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    No problem, what I have done is added 'Reminder Sent' in column 'P' so when a reminder is sent, it will time/date stamp that row with the time and date it was sent.

    only one line of code was added to 'SetEmailParams'

    Updated file also attached

    Code:
    BCell.Offset(0, 8).Value = Now()
    Hope this helps
     

    Attached Files:

  10. Adrian888

    Adrian888 Thread Starter

    Joined:
    Apr 22, 2016
    Messages:
    30
    Excellent. Thanks again.
     
  11. Adrian888

    Adrian888 Thread Starter

    Joined:
    Apr 22, 2016
    Messages:
    30
    Hi All,

    I hope DA can help me again. I thought i had this resolved until my co-workers asked me to add or re-arrange some columns. I tried editing the macro to accommodate the change but nothing happens as in no error message nor email notifications. I'm not sure whether i had coded properly. Please find attached the modified spreadsheet. Please note that the workbook only shows February but ultimately, it would have worksheets for each of the remaining months.
     

    Attached Files:

  12. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Hi,

    There we multiple issues with this workbook which I have now resolved.

    The key issues were

    1) If column 'M' was 'YES' but column 'L' was empty then it would throw an error
    2) Because you have changed the column arrangements the email body was not being constructed correctly

    Please see attached amended workbook.

    Please let us know if it works
     

    Attached Files:

  13. Adrian888

    Adrian888 Thread Starter

    Joined:
    Apr 22, 2016
    Messages:
    30
    It works perfectly. Thanks for the fix.

    As for:
    Set uRange = Sheets("Feb").Range("M7")
    Set lRange = Sheets("Feb").Range("M" & Rows.Count).End(xlUp)

    Do i need to create a macro for each of the other months for & their respective worksheet?
     
  14. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Hi,

    not really this can be modified so that one block of code can work on many different worksheets, as long as they have the same layout.

    I will have a look at this later on tomorrow, shouldn't be too difficult
     
  15. Adrian888

    Adrian888 Thread Starter

    Joined:
    Apr 22, 2016
    Messages:
    30
    thank you again. And yes, the other worksheets would be the same layout.
     
  16. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    769
    Here is the modified version.

    I have created some new sheets named

    Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

    I copied the data from Feb into each sheet and modified some of the dates (had to overwrite your formulas). I then ran the code and it looped through each sheet, checked each item in column M and generated emails accordingly.

    I am using an array to set the name of each sheet that needs to be worked on. You can see this in Sub Setup. Note that each sheet name will have to be exactly as in the array list, I noticed you had something like 'Apr 2' this will not work as my code is right now, you will need to change the array list in Setup to match the sheet names in your workbook.

    Also note that if you want the emails to send automatically without being displayed, change the .Display to .Send

    Let us know how this works for you
     

    Attached Files:

  17. 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/1170299