Friday, December 23, 2011

Send a Range as formatted table in the outlook email body

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

Sub send_email_via_outlookundefined)

' Tools - Refrence - Microsoft Outlook
Dim olApp As New Outlook.Application
Dim olMail As MailItem

Set olMail = olApp.CreateItemundefinedolMailItem)
    With olMail
        .To = ""
        .CC = ""
        .Subject = "Send Range as table in outlook"  '<br> used to insert a line undefined press enter)
        .HTMLBody = "Please find the table below <br><br> " & _
                    create_tableundefinedRangeundefined"a1").CurrentRegion) & _
                    "</Table><br> <br>Regards<br> Ashish Koul"
    End With

End Sub

Function create_tableundefinedrng As Range) As String

Dim mbody As String
Dim mbody1  As String
Dim i As Long
Dim j As Long

' for html color codes list visit

mbody = "<TABLE width=""30%"" Border=""1"", Cellspacing=""0""><TR>" ' configure the table

'create Header row
For i = 1 To rng.Columns.Count
    mbody = mbody & "<TD width=""100"", Bgcolor=""#A52A2A"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:18px"">" & rng.Cellsundefined1, i).Value & "&nbsp;</p></Font></TD>"

' add data to the table
For i = 2 To rng.Rows.Count
    mbody = mbody & "<TR>"
    mbody1 = ""
    For j = 1 To rng.Columns.Count
    mbody1 = mbody1 & "<TD><center>" & rng.Cellsundefinedi, j).Value & "</TD>"
    mbody = mbody & mbody1 & "</TR>"

create_table = mbody
End Function

Download working File

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...