• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can you please give me a vba code to insert a dynamic table into an email body .

Status
Not open for further replies.

rAVINDB

New Member
I have vba code that can attach dynamic table as a pdf file and send email to a list of recipients.
The requirement now is - I want the same dynamic table to be also part of the email body. can someone provide me the vba code and where to insert it.
 
The following looks at cell A1 first and then determines the total range utilized. It converts those cells to a table that is pasted into the email.
Refer to attachment....

Code:
Sub send_email_via_outlook()

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

Set olMail = olApp.CreateItem(olMailItem)
    
    With olMail
        .To = "test@gmail.com"
        .CC = ""
        .Subject = "Send Range as table in outlook"  '<br> used to insert a line ( press enter)
        .HTMLBody = "Please find the table below <br><br> " & _
                    create_table(Range("a1").CurrentRegion) & _
                    "</Table><br> <br>Regards<br> Ashish Koul"
        .Display
        '.Send
    End With


End Sub

Function create_table(rng 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.Cells(1, 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.Cells(i, j).Value & "</TD>"
    Next
    mbody = mbody & mbody1 & "</TR>"
Next

create_table = mbody
End Function
 

Attachments

  • Send Range as formatted table.xlsm
    21.6 KB · Views: 119
Hi, I would like to come back to this topic, cause I have some file with module to create mail from VBA and send it with some body test. But I would like to put there also a table below the text with columns with datas from excel sheet name Arkusz1 from A to O. The issue is that the table should have dynamic range, so if user in cell A34 provide some index, then table should be generated only with rows matching witch the text from user. Can you please help?
 

Attachments

  • Returns.xlsm
    113.3 KB · Views: 6
Last edited:
Status
Not open for further replies.
Back
Top