Creating Excel Email Alert For Expiring Dates

[Closed]
Report
Posts
2
Registration date
Monday June 11, 2018
Status
Member
Last seen
June 12, 2018
-
 Blocked Profile -
Hello,

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 = "emailaddress@xyz.com"
.CC = ""
.BCC = ""
.Subject = "Documents due to expire soon"
.Body = strbody
.Send
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?

Thanks

1 reply


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!).
Posts
2
Registration date
Monday June 11, 2018
Status
Member
Last seen
June 12, 2018

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
VBA is very heavy! Its simple tho!

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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!