Pages

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 = "koul.ashish@gmail.com"
        .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"
        .Display
        '.Send
    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 http://www.w3schools.com/html/html_colornames.asp

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>"
Next

' 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>"
    Next
    mbody = mbody & mbody1 & "</TR>"
Next

create_table = mbody
End Function


Download working File

1 comment: