Table VBA code help for email

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
Office Version
  1. 2010
Platform
  1. Windows
Hi i hope you can help me please i have the table below which is a bit messy at moment as it is not alligned neatly as the bottom row is long than the 1st and 2nd row, i have tried amending but it caused an error, and i also want to add another line to the code but it says i have added to much, the new line i want to add is
Code:
"<th>Late Raises:</th>" & "<td>" & Worksheets("Handover").Range("F9").Value & "</td>" &
VBA Code:
   aEmail.htmlBody = "<html>" & _
                    "<p>Hi All, good afternoon,</p>" & _
                    "<p><u><b>" & Me.TextBox4.Value & "</p></u></b>" & _
                    "<table border=""1"", cellpadding=""18"", style=background:""#a6bbde"" >" & _
                "<tr>" & _
                    "<th>NCMO Issue:</th>" & "<td>" & Worksheets("Handover").Range("C5").Value & "</td>" & "<th>Re-Raised:</th>" & "<td>" & Worksheets("Handover").Range("F5").Value & "</td>" & _
                    "</tr>" & _
                "<tr>" & _
                    "<th>SNR:</th>" & "<td>" & Worksheets("Handover").Range("C6").Value & "</td>" & "<th>Additional Jobs:</th>" & "<td>" & Worksheets("Handover").Range("F6").Value & "</td>" & _
                    "</tr>" & _
                "<tr>" & _
                    "<th>Replans:</th>" & "<td>" & Worksheets("Handover").Range("C7").Value & "</td>" & "<th>Incorrectly Booked:</th>" & "<td>" & Worksheets("Handover").Range("F7").Value & "</td>" & "<th>IGT Issue:</th>" & "<td>" & Worksheets("Handover").Range("C9").Value & "</td>" & "<th>Outages:</th>" & "<td>" & Worksheets("Handover").Range("c8").Value & "</td>" & _
                    "</tr>" & _
                    "</table>" & _
                "" & _
 
Hi good morning, i have tried the code thank you but the border around each line is missing how do i add that in please, i have tried adding the code below after the &"</html>" as well but it wouldn't let me add it in please advise.
VBA Code:
                "<p><u><b>" & Me.TextBox5.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox1.Value & "</p>" & _
                "<p><u><b>" & Me.TextBox6.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox2.Value & "</p>" & _
                "<p><u><b>" & Me.TextBox7.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox3.Value & "</p>" & RangetoHTML(rngDataToEmail) & _
                "<p>Many Thanks</p>" & _
                "<p>New Connections Team</p>" & _
                "</body></html>"

This is what i tried to do with the above code but didnt work.
Code:
= "<html>" _
                    & "<p>Hi All, good afternoon,</p>" _
                    & "<p><u><b>" & Me.TextBox4.Value & "</p></u></b>" _
                    & "<table border=1, cellpadding=18, style=background:#a6bbde >" _
                    & "<tr>" _
                    & "<th>NCMO Issue:</th><td>" & Worksheets("Handover").Range("C5").Value & "</td>" _
                    & "<th>Re-Raised:</th><td>" & Worksheets("Handover").Range("F5").Value & "</td>" _
                    & "<th>SNR:</th><td>" & Worksheets("Handover").Range("C6").Value & "</td>" _
                    & "</tr>" _
                    & "<tr>" _
                    & "<th>Additional Jobs:</th><td>" & Worksheets("Handover").Range("F6").Value & "</td>" _
                    & "<th>Replans:</th><td>" & Worksheets("Handover").Range("C7").Value & "</td>" _
                    & "<th>Incorrectly Booked:</th><td>" & Worksheets("Handover").Range("F7").Value & "</td>" _
                    & "</tr>" _
                    & "<tr>" _
                    & "<th>IGT Issue:</th><td>" & Worksheets("Handover").Range("C9").Value & "</td>" _
                    & "<th>Outages:</th><td>" & Worksheets("Handover").Range("C8").Value & "</td>" _
                    & "<th>Late Raises:</th><td>" & Worksheets("Handover").Range("F9").Value & "</td>" _
                    & "</tr>" _
                    & "</table>" _
                "<p><u><b>" & Me.TextBox5.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox1.Value & "</p>" & _
                "<p><u><b>" & Me.TextBox6.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox2.Value & "</p>" & _
                "<p><u><b>" & Me.TextBox7.Value & "</p></u></b>" & _
                "<p>" & Me.TextBox3.Value & "</p>" & RangetoHTML(rngDataToEmail) & _
                "<p>Many Thanks</p>" & _
                "<p>New Connections Team</p>" & _
                "</body></html>"
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi,

