Urgent Help Required in Pasting excel table in outlook body

Hi,

I wants to paste below excel data table in same format in outlook body.
By HTML,it is removing all the number formats and color coding.

image .

Please suggest some thing.

1 Like

Hi @Mukul_Chandravanshi,

Refer the below post for reference.

It is not giving the same color coding and number format. I have tried this.

If you want to do this with HTML, you have to add a style sheet to the table. Also you have to convert the numbers to string to retain the format.

Can you please share the code if possible

Hi @Mukul_Chandravanshi

I have used shell scripting to copy and paste excel data in outlook body.
This preserves the format as well. Steps to replicate the process is as follows:-

Step 1 - Open a notepad and paste the below code and save the file with .ps1 extension

$x1 = New-Object -comobject Excel.Application
$UserWorkBook = $x1.Workbooks.Open(" excel path")
$UserWorksheet = $UserWorkBook.Worksheets.Item(1)
$UserWorksheet.activate()
$rgeSource=$UserWorksheet.range(“A1”,“D11”)
$rgeSource.Copy() | out-null
$Outlook = New-Object -comObject Outlook.Application
$Mail = $Outlook.CreateItem(0)
$Mail.Recipients.Add(“enter sender email”)
$Mail.Subject=“enter subject”
$oDoc = $Mail.GetInspector.WordEditor
$oRange = $oDoc.Range()
$oRange.InsertParagraph
$oRange.InsertBefore(“Hi Team,”)
$oRange.InsertParagraphAfter()
$oRange.InsertParagraphAfter()
$oRange.InsertAfter(" enter the mail body")
$oRange.InsertParagraphAfter()
$oRange.InsertParagraphAfter()
$oRange.Collapse($wdCollapseStart)
$oRange.Paste()
$oRange.Collapse($wdCollapseEnd)
$oRange.move(4,3)
$oRange.InsertParagraphAfter()
$oRange.InsertAfter(“Thanks & Regards,”)
$oRange.InsertParagraphAfter()
$Mail.Display()
$Mail.Send()

Step 2 - If you encounter the below error- “ps1 cannot be loaded because running scripts is disabled on this system.” then you need to create a .bat file using notepad to execute the shell script-

@echo off
color 1F
echo.

C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -File "shell script path "
:EOF
echo Waiting seconds
timeout /t 40 /nobreak > NUL

Step 3- You can then use start process activity in UiPath to execute the bat file.

The bat file calls and run the shell script which sends email.

Let me know if this helps.
Thanks & Regards,
Lipika

2 Likes

Hi @Lipika_Porey,

The excel path would be dynamic. So how would i pass that dynamic path in notepad file?

Hi @Mukul_Chandravanshi

This may not be an ideal solution but still can be tried-
You can use Run Powershell script activity from uipath to call the shell script like below. the activity is available under UiPath.Script.Activities

image

And in the shell script you can add the below code-

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.VisualBasic’) | Out-Null
$name = [Microsoft.VisualBasic.Interaction]::InputBox(‘Enter the File name’, ‘User Input Dialog’, “”)

This basically generates an user input dialog; where we need to enter the file name without the double code. using uipath activity you can enter the file name in below iput box

image

I am still trying to find a way to pass the dynamic file name using argument. Till then let me know if this works.

Thanks & Regards,
Lipika Porey

2 Likes

yes it works,but i am also checking for dynamic path.

Thanks for the help @Lipika_Porey

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