see here, it might help you, change "date" with a number as you need
http://www.excelforum.com/excel-gene...coming-up.html
Sub Button3_Click()
Dim ce As Range, i As Long
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
For i = 2 To Sheets("Calibration due").Range("c65536").End(xlUp).Row
If Cells(i, 3).Value >= 3 Then
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With Sheets("Calibration due")
strto = .Cells(i, 5).Value
strcc = "" '.Cells(i, 4).Value
strbcc = ""
strsub = "Expiry Notice"
strbody = "Hi" & " " & .Cells(i, 4).Value & vbNewLine & vbNewLine & _
"Your Eqt number" & " " & .Cells(i, 1).Value & " " & _
"Eqt Name" & " " & .Cells(i, 2).Value & " " & _
"is due to expire on " & .Cells(i, 3).Value & _
" " & "Please contact us ......" & _
vbCrLf & vbCrLf & "Thank you."
End With
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
'.Send
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End If
Next i
End Sub
here is column C where you add the number of days
Bookmarks