Access Macros --Send Birthday Greetings to your Staff or Client using Access and Outlook

[ashish koul]

You having details like name , email id  , date of birth  etc  in a table  

And you would like to send them b'day Greetings . Snapshot below- 

Method 1-   Use image stored on the desktop 

Option Compare Database

Sub send_christmas_greet1()

Dim rs As Recordset

' tbl_clientnames is table name

Set rs = CurrentDb.OpenRecordset("SELECT  * FROM tbl_clientnames WHERE DAY([DOB]) = DAY(NOW()) AND MONTH([DOB]) = MONTH(NOW()) ", dbOpenDynaset)

Do While Not rs.EOF

Call sending_bday_greetings_method1(rs.Fields![Client Name].Value, rs.Fields![Client Email].Value)

rs.MoveNext

Loop

rs.Close

End Sub

Sub sending_bday_greetings_method1(nm As String, emid As String)

'To know how to set colors to text or align image in center in html Visit below links

'http://www.echoecho.com/htmltext06.htm

'http://www.w3schools.com/html/html_fonts.asp

' Send image stored in the desktop in the body of email

Dim olApp As Outlook.Application

Dim olMail As MailItem

Dim bdayimage As String

' set objects

Set olApp = New Outlook.Application

Set olMail = olApp.CreateItem(olMailItem)

' path of  image

bdayimage = "C:\Documents and Settings\user\Desktop\christmas greetings\happy_birthday1.PNG"

'************************ add  image and text

s = "<p> <p align='left'><font size='2' face='arial' color='blue'><i> Dear " & nm & ", </p>" & vbNewLine

s = s & "<p> <p align='CENTER'><font size='3' face='arial' color='red'><i> Wish you a very Happy B'day! </p>" & vbNewLine

s = s & "<left><p align='CENTER'><img src=""cid:" & Mid(bdayimage, InStrRev(bdayimage, "\") + 1) & """>" & vbNewLine

s = s & vbNewLine & "<left><p><p align='Left'><font size='3' face='arial' color='blue'><i>Regards<br>" & "Ashish Koul</p>"

' send email

    With olMail

        .To = emid

        .Subject = "Happy B'day"

        .Attachments.Add bdayimage

        .HTMLBody = s

        .Send 'or use .display

    End With

' Release objects

Set olApp = Nothing

Set olMail = Nothing

End Sub

Method 2-   Use image by passing web-link 

Option Compare Database

Sub send_christmas_greet2()

Dim rs As Recordset

' tbl_clientnames is table name

Set rs = CurrentDb.OpenRecordset("SELECT  * FROM tbl_clientnames WHERE DAY([DOB]) = DAY(NOW()) AND MONTH([DOB]) = MONTH(NOW()) ", dbOpenDynaset)

Do While Not rs.EOF

Call sending_bday_greetings_method2(rs.Fields![Client Name].Value, rs.Fields![Client Email].Value)

rs.MoveNext

Loop

rs.Close

End Sub

Sub sending_bday_greetings_method2(nm As String, emid As String)

' TOOLS -> RERENCES -> Microsoft Outlook

'To know how to set colors to text or align image in center in html Visit below links

'http://www.echoecho.com/htmltext06.htm

'http://www.w3schools.com/html/html_fonts.asp

' Send image directly from the weblink

Dim olApp As Outlook.Application

Dim olMail As MailItem

' set objects

Set olApp = New Outlook.Application

Set olMail = olApp.CreateItem(olMailItem)

'************************ add  b'day iamge and text

s = "<p> <p align='left'><font size='2' face='arial' color='blue'><i> Dear " & nm & ", </p>" & vbNewLine

s = s & "<p> <p align='CENTER'><font size='3' face='arial' color='red'><i> Wish you a very Happy B'day! </p>" & vbNewLine

' in img src change the weblink of image

s = s & "<left><p align='CENTER'><img src=""http://simplyizzy.files.wordpress.com/2012/05/happy_birthday1.png"">" & vbNewLine

s = s & vbNewLine & "<left><p><p align='Left'><font size='3' face='arial' color='blue'><i>Regards<br>" & "Ashish Koul</p>"

' send email

    With olMail

        .To = emid

        .Subject = "Happy B'day"

        .HTMLBody = s

        .Send 'or use .display

    End With

' Release objects

Set olApp = Nothing

Set olMail = Nothing

End Sub

Download File :----  https://www.box.com/s/14729714058b8ed16b88