Excel table to outlook body

Hi,

I am using for each row activity. In my excel, for transaction, there is 3 rows. how to I get all the rows into email body? currently the bot is sending 1 email per row.

I need the bot to consolidate those transactions that are the same, and send them in 1 email.

pls refer to the picture I have attached for the html code and the end result

1 Like

Hi,

Will work on this! Planning to start with Assigning Trasncation1 = DT.Select(“Header 1= ‘Transcation 1’”). Not sure if this works but will share solution in some time.

thanks! hope you can provide me some insights

Hi Popo,

Please look into the attachment. This might be what you are looking for.
Sample.zip (17.8 KB)

BR
Devbrath

could you screenshot the workflow. I’m using older version of uipath hence can’t see all the steps

Hi Popo,

I am working on UiPath ver 2018.4.2.
Used Package:
“UiPath.System.Activities”: “[18.4.2]”,
“UiPath.Excel.Activities”: “[2.5.1]”,
“UiPath.Mail.Activities”: “[1.3.0]”,
“UiPath.UIAutomation.Activities”: “[18.4.3]”,
“UiPath.Database.Activities”: “[1.0.7053.27728]”

I guess trying installing these packages, you might get the missing steps.

BR
Devbrath

Hello @Popo,

This is because your’re iterating whole html code for all rows.
I’ll suggest you to take .txt file, make your html code using logic, put that code in that .txt file and then using Read Text File you can pass it in body.

For this follow steps below: Logic

  1. By using Read Range get excel data in a DataTable variable. —> DT
  2. Use create file to create .txt file.

Firstly you’ve to get column names (Headers)

  1. Use Append Line and pass this —> "<html><body><table border = 1><tr>" and txt file path.
  2. take for each (Type Argument —> System.Data.DataColumn) —> col In DT.Columns
    take Append Line —> String.Format("<th>{0}</th>",col.ColumnName) —> to append column names in html code.
    take Append Line —> </tr> —> to close tr (outside the for each)

Secondly, you need to work on dataRows —> i.e. td. Now here you’ve to use For Each Row.

  1. Take For Each Row —> row In DT
    Append Line —> "<tr>"
    take For Each (Type Argument —> System.Data.DataColumn) —> col In DT.Columns —> to put data column-wise (like a dataRow)
    Inside for each,
    Append Line —> String.Format("<td>{0}</td>",row(col.ColumnName).ToString)
    Outside of the for each means, at the end of the for each row, Append Line —> "</tr>"
  2. After for each row, take Append Line —> "</table></body></html>" to end the script.
  3. Take Read Text File and display the MessageBox with output variable of Read Text file.
  4. once you conform the html code, you pass it to body parameter of smtp OR send outlook mail message. :slight_smile:

Sounds complicated. May i know for my understand, under which steps does the bot recognize if excel has more than 1 row, it will extract the relevant no of row? (Eg if row 2-4 is related to the same transaction, extract row 2-4 only. If row 5-9 is related to another transaction,extract row 5-9 only)

Will try it out!

See @Popo,

Read Range get all the sheet data in DataTable and For Each Row iterates every row from the DataTable.

NOTE : In case, you want all sheets data in mailBody 1 by 1, you might have taken for each, to iterate through to read range all sheets. So here, you have to put all those activities inside that for each and next to the read range. Otherwise it’ll take only last sheet data.

here I’m attaching sample workflow for your understanding, because I know you’re almost there. :slight_smile:

ExcelHtml.zip (19.2 KB)

Run this, by just adding send mail message activity at the last, you’ll get the exact idea.
And after that while making changes, don’t forget to add the logic which I mentioned in NOTE.

Hi Sir @samir

I have run the sample workflow that you provided. Is working fine however, it seems that the workflow will extract all the data in the sheet to create a table. The sheet that I’m working on contains many rows with different transactions. For example, Invoice 01 have 3 rows, Invoice 02 have 1 row, and Invoice 03 have 5 rows and so on…

Is there a way to create a table that consolidate Invoice 01 into 1 table with the 3 rows of transactions and send email. Consolidate Invoice 02 with 1 row of transaction and send another email etc?

Thanks

Okay @Popo,
So for that you have to make use of Filter DataTable activity.
Follow the steps below,

  1. After read range you’ve got whole datatable. So now take Assign activity and create one new DataTable variable —> DistinctInvoice
    DistinctInvoice = Dt.DefaultView.ToTable(True,"InvoiceColumnName")
    Where, put invoice column name instead of “InvoiceColumnName” (in quotes) and Dt is your output var of read range.
    —> So by this you’ll get distinct values from invoice column.

  2. Take For Each Row activity
    —> Row In DistinctInvoice

  3. Take Filter DataTable activity inside for each row and click on Filter wizard.
    Input DataTable —> Dt
    Output DataTable —> NewDt (ctrl + k create new datatable var here called NewDt)
    "InvoiceColumnName" = row(0).ToString
    Here you have to select operation as —> =
    So by this it’ll filter data for each distinct invoice value. So by this you’ll able to send filtered datatable according to invoice 1 by 1.
    Like as you said for invoice 01 for 3 rows, invoice 02 for 1 row.
    Now have to put cut-paste all further activities (from create text file to read text file) here in For each row means after Filter DataTable activity. So that all process will execute for every distinct invoice value.

Note: but now, you’ve to put NewDt instead of Dt at every further point in workflow. :slight_smile: