Formatting / Line Breaks with Email Sending Macro - HTML and Cell Reference

[kwread]

I made a quick macro to help facilitate sending a standard email to a list of recipients.

This Macro looks at a list of recipients and sends a standard email body which is populated with a specific cell reference.

I originally just used the ".body = " method, which worked fine and would take line breaks from the referenced cell which is perfect.

However i want to switch to ".htmlbody = " so that the signature can have an image. Doing this however it seems to loose the inherrent formatting with the in referenced cell.
(I used 'alt+enter' in the corresponding cell to add line breaks)

how can i use .htmlbody to keep images in the signature, while still pulling the line breaks and formatting from the cell which cotains the email body text?

See the macro language (not html) below and thanks in advance!!

'This Macro sends a repeated email with updated subject and names


Dim OutApp As Object
Dim OutMail As Object


Set OutApp = CreateObject("Outlook.Application")


recipcount = Worksheets("Main").Range("E1")


'iterates through recipients
For i = 3 To (2 + recipcount)


'sets current recipient email address, cc emails and name
CurRecip = Worksheets("Main").Cells(i, 5)
CurCC = Worksheets("Main").Cells(i, 8)
CurRecipName = Worksheets("Main").Cells(i, 6)



Set OutMail = OutApp.CreateItem(0)

'creates 'Signature' using the default outlook signature
OutMail.display
Signature = OutMail.Body

'sends email to email address in the excel
With OutMail
'.display
.To = CurRecip
.cc = CurCC
.bcc = ""
.Subject = Worksheets("Main").Cells(4, 1) & " - " & Worksheets("Main").Cells(i, 7)
.Body = "Dear " & CurRecipName & vbNewLine & vbNewLine & Worksheets("Main").Cells(8, 1) & vbNewLine & vbNewLine & "Best," & Signature
' 'You can add other files also like this: .Attachments.Add ("C:\test.txt")
.display 'or use .send
End With

Set OutMail = Nothing


' MsgBox CurRecip
Next i




Set OutMail = Nothing
Set OutApp = Nothing

 

[Dracius]

I would also be interested in an answer to this question as well.

 

[paulxmw]

Hi you would need to add the following code to a module

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    
End Function

Then change Body to you can add many bodys to the body to make up lots of text by using sbody & strBody & so on

.HTMLBody = sBody

then use something like this, on the example rename a sheet to text and put your text in the cells as dictated in the code

   sBody = "****** style=font-size:11pt;font-family:Arial>" & _
              Sheets("Text").Range("C1").Value & "
" & "" & _
              Sheets("Text").Range("C2").Value & "
" & "" & _
              Sheets("Text").Range("C3").Value & "
" & _
              Sheets("Text").Range("C4").Value & "
" & _
              Sheets("Text").Range("C5").Value & "
" & _
              Sheets("Text").Range("C6").Value & "
" & _
              Sheets("Text").Range("C7").Value & "
" & _
              Sheets("Text").Range("C8").Value & "
" & _
              Sheets("Text").Range("C9").Value & "
" & _
              Sheets("Text").Range("C10").Value & "
" & _
              Sheets("Text").Range("C11").Value & "& "<br><br><br>"

The "<\b>"and "<\b>" will swtich Bold text on or off

Hope this helps

 

[Fluff]

@paulxmw
when posting HTML code click on Go Advanced > Select HTML Off

 

[paulxmw]

thanks ill note that fr future