How to copy excel cell value with multiple lines into email body with the same format

Help
1

Hi,

I am trying to send a excel cell value within an email body with send outlook activity. The problem is that the excel cell contains various tasks in separate line one after the other within the same cell, but when I read the cell and add it into my email body, UI path combines it into one long string and inserts the same. Please see the screenshot of the excel cell values below -

tasks

I want the tasks to come in the same format with line breaks in the email body.

Any help on this is appreciated !

2

@Ashish_Mehra Hi Ashish after the reading the text from the excel and the format is not preserved. Check preserveFormat property of Read excel/Read cell activity and try once. But if you select preserveFormat property read of excel will become slow.

Otherwise you can try also manipulation, only if you have specific reference in the string.

3

Hi Manjusts, the above does not properly work , further the idea is to add this variable value into an HTML body , so I would need to add a ,
wherever there is an excel line break

4

@Ashish_Mehra After reading the value from excel check how you will get output by printing it.

5

Hi

following is VBA code , you can paste this in your EXCEL file

you can try below (MACRO) either with "execute macro "

Function RangetoHTML(rng As Range)
Application.DisplayAlerts = False
Application.ScreenUpdating = False
’ Changed by Ron de Bruin 28-Oct-2006
’ Working in Office 2000-2013
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 =
6

after this you can send e-mail with body with following VBA code .

Sub Mail_workbook_Outlook_2()
Dim wb1 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object ’ Late binding
Dim OutMail As Object ’ Late binding
Dim rng1 As Range, rng2 As Range

 Set rng1 = Nothing
 Set rng2 = Nothing
On Error Resume Next

'Set rng1 = Sheets("").Range("").SpecialCells(xlCellTypeVisible)
'Mention your range to copy in to BODY OF THE E-MAIL 



With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With



Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
  
    .To = userid1
    .CC = userid2
   
    .Subject = "SOME XXXXXX"
  
    .HTMLBody = RangetoHTML(rng1)

     'HERE rangetoHTML is userdefined function done in previous module
     'whatever you mention  range  here will paste as it is  (with format ) in the body of the e-mai 

    .Send   'or use .Display



End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
8

I found these simple solution :
You have to remplace the line break from excel Chr(10) with the line break from html

So in youre email code just put

body = "<HTML><BODY style=font-size:11pt;font-family:Calibri>"
body = body & “Hi”
body = body & " <br> " & "This are the values from my cell"
body = body & "<br>" & Replace(Sheets(“Sheet1”).Range(“B9”).Text, Chr(10), "<br> ")
body = body & "</BODY></HTML>"

Enjoy

1 Like