Wish Happy Birhtday by Sending a Mail using Excel sheet

October 2017


I have a Excel Sheet in which details of member are recorded with there Date of Birth and e-mail id. and I want to wish them on there Birthday by mail. for this every time i have to check the sheet filter them for today's date and then send the mail individually.

This Macro helps you to do this for you. You have to just open your excel sheet and run the macro.


Suppose that person name in column C,
Date of Birth in column D,
and E-Mail id in Column E.

Sub bdMail()  
Dim OutApp As Object  
Dim OutMail As Object  
Dim cell As Range  
Dim lastRow As Long  
Dim dateCell As Date  

Application.ScreenUpdating = False  
Set OutApp = CreateObject("Outlook.Application")  
lastRow = Range("A" & Rows.Count).End(xlUp).Row  
On Error GoTo cleanup  
    For Each cell In Range("D2:D" & lastRow)  
    dateCell = cell.Value  
    If Day(dateCell) = Day(Date) And Month(dateCell) = Month(Date) Then  
            Set OutMail = OutApp.CreateItem(0)  
            On Error Resume Next  
            With OutMail  
                .To = cell.Offset(0, 1).Value  
                .Subject = "Happy Birthday"  
                ' Here put your Message
                .Body = "Dear " & Cells(cell.Row, "C").Value _  
                        & vbNewLine & vbNewLine & _  
                        "Many Happy Returns of the Day " _  
                        & vbNewLine & vbNewLine _  
                        & vbNewLine & vbNewLine & _  
                        "Cheers," & vbNewLine & _  
            End With  
            On Error GoTo 0  
            Set OutMail = Nothing  
        End If  
    Next cell  
    Set OutApp = Nothing  
    Application.ScreenUpdating = True  
End Sub
Published by RWomanizer. Latest update on April 15, 2011 at 06:19 AM by RWomanizer.
This document, titled "Wish Happy Birhtday by Sending a Mail using Excel sheet," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).