Menu
Ask a question Report

Send mail if today is the birthday. [Solved/Closed]

Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 -
Excel Ask a question Report
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
See more 

5 replies

Best answer
Report
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
3
Thank you
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

Say "Thank you" 3

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6667 users have said thank you to us this month

Report
gteymurova
Posts
1
Registration date
Thursday June 18, 2015
Status
Member
Last seen
June 18, 2015
 -
Dear Rahul,

It was very very helpful. Many Thanks!

Could you please e-mail me this code by taking into consideration changing body text color, font and style.

Looking forward to getting your sooner reply!

BR,
Gunay
Report
-
I was requested to come up with a system for sending birthday messages in outlook and my search is over thanks to you.
Report Respond to Sidami_5147
-
Dear Rahul,

Please elaborate the steps also, how to use this code.

Thanks
Report Respond to ac3mark
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 20, 2019
 1402 > Sidami_5147 -
If you were to continue to read the thread, you would find the deployment instructions in posts following the one you commented on. Please understand, this is closed solved, so if you have more questions, it is important that you open your own thread, and reference this one.
Report Respond to bkdroid13
bkdroid13
Posts
12
Registration date
Wednesday May 29, 2019
Status
Member
Last seen
September 4, 2019
 1 -
Genious !! Its great post and help me a lot. please keep continue posting
Report
Posts
51344
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 5, 2019
 12343
0
Thank you
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
Report
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
0
Thank you
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.
Report
Posts
51344
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 5, 2019
 12343
0
Thank you
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
Report
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
0
Thank you
Thanks Ambucias,

I will post the same in FAQ.
Report
-
Hi Boss,

Thanks for this code. Which is sending mail in plain sheet. is it possible to select background from outlook or insert a picture in mail, will be more colorful.
Ask a question