alert and send e-mail using excel

Microsoft Office excel 2007 home & stude...
June 22, 2010 at 19:40:25
Specs: Windows XP

i have an excel application that has expiration dates, now what i wanted to do is to send notification emails to the respective recipients lets say 60 days prior to the expiration date or just alert me 60 days before the expiry date and then i could just send them e-mails myself.

is there a way i will be able to do that?


See More: alert and send e-mail using excel

Report •

June 23, 2010 at 17:05:30

Look at this post, response 7 and
this post.

Both create and send e-mails from Excel.

Note the warnings about the use of passwords in the code and potential security risks - less of an issue if you are not distributing the worksheet to anyone else.

Have a go with the concepts in these posts and post back if you need more specific advice.

As to the warning, as an example, if Cell C13 has an expiration date in it, then this formula displays a warning if the date is less than 60 days from today. If it is 60 or more days, it returns a blank cell:
=IF(C13-TODAY()<60,"Send email","")


Report •

June 24, 2010 at 22:53:16
Hi Humar,

thank you for the response actually im new with vba what i wanted to do is to make a loop and validate if the expiration date in column E is 60 days away from the date today. and then send those expiring contracts to my recipient together with the company name in column A. i have a code here that sends me an email but i cant figure out how i can make it validate the expiring dates and do the loop that i want.

to be specific.. i just want to make a loop that will validate if my expiration dates are 60 days from today.
* send an email together with its respective company in column A

Sub sendemail()

Dim OutlookApp As Object
Dim myBodyText As String
Dim myLoop As Integer
Dim myRow As Integer
Dim myColumn As Integer
Dim myRecipient As String
Dim myFirstCellAdd
Dim myCounter As Integer
Dim myActiveCell As String

myCounter = 0

Cells.Find(What:="Expiration Date", After:=ActiveCell, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
Do Until ActiveCell.Address = myFirstCellAdd
myCounter = myCounter + 1
myCurrAdd = ActiveCell.Address
If myCounter = 1 Then myFirstCellAdd = ActiveCell.Address
myRow = ActiveCell.Row
ActiveSheet.Range("E" & myRow).Select

Application.ScreenUpdating = False

For myLoop = 1 To 255
If ActiveCell.Value = "" Then myBodyText = myBodyText & "" & ActiveCell.Value Else myBodyText = myBodyText & " " & ActiveCell.Value
If ActiveCell.Column = 1 Then myRecipient = ActiveCell.Value
If ActiveCell.Column = 256 Then myBodyText = myBodyText Else ActiveCell.Offset(0, 1).Select

Set OutlookApp = CreateObject("Outlook.Application")
With OutlookApp.CreateItem(olMailItem)
.Subject = "Expiring Contract of Clients"
.Body = "this is to notify you that " & ActiveCell.Offset(0, -1).Value & "thanks"
.To = myRecipient
myBodyText = ""
End With
Cells.Find(What:="Over Due", After:=ActiveCell, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
MsgBox (myCounter)
Application.ScreenUpdating = False

End Sub

thanks i hope you'll be patient with this one..

Report •

January 14, 2011 at 12:46:04
this is really help me. i do have a question do i ad me email and where do i add me email?

Report •

Related Solutions

January 17, 2011 at 00:03:39
Hi Mirta,

if you already have a database in your excel like mine, and you just want to create a macro to automatically send notification emails to your recipients before something expires (e.g. contracts). you have to know in what column is your expiration date located as well as the email address of your recipients.

if you could post a sample of your excel file here that would be great.

Report •

Ask Question