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

[ashish koul]

You have having details like name , email id  , etc  in a table. Snapshot below - 

And you would like to send them the Christmas, New Year , Diwali ,etc wishes to all . Snapshot below --

Here is the code-

Method 1 ----- Use the image stored on your desktop

Option Compare Database

Sub send_christmas_greet1()

Dim rs As Recordset

' tbl_clientnames is table name

Set rs = CurrentDb.OpenRecordset("tbl_clientnames", 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\christmas2.jpg"

'************************ 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> May joy and happiness snow on you May the bells jingle for you May Santa be extra good to you! Merry Christmas! </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 = "Merry Christmas"

        .Attachments.Add bdayimage

        .HTMLBody = s

        .Send 'or use .display

    End With

' Release objects

Set olApp = Nothing

Set olMail = Nothing

End Sub

Method 2 ----- Use the image from web by passing web-link of image  

Option Compare Database

Sub send_christmas_greet2()

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tbl_clientnames", 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> May joy and happiness snow on you May the bells jingle for you May Santa be extra good to you! Merry Christmas! </p>" & vbNewLine

' in img src change the weblink of image

s = s & "<left><p align='CENTER'><img src=""http://2.bp.blogspot.com/-RZDcC9NKT2A/Tvc9EvV7qAI/AAAAAAAAAFA/HmGMJc3tQJM/s1600/christmas2.jpg"">" & 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 = "Merry Christmas"

        .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/b5dad2aac908de7a8921