The & is positioned neatly on the left of all of my lines of code until your added lines.......
Also you will hit the limit of 25 lines of code with the added lines. So I shifted the lines with least characters onto a single line <tr></tr> for example
Separate lines are clearer but not necessary.

The border/cellpadding needed double quotation marks adding.

See if this works.

Code:
  aEmail =  "<html><body><p>Hi All, good afternoon,</p>" _
                    & "<p><u><b>" & Me.TextBox4.Value & "</p></u></b>" _
                    & "<table border=""1"", cellpadding=""18"", style=background:#a6bbde >" _
                    & "<tr>" _
                    & "<th>NCMO Issue:</th><td>" & Worksheets("Handover").Range("C5").Value & "</td>" _
                    & "<th>Re-Raised:</th><td>" & Worksheets("Handover").Range("F5").Value & "</td>" _
                    & "<th>SNR:</th><td>" & Worksheets("Handover").Range("C6").Value & "</td>" _
                    & "</tr><tr>" _
                    & "<th>Additional Jobs:</th><td>" & Worksheets("Handover").Range("F6").Value & "</td>" _
                    & "<th>Replans:</th><td>" & Worksheets("Handover").Range("C7").Value & "</td>" _
                    & "<th>Incorrectly Booked:</th><td>" & Worksheets("Handover").Range("F7").Value & "</td>" _
                    & "</tr><tr>" _
                    & "<th>IGT Issue:</th><td>" & Worksheets("Handover").Range("C9").Value & "</td>" _
                    & "<th>Outages:</th><td>" & Worksheets("Handover").Range("C8").Value & "</td>" _
                    & "<th>Late Raises:</th><td>" & Worksheets("Handover").Range("F9").Value & "</td>" _
                    & "</tr></table>" _
                    & "<p><u><b>" & Me.TextBox5.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox1.Value & "</p>" _
                    & "<p><u><b>" & Me.TextBox6.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox2.Value & "</p>" _
                    & "<p><u><b>" & Me.TextBox7.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox3.Value & "</p>" & RangetoHTML(rngDataToEmail) _
                    & "<p>Many Thanks</p>" _
                    & "<p>New Connections Team</p>" _
                    & "</body></html>"
 
Upvote 0
You can shift the table styling to a style tag and play around with making your table look pretty.





Code:
  htmlHdr = "<!DOCTYPE html><html><head>" _
  & "<style>" _
  & "body, html{font-family: calibri; font-size:18px;}" _
  & "th{padding:18px; font-size:16px; font-weight:bold;  text-align:left; border-style: solid; border-width:2px; border-color:#40618c;  color:#000000;background-color:#a6bbde;}" _
  & "td{padding:18px; font-size:16px; font-weight:normal; text-align:left; border-style: solid; border-width:2px; border-color:#40618c; }" _
  & "</style>" _
  & "</head>"
    
  aEmail = "<body><p>Hi All, good afternoon,</p>" _
                    & "<p><u><b>" & Worksheets("Handover").Range("C5").Value & "</p></u></b>" _
                    & "<table><tr>" _
                    & "<th>NCMO Issue:</th><td>" & Worksheets("Handover").Range("C5").Value & "</td>" _
                    & "<th>Re-Raised:</th><td>" & Worksheets("Handover").Range("F5").Value & "</td>" _
                    & "<th>SNR:</th><td>" & Worksheets("Handover").Range("C6").Value & "</td>" _
                    & "</tr><tr>" _
                    & "<th>Additional Jobs:</th><td>" & Worksheets("Handover").Range("F6").Value & "</td>" _
                    & "<th>Replans:</th><td>" & Worksheets("Handover").Range("C7").Value & "</td>" _
                    & "<th>Incorrectly Booked:</th><td>" & Worksheets("Handover").Range("F7").Value & "</td>" _
                    & "</tr><tr>" _
                    & "<th>IGT Issue:</th><td>" & Worksheets("Handover").Range("C9").Value & "</td>" _
                    & "<th>Outages:</th><td>" & Worksheets("Handover").Range("C8").Value & "</td>" _
                    & "<th>Late Raises:</th><td>" & Worksheets("Handover").Range("F9").Value & "</td>" _
                    & "</tr></table>" _
                    & "<p><u><b>" & Me.TextBox5.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox1.Value & "</p>" _
                    & "<p><u><b>" & Me.TextBox6.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox2.Value & "</p>" _
                    & "<p><u><b>" & Me.TextBox7.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox3.Value & "</p>" & RangetoHTML(rngDataToEmail) _
                    & "<p>Many Thanks</p>" _
                    & "<p>New Connections Team</p>" _
                    & "</body></html>"


