Hi rjwalters,
Sorry for replying you so late...got stuck in some other work....Let's get into the business now...
What we can do is, we can subtract the due data from current date and check if it is =45 or not. If it is true(i.e., 45 days prior to due date) then email should fire.
Assuming, your due date is at B2( B1 is your header),45 days from now would be 12/26/2012; so plz place the same in B2; below here is your code:
Sub SendEmailAlrt()
Dim CheckDate As Long
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
'Calculate the difference between due date and current date
CheckDate = DateDiff("d", Format(Date, "mm/dd/yyyy"), Sheet1.Range("B2").Value)
'If the condition is true then send e-mail else do not d anything
If CheckDate = 45 Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi Dear" & vbNewLine & vbNewLine & _
"Your first line of message" & vbNewLine & _
"Your second line of message" & vbNewLine & _
"Your third line of message" & vbNewLine & _
"Your fourth line of message"
On Error Resume Next
With OutMail
.To = "replace this statement with the desired e-mail ID"
.CC = ""
.BCC = ""
.Subject = "Your Subject line will go here"
.Body = strbody
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Else: Exit Sub
End If
End Sub
Note: 1)Please remember to check "Microsoft outlook 12.0 object library" before running the code. To do the same press alt+F11 from key board, click on "Tools", select "reference" and look for "Microsoft outlook 12.0 object library" ( you might have different version number per the version of excel you are using).
2)In case you are checking the post tomorrow or day after, change the due date at B2 accordingly to fit it into "prior to 45 days category"
3)Remember to put your desired e-mail ID in .to= partof the code
Suggestion/thoughts: This is a basic code structure (not very dynamic in nature) I have shared with you. You might require lot of check points like for each and every entry the code should run at one go; to do the same we need to incorporate Looping concept; and the entries for which email is sent, you might need to have a marker in the corresponding cell saying "email sent" kind of stuff.
If these things are required, I would request you to share your sample workbook with us to provide you the more customized code.
Regards,
Kaushik
