Send mail if today is the birthday.Solved/Closed

Question

Hello, 

I have a Excel sheet containg profile of some employee like this:

Name           ID          Date of Birth
Rahul          335221     15/01/1986
Rose           325645     13/03/1980

I would like that if today is the birthday of the any employee an Happy Birthday mail should be send to them by outlook.

Could you please help me out.
 

System Configuration: Windows 7,
MS Office 2007

RWomanizer · Accepted Answer

Eureka!  

I Got the code for this problem  

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")  
OutApp.Session.Logon  
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"  
                .Body = "Dear " & Cells(cell.Row, "C").Value _  
                        & vbNewLine & vbNewLine & _  
                        "Many Happy Returns of the Day " _  
                        & vbNewLine & vbNewLine _  
                        & vbNewLine & vbNewLine & _  
                        "Cheers," & vbNewLine & _  
                        "Rahul"  
                .send  
            End With  
            On Error GoTo 0  
            Set OutMail = Nothing  
        End If  
    Next cell  
cleanup:  
    Set OutApp = Nothing  
    Application.ScreenUpdating = True  
End Sub  



Cheers, :) 
Rahul

Ambucias · Answer

Rahul,

You are a little genius!

Where on earth did you get this code?

Just curious, where in the code is the link to Outlook?

Best regards

RWomanizer · Answer

In the following lines:

to create outlook object :

Set OutApp = CreateObject("Outlook.Application")  
OutApp.Session.Logon  


and work with outlook in the following codes:


Set OutMail = OutApp.CreateItem(0)  
            On Error Resume Next  
            With OutMail  
            .................... 
so on.

Ambucias · Answer

Thank you very much.

This thread is precious and should live on for posterity.  I suggest you publish it in the faq even if it's not a frequently asked question.

I never thought that it was possible!  Just fantastic Rahul and congratulations!

Very best regards

RWomanizer · Answer

Thanks Ambucias,

I will post the same in FAQ.

Send mail if today is the birthday.

5 replies

On the same themes

Subscribe To Our Newsletter!