Automatically Send Emails on Special Occasions in Excel

Well, its holiday season again and Christmas🎄🎄is approaching, followed by New Year 🎉. Many of us would be preparing to send emails to colleagues, clients, friends and family. I’ll show you how you can send emails to multiple recipients automatically (based on time and date of the year) from your Excel worksheet using a simple macro and Outlook.

Click to enlarge the image!

Send emails automatically in Excel using a VBA

Let us assume, you have a list of emails in your Excel worksheet. Its Christmas🎄 today, and when you open your Excel workbook, it should check the date, and if its 25th of December, extract emails from your worksheet and send emails to multiple recipients (bulk email).

We can automate the whole process.

Note: You must have Ms-Office Outlook installed in your computer. Therefore, please make sure you have the latest Outlook installed and configured.

Open an Excel file, a macro enabled file. Next, press Alt+F11 keys together to open the VBA editor. In the project explore, find ThisWorkbook and open it. Now, write the below code.

Remember: Before you start working with the macro, you must add a Reference of Microsoft Outlook 16.0 Object Library (version 12.0 or later). I have explained it here.

👉 I am sure you don't want to miss this article... Do you know you can send Emails from your Excel workbook with HTML table (with CSS Style) in Body? Its simple. Check out this article.

The Macro

Option Explicit

Private Sub Workbook_Open()
    ' Check date and month.
    If Day(Date) = 25 And Month(Date) = 12 Then
        Call sendChristmasWishes         ' Its Christmas.
    End If
End Sub

' Send emails to multiple recipients.
Private Sub sendChristmasWishes()

On Error GoTo ErrHandler
    
    ' Set Outlook object.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' Create email object.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)
    
    Dim myDataRng As Range
   
    ' We'll now set a range (to extract email ids).
    Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)
    
    Dim cell As Range
    Dim iCnt As Integer             ' Its just a counter.
    Dim sMail_ids As String         ' Variable to store recipients email ids.
    
    ' Run a loop to extract email ids from the 2nd column.
    For Each cell In myDataRng
        If Trim(sMail_ids) = "" Then
            sMail_ids = cell.Offset(1, 0).Value
        Else
            sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
        End If
    Next cell
    
    ' Clear the range.
    Set myDataRng = Nothing         
    
    Dim sBody As String            ' The body of the email.
    sBody = "May the spirit of Christmas fill your life and that of your family members with hope, positivity and Joy. " & _
            vbCrLf & "Merry Christmas and a very Happy new year in Advance. :-) " & _
            vbCrLf & vbCrLf & " Regards... Arun Banik"
            
    Dim sSubject As String         ' The subject of the email.
    sSubject = "Merry Christmas"
    
    With objEmail
        ' Assign all email ids to the property.
            ' In-addition, you can add CC and/or BCC. 
                ' See this page... https://www.encodedna.com/excel/send-email-from-excel-using-vba-and-outlook.htm#email_attachments_with_cc_bcc 
        .to = sMail_ids            
        .Subject = sSubject
        .Body = sBody
        .Display                   ' Display outlook message window.
        '.Send                     ' Send the email, when you are done.
    End With
    
    ' Clear all objects.
    Set objEmail = Nothing:    Set objOutlook = Nothing
ErrHandler:
    '
End Sub

The list of emails is in the 2nd column in "Sheet1". So, when you open the workbook, it checks the current system date and accordingly calls the procedure sendChristmasWishes().

You can also add attachments to your emails.

It can be any event. You’ll just have to change the date and month condition. For example, if you want to send emails wishing Happy New Year, then simply change this condition in the Macro.

If Day(Date) = 31 And Month(Date) = 12 Then

There’s no need to hardcode the body and subject of the email. You can store this data in a cell, extract it and assign it to the respective properties.

← PreviousNext →