Click to enlarge the image!
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.
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
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.
Well, that’s it. Thanks for reading. ☺