How to send email reminder automatically from Excel Worksheet using VBA

Leather Credit Card Holder

Leather Credit Card Holder

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

How to send email reminder automatically from Excel Worksheet with VBA

How to send email reminder automatically from Excel Worksheet with VBA

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)

28 thoughts on “How to send email reminder automatically from Excel Worksheet using VBA

  1. Jamel

    Hi Dinesh.
    I tried your code in excel 2013 but I get a debugging error at following line.
    .BCC = MailDest

    it doesn’t seem to recognize this statement as existing on its own.

    Reply
    1. sibilesh

      use the code as
      .to = “the email id to whom you want to send”
      .CC = “The email id to whom to put CC”
      .BCC = “The email id”

      Reply
  2. ahmed

    could you tell me more details
    i wrote my syntax like that

    in workbook i wrote this
    Private Sub Workbook_Open()
    For Each cell In Range(“M6:M12″)
    If 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

    and in module i wrote this
    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(12))
    If MailDest = “” And Cells(iCounter, 12).Offset(0, -1) = “Send Reminder” Then
    MailDest = Cells(iCounter, 12).Value
    ElseIf MailDest “” And Cells(iCounter, 12).Offset(0, -1) = “Send Reminder” Then
    MailDest = MailDest & “;” & Cells(iCounter, 12).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

    >>>i need from you to tell me where i wrote your code.

    Reply
    1. Md.Shakil Ahmmed

      Mr: Takyar
      Good Day!!!
      I want to Add Cell Reference in Mail Body Where Few msg is in cell C2 To C 100 are different .Body = “Reminder: Your next credit card payment is due. Please ignore if already paid.””

      Please help me for find the best solution.

      Reply
  3. Matt

    I have tried to do this and I a runtime error ‘-2147467259 (80004005)’ when I try to use any row below 5. It says “there must be at least one name or contact group in the TO, Cc, or Bcc box.

    It works great for the first five rows but will not for anything below it tho everthing appears to be the same for them.

    I tried the debug it just Highlights the ‘.Send’ part in the Module

    please help

    Reply
    1. Bas

      I had the same issue which was caused by miss-spelling of “Send reminder”. It has to be exactly the same in the macro and the workbook

      Reply
  4. hussein

    I have tried to do this and I a runtime error ‘-2147467259 (80004005)’ when I try to use any row below 5. It says “there must be at least one name or contact group in the TO, Cc, or Bcc box.

    It works great for the first five rows but will not for anything below it tho everthing appears to be the same for them.

    I tried the debug it just Highlights the ‘.Send’ part in the Module

    please help

    Reply
  5. Harish

    how to send email reminders, instead of giving instant message, like due for payment , i need to send the actual content in cell like, invoice, clients name payment due. how we can do vba.

    Reply
    1. Lisa Morris

      Have you had any luck on this question? I want to say, for example, “Your First Aid certificate is due to renew” or “Your Confined Space certificate is due to renew”.

      Reply
  6. Karthik

    Can you guide me on how to attach particular cell data respective to the cell containing the email address. For example, How do i do it – if i want to send the respective credit card number of the client along with the reminder mail.

    Reply
  7. Chris

    Hello, I am the same as Hussein with the error code. But my error message reads –

    ‘Run-time error’-2147467259 (80004005)’:
    We need to know who to send this to. Make sure you enter at least one name.

    If I have any rows with ‘send reminder’ in then it works. But if they are blank so no ‘send reminder’ then I get the error message.

    Also can you show me how to set for two conditions and send out emails accordingly.

    ‘Your membership is due for renewal’
    ‘Your membership is out of date’
    So two different emails will be sent to people when membership is due and then again for membership out of date. From the same spreadsheet.

    Reply
  8. Prakash Nair

    Dear Sir,
    I don’t know anything about VB code. But after some help from some friends like you on the web world, I managed to drum up the following code. It will send a reminder mail to the ID in column J, when the expiry date in ” E” is 30 days away. The code also put a “Y” in column “M” and details of the mail in “L”. It works fine. But I need one more function to be added to this code.
    Whenever the Expiry date is changed after the mail is been send, the “Y” in column “M” and the mail details in “L” should disappear. So that its ready again to send the reminder mail again for that item.
    Grateful if you could kindly help.
    The Code:
    Private Sub Workbook_Open()

    Dim i As Long
    Dim OutApp, OutMail As Object
    Dim strto, strcc, strbcc, strsub, strbody As String

    Set OutApp = CreateObject(“Outlook.Application”)
    OutApp.Session.Logon

    For i = 6 To Range(“E65536”).End(xlUp).Row
    If Cells(i, 13) “Y” Then
    If Cells(i, 5) – 30 < Date Then
    Set OutMail = OutApp.CreateItem(0)
    strto = Cells(i, 10).Value 'email address
    strsub = "item " & Cells(i, 1).Value & " is due on Due date " & Cells(i, 5).Value 'email subject
    strbody = "Dear " & Cells(i, 11).Value & vbNewLine & "Please raise TQ/Action the Item: " & Cells(i, 1).Value & vbNewLine & "P/N: " & Cells(i, 2).Value & vbNewLine & "After actioning Please send a reply to ALL" & vbNewLine & " Advising action taken to avoid duplication" & vbNewLine & " ALSO on receipt of new item Change the Expiry/check dates to new dates" & vbNewLine & " Thanks Nd Brgds" 'email body

    With OutMail
    .To = strto
    .Subject = strsub
    .Body = strbody
    .Send
    '.display

    End With
    On Error Resume Next
    Cells(i, 12) = "Mailed " & Now()
    Cells(i, 13) = "Y"

    End If
    End If
    Next

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub

    Thanks and Regards Prakash

    Reply
  9. Md.Shakil Ahmmed

    Hello
    Mr: Takyar

    This is Md.Shakil Ahmmed from Dhaka Bangladesh, Thanks for for your video tutorial its very helpful for me. But i have need to add credit card number which cell shown as send remainder into mail body but i can’t do this things.

    Requesting to you please provide any solution if have on this regards.

    Regards
    Md.Shakil Ahmmed

    Reply
  10. soumini

    Private Sub CommandButton1_Click()
    For Each cell In Range(“B2:B5”)
    If cell.Value < Date + 3 And cell.Value “” Then
    cell.Interior.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
    AFTER DOUBLE CLICK ON THE ‘MAIL REMINDER BUTTON’ ,, SHOWING THE CODING ONLY
    AFTER DEBUGGING IT’S NOT WORKING
    ERROR LINE – 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”)
    PLEASE HELP

    Reply
  11. Vickie

    Good evening Sir,

    I’m not able to get my worksheet to function correctly, Could You please allow me to reach out to you privately for further assistance. Please advise.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *