none
Creating Excel Email Alert For Expiring Dates RRS feed

  • Question

  • Hi,

    all I want to do is to create a code that will send me an e-mail when one of my docs will be expiring or expired. The problem is that there are 26 different documents for 54 different customers so it's about 1400 cells to cover. I have created a formula which converts dates into text (ex. =IF(AB46="","",IF(AB46<=0,"Expired",IF(AND(AB46<30,AB46>0),"Expiring",IF(AND(AB46>=30,AB46<1500),"Valid",IF(AB46>1500,"N/A"))))) so all cells are described as valid/expiring/expired. I've done some research and I've found quite a few solutions but need somebody to help me with last few bits because I'm stuck. 

    My code looks like that: 


    Dim <g class="gr_ gr_53 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="53" id="53">uRange</g> 
    Dim <g class="gr_ gr_54 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="54" id="54">lRange</g> 
    Dim BCell As Range 
    Dim EmailString As String 

    Sub GetExpirations() 

    Set uRange = Sheet1.Range("C2") 
    Set lRange = Sheet1.Range("C" & Rows.Count).End(xlUp) 
    EmailString = Empty 

    For Each <g class="gr_ gr_48 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="48" id="48">BCell</g> In Range(<g class="gr_ gr_49 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="49" id="49">uRange</g>, <g class="gr_ gr_51 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="51" id="51">lRange</g>) 

    If BCell <= 3 Then 

    EmailString = EmailString & BCell.Offset(0, -2) & " is due to expire in " & BCell & " days" & vbCrLf 

    End If 

    Next BCell 

    SendMail EmailString 

    End Sub 

    Sub SendMail(iBody As String) 

    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim <g class="gr_ gr_50 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="50" id="50">strbody</g> As String 


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

    strbody = iBody 

    On Error Resume Next 
    With OutMail 
    .To = "emailaddress@xyz.com" 
    .CC = "" 
    .BCC = "" 
    .Subject = "Documents due to expire soon" 
    .Body = strbody 
    .Send 
    End With 
    On Error GoTo 0 

    Set OutMail = Nothing 
    Set OutApp = Nothing 

    End Sub 

    All I want is to run that code every morning and getting an e-mail saying that "doc x for customer x will expire in x days and doc y for customer y is expired". Also if there is a chance I would like to add a timer to let it run every morning. 

    Can somebody let me know if that makes any sense and is doable? 

    Thanks 
    Monday, June 11, 2018 12:04 PM

All replies

  • Can you provide a sample spreadsheet with the data?  It is probably easier to use VBA to see if it is expired rather than Excel formulas.  You can schedule tasks using Task Scheduler (search for it).
    Tuesday, June 12, 2018 1:09 PM