Creating Excel Email Alert For Expiring Dates

Imgladuc4me Posts 2 Registration date Monday June 11, 2018 Status Member Last seen June 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 uRange
Dim lRange
Dim BCell As Range
Dim EmailString As String

Sub GetExpirations()

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

End If

Next BCell

SendMail EmailString

End Sub

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
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Documents due to expire soon"
.Body = strbody
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

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?



1 response

Blocked Profile
Jun 11, 2018 at 05:24 PM
You need to create a POWERSHELL SCRIPT outside of excel to do that. What operating system is it?

Poweshell can BE SCHEDULED to run as a SCHEDULED TASK, is very thin when processing (unlike EXCEL disk operations!).
Imgladuc4me Posts 2 Registration date Monday June 11, 2018 Status Member Last seen June 12, 2018
Jun 12, 2018 at 03:13 AM
It's Windows 10 Pro. I didn't think about Powershell earlier which more familiar to me... Anyway, I'll give a try but I'm still curious about VBA solution.
Blocked Profile
Jun 12, 2018 at 04:50 PM
VBA is very heavy! Its simple tho!

Your posted code should work if you have an outlook client configured on the station!