Need a macro for sending mails wishing brithday

[Arunkumaramk]

Hi team,

I would like to write a macro which has to send birthday wishes on employees birthday.

I have the employee code, name and their birthday date in the spreadsheet.

Can any one please help me...

Thanks in advance

[Arunkumaramk]

Hi.. can anyone please help me out....

[KOKOSEK]

Check this thread. Is about checking dates and sending emails.
https://www.excelforum.com/excel-pro...s-reached.html

[Arunkumaramk]

Hi Kokosek.

Thanks for this one. But does this macro sends email automatically everyday for the one who has birthday??

Also if I need to add a standard image with the wishes, how do I add that, please guide me for that as well..

[KOKOSEK]

When you change this line:

Please Login or Register  to view this content.

or similar (depends which code you will choose)
for:

Please Login or Register  to view this content.

will be sending email t automatically for people with birthday set for today.

[Arunkumaramk]

Hi thank you for the quick response.

Well do I need to run the Excel every day so it sends mails automatically??

How do I add a pic in the mails.

[KOKOSEK]

Excel file has to be run manually. If it contains:

Please Login or Register  to view this content.

then after opening it will send email automatically.
Depends which options you choose here (comment or uncomment desired option):

Please Login or Register  to view this content.

it will send emails in background.

[KOKOSEK]

With picture.
Put into VBA module:

Please Login or Register  to view this content.

or check attached file.

[Arunkumaramk]

Hi thank u.

I believe whenever we open this Excel.it automatically sends out the mail.

If I would require the content font to be edited, do I need to change the context in the macro??

Also.In my database if I have 3 ppl having birthday in same day, does the macro automatically groups out.

I want this to be manually controlled instead of automatically sending. Can u put a button asking to send mails??

Would that be possible??

[KOKOSEK]

Ad.1. Yes
Ad.2. Yes
Ad.3. No. each person separately.
Ad.4. Remove Workbook_Open from VBA (check attached file) and button is already there (did You check attached file?)

[Arunkumaramk]

I checked the file. I didn't get wat u are mentioning as Ad 1.

Also where is the workbook_open??

[KOKOSEK]

Weird, Work fine for me.
Open attached file, press Alt + F11 (it will open VBA editor), you should see something as below:

Capture.JPG

if you remove code on right, it won't send automatically after opening and you have to use button

Captu222re.JPG

[Arunkumaramk]

Worked now. How do I insert the picture..

Do I need to save the default pic in a location and give the path in the code??

Also if I need to give a font color to happy birthday text. Wat should I do??

[KOKOSEK]

As you see in VBA, email body is created in html (check on internet about FONT, SIZE and COLOR)
You can create message as you want to.
I can't paste it here because firewall blocked html content.

EDIT: check attached file, with coloured font and picture inside.
Find in VBA code parts as below to edit:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[Arunkumaramk]

Hi thank u. I saved the pic in my g drive how do I call that pic in the macro.

Please tell me how should I call that

[Arunkumaramk]

There?? Any update?

[KOKOSEK]

I don't want to be rude but did you read my previous post?
Change path and name here:

Capture.JPG

[Arunkumaramk]

See I called like this saving the pic in the drive. But i didn't get pic in the mail. I gave the correct location


I saved the pic in g drive and gave the code as "G:\pic.jpeg" but it didn't work.

Also please note I am not well worst in vba. So apologies

[Arunkumaramk]

I finally fixed myself the picture.

If I want to add mail Id in cc or BCC should we need a separate code to be written in macro.

[KOKOSEK]

Birthday wishes with CC/BCC ?
OK as You wish:

Please Login or Register  to view this content.

[Arunkumaramk]

Thanks. But I am sorry. Where do I add the mail address.??

After value should I have the mail Id

[Arunkumaramk]

Do u mean to say that if add the cc in H column the "-X" needs to be replaced with H???

[Arunkumaramk]

I finally find out. Thank you for all your patience in teaching me out.

Thanks a ton !!!!

[KOKOSEK]

X is number which declare 'shift' in relation in base column, can be + or -.
For ex. if looping on column B, then offset (0,1) is column C and offset (0,-1) is column A, in general offset (row,column).

[KOKOSEK]

You welcome.
If you happy with solution please use Thread tools and mark thread as SOLVED.

[Arunkumaramk]

Hi just a small modification required.

I don't want to send separate mails if 3 of the employee birthday falls on same day. Can we group and send as one mail to all 3 of them if their birthday falls on same day...

Can u help me...

[KOKOSEK]

Sure I can:
Sorry that is on picture but due to html contains firewall blocked post.

Changes are:
- loop creates list of person with birthday and add them up together as 1 address
- creating email now is after loop just once.

Capture.JPG

[Keebellah]

I hope you guys don't mind but I suggest a minor correction to avoid sending the mail again if you open the file the same day:

Please Login or Register  to view this content.

[KOKOSEK]

I hope you guys don't mind but I suggest a minor correction to avoid sending the mail again if you open the file the same day:

Absolutely right. But maybe you did not spot that OP choose option to run it manually, anyway always can forget that he has already sent it :-)

BTW: why firewall blocked my post with this code and You were able to paste it?

[Keebellah]

Yes, even if the OP use the Manual button the mail is sent again if there is no control.
You paste the code using code tangs not html tags

[KOKOSEK]

yes as always - CODE /CODE
If I use HTML /HTML will be fine? Cheers

[Keebellah]

You use CODE for code HTML for HTML

[Arunkumaramk]

Thank u guys...

[Arunkumaramk]

I m unable to mark this as solved. Help me how do I mark that...

[Keebellah]

One other thing you can do is once the mail is sent set the date to the next year so you won't have to modify anything and it will mail again the next year