Embedding Table in Email Message Using VBA Code

[Patriot2879]

Hi, I have created VBA code in Excel that takes the contents of a recordset and places the data (Date and Area) in the body of an email message. My code looks like this:

This code all works great. The problem is that I want to output additional fields ( Date)and Area), and I want the data to appear in a table so that the data is formatted nicely. I have tried separating the fields with tabs, but I have no way of knowing the length of the data in each field, so the data does not line up correctly.

Is this possible at all to put a table around the Date and Area textbox? Any help would be much appreciated!

I have included the 2 lines where i want a table around and the full coding.

"Date:" & vbTab & Me.TextBox18.Value & vbTab & Me.TextBox19.Value & vbTab & Me.TextBox21.Value & vbCrLf & _
"Area:" & vbTab & Me.TextBox9.Value & vbTab & vbTab & Me.TextBox20.Value & vbTab & vbTab & Me.TextBox22.Value & vbCrLf & _

Private Sub CommandButton1_Click()

Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)

        aEmail.Body = "Hi There," & Chr(10) & vbCrLf & _
                      "" & Me.TextBox33.Value & Chr(10) & _
                      "Date:" & vbTab & Me.TextBox18.Value & vbTab & Me.TextBox19.Value & vbTab & Me.TextBox21.Value & vbCrLf & _
                      "Area:" & vbTab & Me.TextBox9.Value & vbTab & vbTab & Me.TextBox20.Value & vbTab & vbTab & Me.TextBox22.Value & vbCrLf & _
                      "" & Me.TextBox17.Value & Chr(10) & vbCrLf & _
                      "Many thanks " & Chr(10)

        aEmail.Recipients.Add "test.test@test.com; test.test@test.com"
        aEmail.CC = ""
        aEmail.BCC = ""
        aEmail.Subject = "Weekly " & Range("D2").Value
        aEmail.Display

 

[John_w]

Use an online HTML editor to compose the email body, including a table. Then use the generated HTML in aEmail.HTMLBody instead of aEmail.Body, like this:

    Dim HTML As String
    HTML = ""
    HTML = HTML & "< p>Hi there,< /p>"
    HTML = HTML & "< table style='border-collapse: collapse; width: 100%;' border='1'>"
    HTML = HTML & "< tbody>"
    HTML = HTML & "< tr>"
    HTML = HTML & "< td style='width: 25%;'>Text< /td>"
    HTML = HTML & "< td style='width: 25%;'>Text< /td>"
    HTML = HTML & "< td style='width: 25%;'>Text< /td>"
    HTML = HTML & "< td style='width: 25%;'>Text< /td>"
    HTML = HTML & "< /tr>"
    HTML = HTML & "< /tbody>"
    HTML = HTML & "< /table>"
    HTML = HTML & "< p>< /p>"
    HTML = HTML & "< p>Many thanks< /p>"

    aEmail.HTMLBody = HTML

Remove the space after every "<" in your code. The space prevents the forum rendering the HTML.

 

[Patriot2879]

Hi i am sorry and thank you for the reply but i havn't a clue what you mean sorry , i am pretty new to all this VBA coding, and this has really confused me, sorry can you offer any more guidance at all please?

 

[daverunt]

Hi,

here's an example file with your email message.
https://www.dropbox.com/s/fw8jrp7wt16pdnc/table.txt?dl=0

I put it in a text file as posting html code on the forum requires you know the pitfalls and I find it simpler to provide a link to a file.

Note the use of .HTMLBody as per Johns post. This tells Outlook the content is in the html format.

It should create a table with 2 rows as per your post.
Date and Area will be bold. If you don't want that change 'th' (table header) to 'td' (table detail)
You can add a border by adding the extra text in the border tag as shown in Johns code.
Learn a bit of html and you can change font sizes / colours etc.

 

[Patriot2879]

Hi Sorry me again do i put this in with my VBA code? sorry still confused i am very new to this still, and putting HTML in VBA has confused me now , can i send you the file at all please for you to look at and help me please? Thank you again for all your help and assistance it is greatly appreciated

 

[daverunt]

