Macro to send b'day message automatically
Snapshot of email:
Snapshot of data:
Method 1 Add image using web link in the email body
Snapshot of email:
Snapshot of data:
Method 1 Add image using web link in the email body
Sub method1()
For i = 2 To 6
If Day(VBA.Date) = Day(Range("c" & i).Value) And Month(VBA.Date) = Month(Range("c" & i).Value) Then
Call sendbday(Range("a" & i).Value, Range("b" & i).Value)
End If
Next
End Sub
Sub sendbday(name_to As String, b_to As String)
Dim OutApp As Object
Dim OutMail As Object
Dim body_text As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
' Use weblink to add the image in the email body
body_text = "<p> <p align='left'><font size='2' face='arial' color='blue'><i> Dear " & name_to & ", </p>" & vbNewLine
body_text = body_text & "<p> <p align='CENTER'><font size='3' face='arial' color='red'><i> Wish you a very Happy Birthday! </p>" & vbNewLine
body_text = body_text & "<left><p align='CENTER'><img src=""http://simplyizzy.files.wordpress.com/2012/05/happy_birthday1.png"">" & vbNewLine
body_text = body_text & vbNewLine & "<left><p><p align='Left'><font size='3' face='arial' color='blue'><i>Regards<br>" & "Ashish Koul</p>"
On Error Resume Next
With OutMail
.To = b_to
.Subject = "Happy B'day!"
.htmlBody = body_text
.display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Sub method2()
For i = 2 To 6
If Day(VBA.Date) = Day(Range("c" & i).Value) And Month(VBA.Date) = Month(Range("c" & i).Value) Then
Call sendbday2(Range("a" & i).Value, Range("b" & i).Value)
End If
Next
End Sub
Sub sendbday2(name_to As String, b_to As String)
Dim OutApp As Object
Dim OutMail As Object
Dim body_text As String
Dim bdayimage As String
bdayimage = "C:\Users\Koul Ashish\Desktop\B'day wishes\img1.jpg"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
' Use weblink to add the image in the email body
body_text = "<p> <p align='left'><font size='2' face='arial' color='blue'><i> Dear " & name_to & ", </p>" & vbNewLine
body_text = body_text & "<p> <p align='CENTER'><font size='3' face='arial' color='red'><i> Wish you a very Happy Birthday! </p>" & vbNewLine
body_text = body_text & "<left><p align='CENTER'><img src=""cid:" & Mid(bdayimage, InStrRev(bdayimage, "\") + 1) & """>" & vbNewLine
body_text = body_text & vbNewLine & "<left><p><p align='Left'><font size='3' face='arial' color='blue'><i>Regards<br>" & "Ashish Koul</p>"
On Error Resume Next
With OutMail
.To = b_to
.Subject = "Happy B'day!"
.Attachments.Add bdayimage
.htmlBody = body_text
.display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Download working file here
You are amazing Mr. Ashish
ReplyDeleteThank you Mr. Ashish, the email is created on outlook, but it is not send. Is it possible to send automatically?
ReplyDeleteuse .send instead of .display
DeleteThank you very much
ReplyDeleteFinal question, is it possible to add a dynamic CC email address, so depending on the person of the list it will send the birthday email with cc to a different person.
you can add cc email in col d and then
Deletechange
Call sendbday2(Range("a" & i).Value, Range("b" & i).Value,Range("d" & i).Value)
Sub sendbday2(name_to As String, b_to As String, cc_to as string)
and
With OutMail
.To = b_to
.cc = cc_to
hye ashish can i have your contact number pls
ReplyDeleteHi Ashish
ReplyDeleteThanks for the help. I worked for me.
I have a query. Can I change the 'From' in the mail??
Hi Ashish,
ReplyDeleteThanks for your help..
Is there any way to send random pictures for the wishes..
Thanks in advance...
Sony
Thanks Ashish! This really works! Glad I came across this. I have two specific questions to ask you:
ReplyDelete1. How do I assign RGB colour code to the output
2. How can I auto run this query including the times when the workbook is closed?