Posts2Registration dateMonday June 11, 2018StatusMemberLast seenJune 12, 2018
Jun 11, 2018 at 05:59 AM
Blocked Profile -
Jun 12, 2018 at 04:50 PM
all I want to do is to create a code that will send me an e-mail when one of my docs will be expiring or expired. The problem is that there are 26 different documents for 54 different customers so it's about 1400 cells to cover. I have created a formula which converts dates into text (ex. =IF(AB46="","",IF(AB46<=0,"Expired",IF(AND(AB46<30,AB46>0),"Expiring",IF(AND(AB46>=30,AB46<1500),"Valid",IF(AB46>1500,"N/A"))))) so all cells are described as valid/expiring/expired. I've done some research and I've found quite a few solutions but need somebody to help me with last few bits because I'm stuck.
My code looks like that:
Dim BCell As Range
Dim EmailString As String
Set uRange = Sheet1.Range("C2")
Set lRange = Sheet1.Range("C" & Rows.Count).End(xlUp)
EmailString = Empty
For Each BCell In Range(uRange, lRange)
If BCell <= 3 Then
EmailString = EmailString & BCell.Offset(0, -2) & " is due to expire in " & BCell & " days" & vbCrLf
Sub SendMail(iBody As String)
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = iBody
On Error Resume Next
.To = "firstname.lastname@example.org"
.CC = ""
.BCC = ""
.Subject = "Documents due to expire soon"
.Body = strbody
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
All I want is to run that code every morning and getting an e-mail saying that "doc x for customer x will expire in x days and doc y for customer y is expired". Also if there is a chance I would like to add a timer to let it run every morning.
Can somebody let me know if that makes any sense and is doable?