Tech Support Guy banner

Email when close to expiry date

862 Views 0 Replies 1 Participant Last post by  Loohsea
Hi all,

I had a thread open a few months ago in regards to sending an email everytime an excel sheet is open to let colleagues know of expiry dates that are 90 days away (or ones that have already passed expiry.

I have this currently written up in VBA, however it's not bringing up number of days till expiry in the email anymore -


Dim iTo, iCC, iSubject, iBody As String
Dim uRange
Dim lRange
Dim Bcell As Range
Dim GenerateEmail As Boolean

Public Sub CheckDates()

Set uRange = Sheets("Sheet1").Range("B2")
Set lRange = Sheets("Sheet1").Range("B" & Sheets("Sheet1").Rows.Count).End(xlUp)

iTo = "[email protected]"
iCC = "[email protected]"
iSubject = "Expiry"

iBody = "Dear team," & vbCrLf & vbCrLf & "Please note that the following are 90 days or less away from expiry." & vbCrLf & vbCrLf & "Some have already passed expiry and will need to be reviewed." & vbCrLf & vbCrLf

For Each Bcell In Sheets("Sheet1").Range(uRange, lRange)

If Bcell.Offset(0, 1) >= 0 And Bcell.Offset(0, 1) <= 90 Then
GenerateEmail = True
iBody = iBody & Bcell.Value & " is about to expire in " & Format(Bcell.Offset(0, 4), "##") & " days" & vbCrLf

ElseIf Bcell.Offset(0, 1) < 0 Then
GenerateEmail = True
iBody = iBody & Bcell.Value & " expired " & Format(Bcell.Offset(0, 4), "##") & " days ago" & vbCrLf

End If

Next Bcell

If GenerateEmail = True Then
End If

iTo = Empty
iCC = Empty
iSubject = Empty
iBody = Empty

End Sub


My excel sheet looks like this -

Azure Rectangle Font Parallel Screenshot

The email contains the Customer Name (B), and should be getting number of days from Nearest GMP Expiry Date (E)

Any idea where I'm going on? Keeping in mind that my knowledge of VBA is what I've learnt off the internet the past few days, haha.

Any help is appreciated.

See less See more
Not open for further replies.
1 - 1 of 1 Posts
1 - 1 of 1 Posts
Not open for further replies.