Send Birthday Messages Automatically Using Excel VBA

Macro to send b'day message automatically

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  

Method 2 Add image in the email body from local drive

 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