Our YouTube video: How to create notifications or reminders in Excel became quite popular. People now want to know how to read out the message ‘Send Reminder’ and the credit card number. The VBA code below describes how to achieve the speak cells feature in MS Excel:
Private Sub Workbook_Open()
For Each cell In Range(“B2:B5”)
If cell.Value < Date + 3 And cell.Value <> “” Then
cell.Interior.ColorIndex = 3
cell.Font.ColorIndex = 2
cell.Font.Bold = True
Application.Speech.Speak (“Send reminder”)
End If
Next
End Sub

Private Sub CommandButton1_Click()
For Each cell In Range(“B2:B5”)
If cell.Value < Date + 3 And cell.Value <> “” Then
cell.Interior.ColorIndex = 3
cell.Font.ColorIndex = 2
cell.Font.Bold = True
Application.Speech.Speak (“send reminder to”)
‘cell.Value = cell.Offset(0, -1).Value
Application.Speech.Speak (cell.Offset(0, -1).Value)
End If

‘ Application.Speech.Speak (“cell.text”)

Next
SendReminderMail
End Sub

The click on the above command button activates the speak cells feature and also calls another macro or subroutine called ‘SendReminderMail. The complete VBA code for this macro is given below:

Sub SendReminderMail()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String

Set OutLookApp = CreateObject(“Outlook.application”)
Set OutLookMailItem = OutLookApp.CreateItem(0)

With OutLookMailItem
MailDest = “”
For iCounter = 1 To WorksheetFunction.CountA(Columns(4))
If MailDest = “” And Cells(iCounter, 4).Offset(0, -1) = “Send Reminder” Then
MailDest = Cells(iCounter, 4).Value
ElseIf MailDest <> “” And Cells(iCounter, 4).Offset(0, -1) = “Send Reminder” Then
MailDest = MailDest & “;” & Cells(iCounter, 4).Value
End If
Next iCounter

.BCC = MailDest
.Subject = “FYI”
.Body = “Reminder: Your next credit card payment is due. Please ignore if already paid.”
.Send
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub

Watch the training video to learn how we can send an email reminder automatically from an Excel Worksheet using VBA.

Tips for the SendMail code examples
Worksheetfunctions in VBA
COUNTA Worksheet Function
Speak Cells feature in Excel

Excel 2016 Power Programming with VBA (Mr. Spreadsheet’s Bookshelf)