It is just text with instructions on how it is to be presented, for applications that can interpret the tags.

Copy the file content to replace the following:
The rest of your code remains as is.

aEmail.Body = "Hi There," & Chr(10) & vbCrLf & _
                      "" & Me.TextBox33.Value & Chr(10) & _
                      "Date:" & vbTab & Me.TextBox18.Value & vbTab & Me.TextBox19.Value & vbTab & Me.TextBox21.Value & vbCrLf & _
                      "Area:" & vbTab & Me.TextBox9.Value & vbTab & vbTab & Me.TextBox20.Value & vbTab & vbTab & Me.TextBox22.Value & vbCrLf & _
                      "" & Me.TextBox17.Value & Chr(10) & vbCrLf & _
                      "Many thanks " & Chr(10)

By all means I will look at the file if you get stuck.

 

[Patriot2879]

Thank you I will have a go in the morning thanks again wish me luck

 

[Patriot2879]

Hi there i have inputted the code provided now many thanks for this it does work great and all in align, but i am trying to add more textboxes on new line to the date and area but it keeps coming up with an error and wont let me add any new ones for example under date i want to add a new line ... "<td>" & Me.TextBox27.Value & "</td>" & _ but it wont except this its the same for area, please can you help with this. thanks again

Private Sub CommandButton1_Click()

Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)

          aEmail.HTMLBody = "<html>******>" & _
                            "<p>Hi " & Me.TextBox35.Value & "</p>" & _
                            "<p>" & Me.TextBox33.Value & "</p>" & _
                            "<p>" & Me.TextBox17.Value & "</p>" & _
                            "<table>" & _
                "<tr>" & _
                            "<th>Date:</th>" & _
                            "<td>" & Me.TextBox18.Value & "</td>" & _
                            "<td>" & Me.TextBox19.Value & "</td>" & _
                            "<td>" & Me.TextBox21.Value & "</td>" & _
                            "<td>" & Me.TextBox23.Value & "</td>" & _
                            "<td>" & Me.TextBox25.Value & "</td>" & _
                            "<td>" & Me.TextBox26.Value & "</td>" & _
                            "</tr>" & _
                "<tr>" & _
                            "<th>Area:</th>" & _
                            "<td>" & Me.TextBox9.Value & "</td>" & _
                            "<td>" & Me.TextBox20.Value & "</td>" & _
                            "<td>" & Me.TextBox22.Value & "</td>" & _
                            "</tr>" & _
                            "</table>" & _
                "<br>" & _
                "<p>Many Thanks</p>" & _
                "<p>Complex Team</p>" & _
                "</body></html>"


        aEmail.Recipients.Add (UserForm1.TextBox36.Value)
        aEmail.CC = (UserForm1.TextBox37.Value)
        aEmail.BCC = ""
        aEmail.Subject = "Weekly " & Range("D2").Value
        aEmail.Display

End Sub

 

[daverunt]

Hi,

as you can see the posting of HTML on the forum has issues.
When you post html code use a space between every less than tag in the posted code e.g < td> - see John w post 2.27pm to see how it's done.
Also use [ html ] [ /html ] tags for the code instead of [ Code ] tags.

Until you do that your post is just unreadable.


There is a limit to the number of lines you can use with a continue underscore at the end.
I think it's 25, so if you see a too many lines to continue or some such warning when you are adding the line then you can reduce the lines by turning 2 lines of code into 1.

It's less tidy and less readable in my view and I would split the tables into different strings and concatenate those, but the easiest method is merging the lines of code.
Remember there will only be one underscore on any one line of code.

"< td >" & Me.TextBox21.Value & "< /td >< td >" & Me.TextBox27.Value & "< /td >" & _

Here's a link for html table code generation.
https://html-cleaner.com/html-table-generator/

You can generate the code for the size of table you want. Then it's just a matter of comparing their code to what has been done by me to prep it for vba

 

[Patriot2879]

that is brilliant thanks for your advise that has worked , just a couple more things please on the Date line with textboxes the date is coming up as american how can i change this to english please? and is there any way i can put more space inbetween the textboxes? thanks again for all your help, your knowledge is trumendous