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.

 

[karthik pullamsetty]

Hi Jim

Its working great !! thank you so much god bless you

Regards
Karthik

 

[cool0657]

Hi Jim,

When i'm trying to run your code in VB, it is giving the following error:

Run time eror : 1004 Application-defined or object-defined error

Please help.

 

[rbarrera05]

Hi Jim,

Good day to you. Thank you for your utmost contribution to this forum.
I follow the code you gave and paste it in module, however I get an error message when running:
Run-time error '1004':
Unable to get the Find property of the WorksheetFunction class

when I click debug it highlight this line of code:
Pos = WorksheetFunction.Find(" ", cell.Offset(, -1))

Please help me Sir.
Thank you.

 

[Worf]

You will get errors if the corresponding cell at column A does not contain a space, or if the loop is done at column A instead of B, as the code refers to one column to the left.
Expected contents at column A are full names like “Jim May”.

See also this thread:http://www.mrexcel.com/forum/excel-...automatically-happy-birthday.html#post4640192

Sub DoBirthdayRoutine()
Dim olApp As Outlook.Application, MItem As Outlook.MailItem
Dim cell As Range, Msg$, pos
Set olApp = New Outlook.Application
Sheets("Sheet1").Activate
For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    If Month(cell) = Month(Date) And Day(cell) = Day(Date) Then
        pos = WorksheetFunction.Find(" ", cell.Offset(, -1))
        Msg = "Dear " & Left(cell.Offset(, -1), pos - 1) & "," & 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 = cell.Offset(, 1)
            .Subject = "Happy Birthday!"
            .Body = Msg
            .Display
        End With
    End If
Next
End Sub

 

[sharonr]

Hi Jim May,

I am getting an error in this line of code:
Kindly help me.


If Month(cell) = Month(Date) And Day(cell) = Day(Date) Then

 

[Worf]

What is the error number and message?

 

[daiwuliz]

Hello,

This is great, thank you!
Can this be adjusted so that the email is sent to all recipients on the list, but the one who is celebrating their birthday? My goal is to set it up as a reminder to all team members, rather than send the happy birthday message.

Many thanks,
Dee

 

[Worf]

Welcome to the Forum

This code assumes names in column A, dates of birth in column B and mail addresses in column C.

Sub Reminder()
Dim cell As Range, person$, olApp As Outlook.Application, MItem As MailItem
person = ""
Set olApp = New Outlook.Application
Sheets("Sheet1").Activate
For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    If Month(cell) = Month(Date) And Day(cell) = Day(Date) Then
        person = cell.Offset(, -1)
        Exit For
    End If
Next
If Len(person) = 0 Then
    MsgBox "No birthday today"
    Exit Sub
End If
For Each cell In Range("a2:a" & Range("a" & Rows.Count).End(xlUp).Row)
    If cell <> person Then
        Set MItem = olApp.CreateItem(olMailItem)
        With MItem
            .To = cell.Offset(, 2)
            .Subject = "Someone is celebrating"
            .Body = person & "'s birthday is today!"
            .Display                                    ' or Send
        End With
    End If
Next
End Sub

 

[daiwuliz]

Thank you Worf!

Just one minor issue - the messages do not get sent automatically when I run the code. I get the email windows open and populated with email addresses, but they do not get sent. Can this be automated, please?

Many thanks

 

[daiwuliz]

Thank you Worf!

Just one minor issue - the messages do not get sent automatically when I run the code. I get the email windows open and populated with email addresses, but they do not get sent. Can this be automated, please?

Many thanks

By the way, I did try the .Send option instead of .Display - but it did not work..