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

 

[daverunt]

You should be able to add the cellpadding code to the table tag as shown. 5 is a nominal figure - experiment.

As for dates they must be American in the textboxes to start with?

< table cellpadding=5 >

Oh and my knowledge barely scratches the surface .

 

[Patriot2879]

thank you for your help the cellpadding worked great, i have checked the textboxes and the dates are in English but convert to american in the code, please can you help? thank you again, i am loving this VBA coding it is great and your help is amazing i would love to know as much as you

 

[daverunt]

Does using .Text instead of .Value sort it?
e.g.

Me.TextBox27.Text

 

[Patriot2879]

brilliant thank you that sorted it thanks again for all your help

 

[daverunt]

Great. Thanks for the feedback and keep using this forum.
It's a good place to learn.

 

[Patriot2879]

i was wondering do you know howi can add a background colour to the date section and area section please? thanks again

 

[daverunt]

Hi,

if you mean the whole table;

"< table border=""1"", cellpadding=""5"", style=background:""#a6bbde"" >" & _

or
style=background:""green""

You can get your desired hex colour code from here:

http://www.psyclops.com/tools/rgb/

or colour names try here:
https://www.w3schools.com/colors/colors_names.asp

If you only want to colour, say the header table cells use the background style in those. Remove from the table tag..

 

[Patriot2879]

wow that works great thank you so much again for your help, that makes alot of difference now, looks lovely thank you