Tech Support Guy banner
Status
Not open for further replies.

Excel - Trigger Auto Email Depending on Date

2K views 1 reply 2 participants last post by  Keebellah 
#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 = "me@email.com"
Email_Send_To = "mergedcompany@email.com"
Email_Cc = "mergedassignedofficer@email.com"
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
 
See less See more
#2 ·
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,
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top