Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jul 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to set email reminders from Excel

    Hi everyone,

    I'm new to this forum (new to all forums actually) so forgive me if I am not up to speed with general forum etiquette.

    I'm looking for help in how to set up email reminders for an Excel program I have set up. It's a very basic program with a list of policies I have in place for my job as a transport manager. These policies need to be updated throughout the year and I use the program to list when each one was last checked and when it is next due. I would like the program to send me an email reminder from outlook each time a due date is approaching.

    If anyone can help I would be most grateful and I'll gladly answer any questions you may have.

    Thanks in advance for your time.

    Shembo

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Shembo,

    If you could post a sample of your spreadsheet, code to create an email generated by certain criteria is very possible. Without knowing what and where the criteria is as well as the rules to create the email, it would be only a guessing game.

    Maud

  3. #3
    New Lounger
    Join Date
    Jul 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Driver policy file review schedule.xlsx

    Hi Maud, thanks for your reply and sorry for my late response. I'm not in the office every day.

    I've tried to upload my spreadsheet and all I get is the above. Does this work for you or am I doing something wrong?

    Shembo.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Shembo,

    Here is your revised file. The code will cycle through your list of policies looking at the date in the Next Review column. If that date is less than or equal to 30 days (customizable) from today's date, an email reminder will be generated. In the cell of the Next Review Date a comment will be added indicating that an email reminder was sent. There is a optional setting in the code that will enable you not to inhibit a second email reminder for the same policy the next time the code is run or you could also delete the comment for the date as well. The code will automatically do a check when a date change is made in column F. Note, your file has a class module for sending email (not customizable) and a standard module that you can modify to meet your needs. Or, you just leave as is. I also changed several of your dates for testing so make sure they are correct.

    HTH,
    Maud

    Shembo1.png

    Shembo2.png

    Modify the strings of code highlighted in blue to your needs

    In a standard module:
    Code:
    Public Sub EmailManagers()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim LastRow As Long, I As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
    '--------------------------------
    'CYCLE THOUGHT NEXT REVIEW DATES
    For I = 5 To LastRow
    '--------------------------------
    'DECLARE AND SET CLASS VARIABLES
        Dim Email As ClsTaskEmail
        Set Email = New ClsTaskEmail
    '--------------------------------
    'SETUP EMAIL
        If IsDate(Cells(I, 7)) Then
        If Date >= Cells(I, 7) - 30 Then
            If Email.OutlookCheck = False Then Exit Sub
            Email.EmailTo = "JohnDoe@gmail.com"
            Email.EmailCopyTo = ""
            Email.EmailBlindCopyTo = ""
            Email.EmailSubject = "Policy: " & Cells(I, 3)
            Email.EmailBody = "Dear Manager," & Chr(13) & Chr(13) & _
                    "Policy " & Cells(I, 3) & " will need to be reviewed on " & _
                    Cells(I, 7) & Chr(13) & Chr(13) & _
                    "Best Regard," & Chr(13) & _
                    "Your name goes here"
            Email.EmailReadReceipt = False
            Email.EmailAttachmentFile = ""
            Email.EmailCellComment = Cells(I, 7)
            Email.EmailCommentInhibit = True
            Email.EmailCreate
        End If
        End If
    Next I
    '--------------------------------
    'CLEANUP
    Set Email = Nothing
    End Sub
    In the worksheet module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.Columns("F")) Is Nothing Then
        EmailManagers
    End If
    End Sub
    Optional Customizations:
    1. Change the 30 in line 16 to the amount of days prior to the Next Review date the email will be generated.
    2. Change the email address in line 18 to whom the email will be sent
    3. Add email addresses to lines 19 and/or 20 if you want to CC or BCC
    4. Modify parts of the body of the email if desired in lines 22-26
    5. Attach a file if desired by placing the full path in quotes in line 28
    Example: Email.EmailAttachmentFile = "C:\Users\Maudibe\Desktop\Policy1.pdf"
    6. To allow repeat email reminders sent, change line 30 to False (not recommended)
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    pcarlston (2016-03-02)

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Did I mention that the code would only work for Outlook?

  7. #6
    New Lounger
    Join Date
    Jul 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Wow, this is brilliant! I didn't expect your reply to be so comprehensive, I thought you might just point me in the right direction. Having said that, I'm glad you didn't because I would have had no chance.

    Now this might be a silly question but I need to ask. The list of optional customizations that you have given me, do I change them here, where you have actually highlighted them in blue? Or do I change them on the revised spreadsheet that you've sent me? I have found the code in the visual basic menu but I'm terrified to touch it in case I mess something up.

    Thanks again Maud, this is much appreciated.

    Shembo.

    P.S. Just Outlook is fine.

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Shembo,

    Thank you. The customizations are done in the revised spreadsheet I have sent you. Follow these instructions:

    1. With the workbook open and viewing any sheet, press Alt-F11 to open the VB editor (see image)
    2. On the left upper pane VBA Project window expand Modules and you should see StandardGroupTextEmail
    3. Either double click it OR right click it then select view code.
    4. To its right, the large module window will show the code in the module. here is where you can make the changes I indicated in blue in my above post OR outlined with the red boxes in the image below.
    5. If there are quotation marks, make sure they remain after you make your changes.
    6. When you are finished, close the VB editor by clicking the red "X" at the top right then save the workbook. You can "save as" and then change the name if you like.
    7. You can add as many policies as you like. The code will adjust.

    Things you should not change:
    1. Keep your Last Review and Next Review dates in columns F and G respectively.
    2. Keep the policy names in column C and start with the first policy on row 5

    Enjoy,
    Maud


    Shembo1.png

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    pcarlston (2016-03-02)

  10. #8
    New Lounger
    Join Date
    Jul 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Runtime error.PNG

    Hi Maud,

    I'm sorry but I think I've broken something. After I tried updating the email details etc. now when I change the next review date it comes up with the error code above. I tried just deleting the entire workbook then reinstalling it from the link you gave me above but now that one is doing it too. Please help!

    Thank you.

    Shembo

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Shembo,

    When this message appears, click on debug button. You will be presented with the code and the code line that caused the error highlighted in yellow. Can you take a screenshot of that and post. There is a fix for whatever is causing the problem.

    Also, Please make sure that the change you made to the review date is in the correct date format. Also, make sure that outlook is started prior to opening the workbook.

    Please tell me what version of Windows and Office you are using.

    Maud

  12. #10
    New Lounger
    Join Date
    Jul 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    emc.PNG

    Hi Maud, Thanks again for your help. The screen shot you asked for is above and I am using Windows 7 Professional and Office 2010.

    Look forward to hearing from you.

    Shembo

  13. #11
    New Lounger
    Join Date
    Jul 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also Maud, will this program send the email reminder while the spreadsheet is closed or will it only send the email once I have opened it?

    Shembo

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Shembo,

    Thanks for the screen shot. Something is being sent to the class module that is not correct. I rechecked the file I posted and it runs fine. Let's make sure each of the following is correct in your workbook.

    1. The policies are listed in column C
    2. The first policy record begins on row 5
    3. Columns F and G have valid dates
    4. Outlook is started

    If all is correct and the error persists, when the highlighted line appears, repeatedly press F8 which will cycle through each line in the class module until you received an error message. Please take a screen shot of that, then press debug on the error message and take a screen shot of the highlighted code like you did above.

    Lastly, please reboot your computer, restart Outlook, and download a fresh copy of the file from post #4 and change a date in the Last Reviewed column to initiate the code.

    In the meantime, I will add additional error checking to the class module.

    Maud

  15. #13
    New Lounger
    Join Date
    Jul 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello again Maud,

    So sorry for not getting back to you sooner. For some reason your last email got sent to the junk folder even though all your others have come through fine.

    I have checked and followed all the things you laid out in your last email. The problem is happening again but I have noticed that it's only when it tries to send an email that the error occurs. I only noticed this because after downloading and rebooting, I forgot to open outlook. When I changed a date in the last review column, it just told me that Outlook wasn't open but didn't show the error message. When I opened Outlook and tried again, it sent the error message. Don't know if this helps.

    I tried doing the F8 thing. It scrolled through the list as you said but I'm not sure what I'm looking at, so I've posted the different screens it showed. I hope it means something to you.

    Let me know if there is anything more I can do to help.Look forward to hearing from you.

    Many thanks again.

    Shembo

  16. #14
    New Lounger
    Join Date
    Jul 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    error 2.PNGerror 3.PNGerror1.PNGerror4.PNGerror5.PNGerror6.PNG

    Sorry, these are the files I meant to attach. They come in order, Error 1, Error 2 ETC.

    Thanks.

Posting Permissions

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