VBA replace string into an email template

[loadius]

Hi, hopefully someone can help me.

I have been using the below code for years to generate incident comms. In my new role I am adapting it to new requirements. The code grabs data from Sharepoint/teams it then filters and copy and pastes it into a new tab. The code calls an email template that contains placeholders in multiple tables which includes a banner. then by using strings it replaces the value of cells with the placeholder. The issue I face is that the data has bullet points and is paragraphs. However when the email is generated, it has the data in one block. I have tried inserting line breaks but without success.


'Populate replacement strings from sharepoint. .Range("xx") corresponds to the column containing new text.
Dim repNumberText As String: repNumberText = dataSheet.Range("f2").Value
Dim repTitleText As String: repTitleText = dataSheet.Range("I2").Value
Dim repSummaryText As String: repSummaryText = dataSheet.Range("B2").Value
Dim repImpactText As String: repImpactText = dataSheet.Range("C2").Value
Dim repUnderwayText As String: repUnderwayText = dataSheet.Range("D2").Value
Dim repCompletedText As String: repCompletedText = dataSheet.Range("E2").Value
Dim repUpdateText As String: repUpdateText = dataSheet.Range("G2").Value

repSummaryText = "<p>" & repSummaryText & "</p>"
repCompletedText = "<p>" & repCompletedText & "</p>"





Dim replaceStrings() As Variant
Dim replaceWithStrings() As Variant
'Replacement Array, replaceStrings are the text placeholders in the email templates, replacewithstrings are the variables assigned above.
replaceStrings = Array("NumberText", "TitleText", "SummaryText", "ImpactText", "UnderwayText", "CompletedText", "UpdateText")
replaceWithStrings = Array(repNumberText, repTitleText, repSummaryText, repImpactText, repUnderwayText, repCompletedText, repUpdateText)
Dim currentItem As String
Dim currentReplaceItem As String
Dim i As Integer
i = UBound(replaceStrings)
Dim j As Integer
j = 0


With msgFile
Today = Format(Now(), "DDDD DD MMM yyyy")
'Dim HtmlBody As String

'Loop through arrays and replace text
Do Until j = i + 1
.HtmlBody = Replace(.HtmlBody, replaceStrings(j), replaceWithStrings(j))
j = j + 1

Loop


'Replace subject texts.\
' .Subject = "Security Major Incident Communications"
' .Subject = Today
.Subject = Replace(.Subject, "NumberText", repNumberText)
.Subject = Replace(.Subject, "TitleText", repTitleText) & " " & "-" & " " & Today


.Display

 

[loadius]

Anyone?

 

[loadius]

I have decided to revisit this issue.

My latest iteration is to use a replace function, after the loop through the string arrays in the above code.

.HTMLBody = replace(.htmlBody, "';", "<BR>")

However, whilst the <BR> does add the line break, it changes the font to TNR and puts in a lot HTML garbage when the email is generated? I suspect is it is from the "<BR>".

The other odd thing is that when I add in a debug.print onto htmlbody it shows that the font and line breaks are correct, even with or without the 2nd replace function.

I have also tried to change "<BR>" with CHR(10) and vbnewline and all the other permutations without success.