Copy same format from excel file to email body

Hi ,

I am trying to copy the excel data as it is to email body . I tried the attached workflow but it is not giving me expected output:

Please find my output below :

.
When i tried with workflow it is not giving me along with % .
Please let me know how can i achieve it.
I checked other codes and got the workflow , even i tried power shell it has given the output but not sure how to send to multiple emails.
DataTableToHtmlText-Excel.xaml (18.6 KB)
Regards,
Padmini

Hi @chandrashekar_padmin

I got the solution for your problem but it is not residing in Uipath instead in Excel itself. Instead of thinking in Ui-Path we can do it in Excel. Please find the attached image where i have applied a simple formula of text in excel sheet. I believe you must be dividing the value so that you can get the percentage. Along with the divide function you need to apply the text function.

I have added one example like i am dividing the D3 with E3 and along with that i am applying the text formula =Text((D3/E3,“0.0%”) to get the one decimal percentage value in text format. If you want to extend the decimal you need to do it by increasing the zero’s. After doing this run your code it will work fine. I testing and it was working fine.

After applying the formula refer the second image.

Hope the information should be useful to you :slight_smile:

Regards,
CP

image

Hi,

I tried , but it didn’t give me the expected output in email.

Can anyone help me on this?

Regards,
padmini

@chandrashekar_padmin Can you send the Excel File ? And Also Do you have to use Other Formats of Excel inside the Email. Even if we Make a Solution for this, It can only be used for the Same Format of Excel and Not Any other Format, Is it Fine?

Hi ,
Mail_BH.xlsx (46.8 KB)
My excel has different formulas i have already applied to it . I need to show the same format in email as well like below: % to be represented as % and 5,472 to represent the same.

, yes the solution can be for the same excel . Also i am not able to share excel as it has formulas and it is getting changed once i change any values , please find attached one.

Hi @supermanPunch,

Any workaround for this?

Regards,
Padmini

@chandrashekar_padmin It’s Difficiult :sweat_smile: Trying to implement it Still

sure :slight_smile: @supermanPunch

Hi @supermanPunch,

I got my answer using vba only. Please find the code below for future references in case if any one needs it:
Sub Email()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection

Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a range if you want
Set rng = Sheets(“Overall Summary”).Range(“A1:N28”).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox “The selection is not a range or the sheet is protected” & _
vbNewLine & “please correct and try again.”, vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject(“Outlook.Application”)
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
strHtml = “” & “” & “Hi All,

please find below:” & “
” & “” & “” & “”

With OutMail
.To = “”
.CC = “”
.Subject = “Test Mail”
.HTMLBody = strHtml & RangetoHTML(rng)
.Attachments.Add “”
.Display
.Send 'or use .Display

End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.Save
End Sub
Function RangetoHTML(rng As Range)

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

@chandrashekar_padmin Wow, It really worked. Same as the Output you needed ?

Yes it worked :slight_smile:

@chandrashekar_padmin Glad it is Solved. Thank you for Sharing the Code. :smile:

1 Like

Yes finally after lot of struggle :stuck_out_tongue:
Thanks to you too for taking time and trying to find solution :slight_smile:

1 Like

Good Job @chandrashekar_padmin thanks for sharing the code

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.