'Add the header to the mail

.HTMLBody = htmlHdr & aEmail
 
Upvote 0
- I replaced Me.TextBox4.Value with Worksheets("Handover").Range("C5").Value in the previous post to test it, so that needs sorting if you use it.
 
Upvote 0
Hi good morning. Thank you for your help much apprecaited, i have updated my code to the below code, but when i click on my command button to send the email it is no blank and there is nothing in it. There is no borders, no textbox data, hope you can adivse please what i doing wrong?
VBA Code:
Private Sub CommandButton4_Click()
Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String
Dim rngDataToEmail As Range

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

    With Sheets("Issues")
        Set rngDataToEmail = .Range("A1:K" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    
aEmail = "<html><body><p>Hi All, good afternoon,</p>" _
                    & "<p><u><b>" & Me.TextBox4.Value & "</p></u></b>" _
                    & "<table border=""1"", cellpadding=""18"", style=background:#a6bbde >" _
                    & "<tr>" _
                    & "<th>NCMO Issue:</th><td>" & Worksheets("Handover").Range("C5").Value & "</td>" _
                    & "<th>Re-Raised:</th><td>" & Worksheets("Handover").Range("F5").Value & "</td>" _
                    & "<th>SNR:</th><td>" & Worksheets("Handover").Range("C6").Value & "</td>" _
                    & "</tr><tr>" _
                    & "<th>Additional Jobs:</th><td>" & Worksheets("Handover").Range("F6").Value & "</td>" _
                    & "<th>Replans:</th><td>" & Worksheets("Handover").Range("C7").Value & "</td>" _
                    & "<th>Incorrectly Booked:</th><td>" & Worksheets("Handover").Range("F7").Value & "</td>" _
                    & "</tr><tr>" _
                    & "<th>IGT Issue:</th><td>" & Worksheets("Handover").Range("C9").Value & "</td>" _
                    & "<th>Outages:</th><td>" & Worksheets("Handover").Range("C8").Value & "</td>" _
                    & "<th>Late Raises:</th><td>" & Worksheets("Handover").Range("F9").Value & "</td>" _
                    & "</tr></table>" _
                    & "<p><u><b>" & Me.TextBox5.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox1.Value & "</p>" _
                    & "<p><u><b>" & Me.TextBox6.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox2.Value & "</p>" _
                    & "<p><u><b>" & Me.TextBox7.Value & "</p></u></b>" _
                    & "<p>" & Me.TextBox3.Value & "</p>" & RangetoHTML(rngDataToEmail) _
                    & "<p>Many Thanks</p>" _
                    & "<p>New Connections Team</p>" _
                    & "</body></html>"
    
    
    aEmail.Recipients.Add (Worksheets("Email Links").Range("B2").Value)
    aEmail.CC = (Worksheets("Email Links").Range("C2").Value)
    aEmail.BCC = ""
    aEmail.Subject = "" & Range("J1").Value & " " & Range("K1").Value
    aEmail.Display
    
      
End Sub
 
Upvote 0
This line should have been.
Code:
aEmail.HTMLBody = "<html><body><p>Hi All, good afternoon,</p>" _
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top