none
Exel 2010 - sending automatic emails from outlook when date is due to expire RRS feed

  • Question

  • hi there,

    im working on a trainig matrix (excel 2010) and need to be able to send automated emails from the spreadsheet that details that training is due to expire.

    I need this macro / VBA to send an outlook 2010 email reminder 3 months prior to expiry, 2 months prior to expiry, and 1 month prior to expiry.

    this formula will need to carry accross multiple columns to capture a large variety of training courses and personnell.

    As per below im trying to have asic reminders issued 3,2 and 1 months prior to month of expiry.

    same with MRM, CT, i have a renewal date for this but want the reminders issued regarding when they need to refresh

    any help you can offer is greatly appreciated

    many thanks in advance,

    ROW    1          Column A        column F         column H        Column I        column J

    ROW    2          NAME               ASIC EXPIRY     MRM                 CT                    CT RENEWAL   

    ROW    3          GEOFF              JUL-17              DEC-13             DEC-13             DEC-15

    ROW    4          JOHN                OCT-16             APR-15             APR-15             APR-17


    • Edited by MAY.VDW Thursday, February 18, 2016 7:11 AM
    Thursday, February 18, 2016 7:11 AM

Answers

  • Hi, MAY.VDW

    According to your description, I suggest that you could use Range.AutoFilter Method (Excel) to filter 3,2 and 1 months prior to month of expiry, This example filters a list starting in cell A2:B3 on Sheet1 to display only the entries in which field two is equal to the string "JUL-2". The drop-down arrow for field two will be hidden:

    Worksheets("Sheet1").Range("A1:B3").AutoFilter field:=2, Criteria1:="JUL-2", VisibleDropDown:=False
    Set Rng = ActiveSheet.Range("A2:B3").SpecialCells(xlCellTypeVisible)

    For more information, click here to refer about Range.AutoFilter Method (Excel)

    then you could create and send email based on your have filtered Range, how to create email in Excel, refer to below code:

    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = xxx@xxx.com
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hello World!"
        .Send
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing

    For more information, click here to refer about OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook (Part 1 of 2)


    Friday, February 19, 2016 8:17 AM

All replies

  • Hi, MAY.VDW

    According to your description, I suggest that you could use Range.AutoFilter Method (Excel) to filter 3,2 and 1 months prior to month of expiry, This example filters a list starting in cell A2:B3 on Sheet1 to display only the entries in which field two is equal to the string "JUL-2". The drop-down arrow for field two will be hidden:

    Worksheets("Sheet1").Range("A1:B3").AutoFilter field:=2, Criteria1:="JUL-2", VisibleDropDown:=False
    Set Rng = ActiveSheet.Range("A2:B3").SpecialCells(xlCellTypeVisible)

    For more information, click here to refer about Range.AutoFilter Method (Excel)

    then you could create and send email based on your have filtered Range, how to create email in Excel, refer to below code:

    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = xxx@xxx.com
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hello World!"
        .Send
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing

    For more information, click here to refer about OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook (Part 1 of 2)


    Friday, February 19, 2016 8:17 AM
  • A gentle suggestion. One row per person - training requirement - next due date; don't do one row per person. Think about creating a database table that's normalized not something that is easy for people to read. You can always blow out something pretty for the managers by doing something like a pivot table but when you want a system to manage something like reminders, if you just keep extending columns as you add courses, the table will become difficult to manage, your code is more complex, and it's just messy.

    When I get into this kind of design, I tend to look for solutions that sit on servers and run scheduled code to do the work for me; I'm not required to open the sheet to get the macro to run, it just fires off on the server and my life is easy.

    Friday, February 19, 2016 5:01 PM