Hi! I have on code below which fetch data from my excel file ( I cannot show it since its a confidential ) I just want to put a automatically break on each line, I already did it using <br> but when I put multiple data on one cell, it stays on single line. The sample is on Data1. Can anyone help me?
So the Output should be
Name1
Name2
Name3
Name4
Name5
VBA Code:
Sub SendEmail()
Dim OutApp As Object, OutMail As Object
TodayDate = Format(Date, "mm/dd/yyyy")
Dim Data1 As String
Dim Data2 As String
Dim Data3 As String
' (For example this is the data that I pulled from Excel File) (When I triggered the Run button, It stays on one line like Name1Name2Name3
Data1= Name1, Name2, Name3
Data2= Name4
Data3= Name5
I want the output like:
Name1 1st line
Name2 2nd line
Name3 3rd line
Name4 4th line
Name5 4th line
'Dim OutApp As New Outlook.Application
'Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'HTML Contents including CSS
strHtml1 = "<html xmlns:v='urn:schemas-microsoft-com:vml' xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:w='urn:schemas-microsoft-com:office:word' xmlns:m='[URL='http://schemas.microsoft.com/office/2004/12/omml%27']http://schemas.microsoft.com/office/2004/12/omml'[/URL] xmlns=' strHtml2 = "{margin:0in; font-size:11.0pt; font-family:'Calibri',sans-serif;} a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;} span.EmailStyle17 {mso-style-type:personal-compose; font-family:'Calibri',sans-serif; color:windowtext;} .MsoChpDefault {mso-style-type:export-only; font-family:'Calibri',sans-serif;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 {page:WordSection1;} --></style><!--[if gte mso 9]><xml> <o:shapedefaults v:ext='edit' spidmax='1026' /> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext='edit'> <o:idmap v:ext='edit' data='1' /> </o:shapelayout></xml><![endif]--></head> "
strBody1 = "<body lang=EN-US link='#0563C1' vlink='#954F72' style='word-wrap:break-word'>"
strBodyDiv = "<div class=WordSection1><p >Hi All,<br><br>Please see the table below for the Test Table.<o:p></o:p></p> <p style='margin-bottom:12.0pt'><span style='color:black;background:white'><a href='testlink[URL='http://sn-sos:8080/secure/Dashboard.jspa?selectPageId=19909%27']'[/URL] target='_blank'><span style='font-size:10.0pt;font-family:'Segoe UI',sans-serif'>testlink[URL='http://sn-sos:8080/secure/Dashboard.jspa?selectPageId=19909']</span>[/URL] </a> </span> <o:p></o:p> </p> "
strBodyTable = "<table border=0 cellspacing=0 cellpadding=0 style='border-collapse:collapse'><tr style='height:17.95pt'><td width=882 valign=top style='width:661.25pt;border:solid windowtext 1.0pt;background:#002060;padding:0in 5.4pt 0in 5.4pt;height:14.95pt'><p ><b><span style='font-size:10.0pt;font-family:'Times New Roman',serif;color:white'>Manila Pictures</span></b><o:p></o:p></p></td></tr><tr"
strBodyTable2 = "style='height:17.95pt'><td width=882 valign=top style='width:661.25pt;border:solid windowtext 1.0pt;border-top:none;background:white;padding:0in 5.4pt 0in 5.4pt;height:17.95pt'><p ><span style='color:black'></span><o:p></o:p></p></td></tr><tr style='height:17.95pt'><td width=882 valign=top style='width:661.25pt;border:solid windowtext 1.0pt;border-top:none;background:#1F3864;padding:0in 5.4pt 0in 5.4pt;height:14.95pt'>"
strBodyTable3 = "<p ><b><span style='font-size:10.0pt;color:white'>Names</span></b><o:p></o:p></p></td></tr><tr style='height:5.9pt'><td width=882 valign=top style='width:300.25pt;border:solid windowtext 1.0pt;border-top:none;padding:0in 5.4pt 0in 3.0pt;height:10.0pt'>"
strBodyTableQA = Data1 & "<br>" & Data2 & "<br>" & Data3 & "<br>" 'Here is the data
strBodyTable4 = "</td></tr>"
strEndHtml = strHtml1 & strHtml2 & strBody1 & strBodyDiv & strBodyTable & strBodyTable2 & strBodyTable3 & strBodyTableQA & strBodyTable4 & "</table>" & "</div>" & "</body>" & "</html>"
ToEmail = "sampleemail123@email.com"
With OutMail
.To = ToEmail
.Subject = "Sample Subject" & " " & ScrumDate
.CC = "sampleemail@email.com" & ";" & "sampleemail1@email.com" & ";" & "sampleemail2@email.com"
.Display
.HTMLBody = strEndHtml & .HTMLBody
End With
Set OutApp = Nothing
Set OutMail = Nothing
End SubSo the Output should be
Name1
Name2
Name3
Name4
Name5
