Help in copy and paste table from excel to email

need help or rather tutorial Copy and Paste table from excel to email body by keeping format

Do you get same Format every time in Excel table?

yeah

try to set text

You can check my ans for similar query-

Hi @Lipika,

Can yo u please let me know how can we specify multiple email recipients as i have some 100+ individuals to be sent in one email.
Also , please let me know how to close excel process after run?

Regards,
Padmini

1 Like

HI @chandrashekar_padmin,

If you want to keep the emails(Sending Multiple Recepients) in CC or BCC,mention the emails like below in text file and pass the text file variable(Create variable type as string) in CC or BCC .
image

For Kill Process,use Kill Processa activity and mention the “Excel” in Process name field.
image

Hi Rajesh,

So manually i have to give ; to each email and paste in text file?
Also what if i don’t have exact email id , i have first name,last name how can i convert into email for all of it?

Hi @chandrashekar_padmin,

If you have email id’s in excel or text file in line by line ,first convert them in to the format in which i have mentioned in my first post…

For converting use the below xaml:
Mail Format.xaml (5.1 KB)

Use string manipulation to create email.
Firstname+Secondname+"@gmail.com"

1 Like

Hi Rajesh,

I tried this and working fine . can you please share me xaml or power shell code for declaring the variable. I am bit confused … i have saved the .ps1 file and tryin to run from power shell itself not uipath.
I am not very sure of how to do a power shell scripting

@chandrashekar_padmin
How you are sending email,using which activity?

Hi Rajesh ,
I am using powershell code as given by Lipika as i wanted to use the same format as in excel like this. :slight_smile:

I have no idea on Power Shell scripting ,but reading the email file(variable as String) and assigning in Mail as attached for your reference.

Mail Format.xaml (5.0 KB)

Hi Rajesh,

Same here , or can you pls give any workaround to copy and paste the same format of the table which i mentioned above ?

Regards,
Padmini

1 Like

Hello @chandrashekar_padmin,

Kindly check this ans for passing the sender email address in the variable.

and for closing the excel you have to append the below code in .ps1 script
$UserWorkBook.close()

And It will be better if you can add all the email addresses in a single variable and then pass that in shell script.
The above link will help you to achieve that.

In case you need any help you can message me.

Thanks & Regards,
Lipika Porey.

1 Like

Hi @Lipika_Porey @chandrashekar_padmin

Can you help me on below datatable in to email body if you done before with this scenario?
image

Hello @RajeshT

I have done the same using powershell- refer the below link for detailed steps description
Urgent Help Required in Pasting excel table in outlook body

1 Like

@Lipika_Porey After copying the data ,how we will execute power shell script,I see second step if we got that particular error then only we need create a bat file.To check whether the error is coming or not how i can proceed?

@Lipika_Porey.

I got it ,run the script with “Run Power Shell Script Activity”.
But i got this error:
“Run power shell script: One or more errors occurred”
Please check the code i have copied:
$x1 = New-Object -comobject Excel.Application
$UserWorkBook = $x1.Workbooks.Open(“C:\Users\Desktop\ ReportValidation.xlsx”)
$UserWorksheet = $UserWorkBook.Worksheets.Item(2)
$UserWorksheet.activate()
$rgeSource=$UserWorksheet.range("A1",“C10”)
$rgeSource.Copy() | out-null
$Outlook = New-Object -comObject Outlook.Application
$Mail = $Outlook.CreateItem(0)
$Mail.Recipients.Add(“rajesh@gmail.com”)
$Mail.Subject=Report
$oDoc = $Mail.GetInspector.WordEditor
$oRange = $oDoc.Range()
$oRange.InsertParagraph
$oRange.InsertBefore(“Hi Team,”)
$oRange.InsertParagraphAfter()
$oRange.InsertParagraphAfter()
$oRange.InsertAfter(“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()

Hi Rajesh,

Please find below vba for solution:
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

2 Likes