Pages

Tuesday, July 19, 2011

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

7 comments:

  1. Thank you Mr. Ashish, the email is created on outlook, but it is not send. Is it possible to send automatically?

    ReplyDelete
  2. Thank you very much

    Final 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.

    ReplyDelete
    Replies
    1. you can add cc email in col d and then

      change
      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

      Delete
  3. hye ashish can i have your contact number pls

    ReplyDelete
  4. Hi Ashish

    Thanks for the help. I worked for me.

    I have a query. Can I change the 'From' in the mail??

    ReplyDelete