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.

Excel - Trigger Auto Email Depending on Date

Discussion in 'Business Applications' started by PuissantKalEl, Oct 2, 2012.

Thread Status:
Not open for further replies.
  1. PuissantKalEl

    PuissantKalEl Thread Starter

    Joined:
    Oct 2, 2012
    Messages:
    1
    Hello Everyone,

    I am a beginner at using vba and I am trying to use excel to send an email automatically depending on a past due date. I have a an excel sheet that has over 200 companies and we require an annual report but everyone report is due at a different date. I am using conditional formating to change the color of the text depending on how long it has been since we have received the last report. If the last report is 366 days since it has been submitted then I would like an automatic email to be sent.


    For conditional formatting i used this "=(TODAY()-365)"

    For email i am using;

    Sub Send_Email_Using_VBA()
    Dim Email_Subject, Email_Send_From, Email_Send_To, _
    Email_Cc, Email_Bcc, Email_Body As String
    Dim Mail_Object, Mail_Single As Variant
    Email_Subject = "Trying to send email using VBA"
    Email_Send_From = "[email protected]"
    Email_Send_To = "[email protected]"
    Email_Cc = "[email protected]"
    Email_Bcc = ""
    Email_Body = "Your annual report is Due"
    On Error GoTo debugs
    Set Mail_Object = CreateObject("Outlook.Application")
    Set Mail_Single = Mail_Object.CreateItem(0)
    With Mail_Single
    .Subject = Email_Subject
    .To = Email_Send_To
    .cc = Email_Cc
    .BCC = Email_Bcc
    .Body = Email_Body
    .send
    End With
    debugs:
    If Err.Description <> "" Then MsgBox Err.Description
    End Sub

    If possible i would like to use an email template that excel would place the values specific to the company's name, owner, and their due date and then send the email to the email address on the specific cell.


    Thank you so much in advanced,

    PuissantKalEl
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi welcome to the forum.

    You already have the send_email_using_vba, weel you will need something that checks all the cells where the due date is x days old and call this macro

    Sub CheckSomething()

    << here you will have to put the code that checks each rows in the
    << date column and if the condition is met then call the send email macro

    End Sub

    Since I have no idea of your sheet's layout you will have to do your own homework

    I suggest you also tell us what version of Excel you're using,
     
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/1071150

  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