Send Christmas , Republic Day, Diwali , New year wishes to your client or staff using Outlook and Access

If you want to send your clients , friends or staff Christmas , Republic Day, Diwali , etc wishes. Snapshot below

Christmas -

Republic Day -

And you have all the employees or client details in a access table (Table name Data).Snapshot

we can easily write a macro to send greetings to staff or client using outlook .

Download Macros

The first thing required is to choose the image from any online photo sharing websites like-
http://www.glitter-graphics.com or http://photobucket.com/ , etc. and pic its code to be used in macro

For example in above cases For Christmas greetings . I have taken image from below link

http://www.glitter-graphics.com/graphics/387296

Before find and replace

<a href="http://www.glitter-graphics.com"><img src="http://dl9.glitter-graphics.net/pub/788/788439qwetsgfyyb.gif" width=573 height=385 border=0></a><br><a href="http://www.glitter-works.org" target=_blank>glitter-graphics.com</a>


Snapshot -


<a href='http://www.glitter-graphics.com'><img src='http://dl9.glitter-graphics.net/pub/788/788439qwetsgfyyb.gif' width=573 height=385 border=0></a><br><a href='http://www.glitter-works.org' target=_blank>glitter-graphics.com</a>



Also we don't require any lines after border=0></a> so we can delete them



<a href='http://www.glitter-graphics.com'><img src='http://dl9.glitter-graphics.net/pub/788/788439qwetsgfyyb.gif' width=573 height=385 border=0></a><br>



Now we will add the image code to vba


Sub christmas_greetings()

' tools ->refrence -> microsoft outlook



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

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

' image taken from http://www.glitter-graphics.com/graphics/387296

' <br> is used to insert a new line

' to chnage the image code please change in this line

'"<a href='http://www.glitter-graphics.com'><img src='http://dl9.glitter-graphics.net/pub/788/788439qwetsgfyyb.gif' width=573 height=385 border=0></a><br></p>" & _

Dim rs As DAO.Recordset

Dim olApp As Outlook.Application

Dim olMail As MailItem

Set olApp = New Outlook.Application

' open the access table using dao

Set rs = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

' goto first row or recordset

rs.MoveFirst

'run a loop till end of table

Do While Not rs.EOF

Set olMail = olApp.CreateItem(olMailItem)

With olMail

' email id is taken from column b

' rs(1).Value = second column or email id

'rs(0).Value = first column or name

.To = rs(1).Value

.Subject = "Wish you a Merry Christmas!!! "

' NAME OF CLIENT/STAFF from column a

.HTMLBody = "<p><font size='4' face='arial' color='red'><i>Dear " & rs(0).Value & "," & _

" <br></font></p><br><p align='CENTER'><font size='4' face='COMIC SANS' color='Blue'>Wish you a Merry Christmas</p><br><br></font><p align='CENTER'>" & _

"<a href='http://www.glitter-graphics.com'><img src='http://dl9.glitter-graphics.net/pub/788/788439qwetsgfyyb.gif' width=573 height=385 border=0></a><br></p>" & _

"<left>Thanks & Regards <br>Ashish Koul<br>www.excelvbamacros.com/</p>"

.Send

End With

Set olMail = Nothing

rs.MoveNext

Loop

rs.Close

Set olApp = Nothing

End Sub





Now In Case of republic day . I have taken image from

http://media.photobucket.com/image/republic%20day/gopalmurmu/HappyRepublicDay3.jpg?o=6

Before find and replace


<a href="http://photobucket.com/images/republic%20day" target="_blank"><img src="http://i223.photobucket.com/albums/dd30/gopalmurmu/HappyRepublicDay3.jpg" border="0" alt="Happy-Republic-Day Pictures, Images and Photos"/></a>



<a href='http://photobucket.com/images/republic%20day' target='_blank'><img src='http://i223.photobucket.com/albums/dd30/gopalmurmu/HappyRepublicDay3.jpg' border='0' alt='Happy-Republic-Day Pictures, Images and Photos'/></a>


Also we do not require "alt="Happy-Republic-Day Pictures, Images and Photos" so we can delete it


<a href='http://photobucket.com/images/republic%20day' target='_blank'><img src='http://i223.photobucket.com/albums/dd30/gopalmurmu/HappyRepublicDay3.jpg' border='0'/></a>


Now we can add this image code to vba macro



Option Compare Database



Sub republic_day_greetings()

' tools ->refrence -> microsoft outlook

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

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

' image taken from http://media.photobucket.com/image/republic%20day/gopalmurmu/HappyRepublicDay3.jpg?o=6

'change image code in this line

'"<a href='http://photobucket.com/images/republic%20day' target='_blank'><img src='http://i223.photobucket.com/albums/dd30/gopalmurmu/HappyRepublicDay3.jpg' width=450 height=412 border=0></a><br><br><br></p>" & _

' <br> is used to insert a new line

Dim olApp As Outlook.Application

Dim olMail As MailItem

Set olApp = New Outlook.Application

' open the access table using dao

Set rs = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

' goto first row or recordset

rs.MoveFirst

'run a loop till end of table

Do While Not rs.EOF

Set olMail = olApp.CreateItem(olMailItem)

With olMail

' email id is taken from column b



' rs(1).Value = second column or email id

'rs(0).Value = first column or name

.To = rs(1).Value

.Subject = "Wish you a very Happy Republic Day!!! "

' NAME OF CLIENT/STAFF from column a

.HTMLBody = "<p><font size='4' face='arial' color='red'><i>Dear " & rs(0).Value & "," & _

" <br></font></p><br><p align='CENTER'><font size='4' face='COMIC SANS' color='Blue'>Wish you a Happy Republic Day</p><br><br></font><p align='CENTER'>" & _

"<a href='http://photobucket.com/images/republic%20day' target='_blank'><img src='http://i223.photobucket.com/albums/dd30/gopalmurmu/HappyRepublicDay3.jpg' width=450 height=412 border=0></a><br><br><br></p>" & _

"<left>Thanks & Regards <br>Ashish Koul<br>www.excelvbamacros.com/</p>"

.Send

End With

Set olMail = Nothing

rs.MoveNext

Loop

rs.Close

Set olApp = Nothing

End Sub








Download Macros