Access Macro - Send a Access Table data as formatted table in the outlook email body

[ashish koul]

If you want to send the data of table in the body of outlook email.Snapshot below- 

Here is the code-

Option Compare Database

Sub send_range_as_table()

'''''''''''''''''' tools -> refrence -> Microsoft outlook

Dim olApp As Outlook.Application

Dim olMail As MailItem

Dim mailbody As String

Dim rs As DAO.Recordset

' <br> used to insert a line ( press enter)

' create a table using html

' check the link below to know more about html tables

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

' html color code

'http://www.computerhope.com/htmcolor.htm or http://html-color-codes.info/

'bg color is used for background color

' font color is used for font color

'<b> bold the text  http://www.w3schools.com/html/html_formatting.asp

' &nbsp;  is used to give a single space between text

'<p style="font-size:15px">This is some text!</p> used to reduce for font size

'********************* created header of table

   mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _

   "<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Rep Name&nbsp;</p></Font></TD>" & _

   "<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Zone&nbsp;</p></Font></TD>" & _

   "<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Location&nbsp;</p></Font></TD>" & _

   "<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Sales&nbsp;</p></Font></TD>" & _

      "</TR>"

' add the data to the table

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

rs.MoveFirst

Do While Not rs.EOF

       

              mailbody = mailbody & "<TR>" & _

               "<TD ><center>" & rs.Fields![Rep name].Value & "</TD>" & _

               "<TD><center>" & rs.Fields![zone].Value & "</TD>" & _

               "<TD><center>" & rs.Fields![Location].Value & "</TD>" & _

               "<TD><center>" & rs.Fields![Sales].Value & "</TD>" & _

                         "</TR>"

        

rs.MoveNext

Loop

rs.Close

' <br> used to insert a line ( press enter) and send email

Set olApp = New Outlook.Application

Set olMail = olApp.CreateItem(olMailItem)

With olMail

.To = "koul....@gmail.com"

.CC = ""

.Subject = "Send Access Table in the body of outlook email as Formatted Table"

.HTMLBody = "Please find the ----- below ----- <br><br> " & mailbody & "</Table><br> <br>Regards <br> <br> Ashish Koul"

.Display

'.Send

End With

End Sub

Download Access Database