automatic birthday wishes using excel and outlook.

[nidhinkg]

i have a excel file which contains Name(column A),DOB(column B), & email address(column C),.

i would like to send birthday wishes mail automatically to the people according to their bday.

there may be "n" number of people on a particular day. i would like to send a single mail to all the people.i am using microsoft outlook.

When i open that excel file, it should pop up the New Message window of outlook with all email ids in the To. field with "Happy B'day" as subject and Message body as :"many more happy returns of the day & have a nice n wonderful day ahead".



i am very new to excel n have no idea about VB also. pls pls pls help me.....

nidhin.

 

[jim may]

While in your VBE window check to make sure, under Tools, References... you have Microsoft Outlook "CHECKED" before running the below Macro

This code sends a single e-mail to each person versus your request for 1 single e-mail to ALL

Post this code in a standard module, then run DoBirthdayRoutine.

Write back with questions.. Jim

Sub DoBirthdayRoutine()

Dim olApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Msg As String
Set olApp = New Outlook.Application
Application.ScreenUpdating = False
Sheets("Sheet1").Activate
LR = Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range("B2:B" & LR)
    If Month(cell) = Month(Date) And Day(cell) = Day(Date) Then
    Pos = WorksheetFunction.Find(" ", cell.Offset(, -1))
    FName = Left(cell.Offset(, -1), Pos - 1)
    Subj = "Happy B'day"
    EmailAddr = cell.Offset(, 1).Value
    Msg = "Dear " & FName & "," & vbNewLine
    Msg = Msg & vbNewLine & " Happy Birthday to you and many more happy returns.  Have a wonderful day." & vbCrLf & vbCrLf
    
Set MItem = olApp.CreateItem(olMailItem)
With MItem
    .To = EmailAddr
    .Subject = Subj
    .Body = Msg
    .Send
End With
End If
Next

Application.ScreenUpdating = True

End Sub

 

[madforgolf]

Hi Jim,

Thats a great piece which will help in our office.
I have tested it and it only sends to one if more than one DOB is the same day. It finds only the last person in list to match and send too.
Also i have it in a module, but do i need to place a button to activate it as it only runs when i run DoBirthdayRoutine? (When i open that excel file, it should pop up the New Message window ) ref to nidhinkg post?

Thnks for the vba, i certainly will use this at work to aid the group by sending a distribution mail listto those invloved.
Marty<o></o>

 

[jim may]

My Test List only has 2 persons. When I changed BOTH DOB's to 7/7/2012 -- BOTH got an e-mail as expected.

The 2 lines...

For Each cell In Range("B2:B" & LR)
If Month(cell) = Month(Date) And Day(cell) = Day(Date) Then

examines every cell from B2 to B? (the last non-blank cell) AND tests it as to whether the If statement is TRUE.

If you have 7 person's with the DOB of 7/07/???? - ALL 7 should receive an e-mail. You can step through the Code (using the F8 key)
to verify the code is working properly.

Good Luck,

Jim

 

[Mahesh_P]

Hi

Check this link
http://ashish-msexcel.blogspot.in/2011/07/sending-beautiful-birthday-messages-to.html

 

[madforgolf]

Hi Jim may,

Sorry my fault as i had not placed the headers on row 1, actually i only had names starting at B1
Thanks, is there a way for it to run when you open the excel file, rather than going into the editor or making a button?
Marty

My Test List only has 2 persons. When I changed BOTH DOB's to 7/7/2012 -- BOTH got an e-mail as expected.

The 2 lines...

For Each cell In Range("B2:B" & LR)
If Month(cell) = Month(Date) And Day(cell) = Day(Date) Then

examines every cell from B2 to B? (the last non-blank cell) AND tests it as to whether the If statement is TRUE.

If you have 7 person's with the DOB of 7/07/???? - ALL 7 should receive an e-mail. You can step through the Code (using the F8 key)
to verify the code is working properly.

Good Luck,

Jim

 

[jim may]

To your Thisworkbook module add

Private Sub Workbook_Open()
Call DoBirthdayRoutine
End Sub

 

[madforgolf]

Hi Jim may,

Thanks it worked a treat (after i remembered to change the date til todays dates )

Cheers for the help.
Marty

 

[jim may]

Thanks, so glad It worked for you.

Jim

 

[nidhinkg]

Hi Mr.Jim may..

Thanks a ton.....