How to read datatable from email

How to read datatable from email and copy datatable out to another email?

image

hi @fairymemay
kindly refer below thread ,

which may aid you…
hpy learning… :grinning:

1 Like

If i have more than 10 email per month.
I think more resources.

Any suggestions?

Hi @fairymemay

Try this :point_down:

  1. Get the mail message using ‘Get Outlook Mail Message’
  2. Save the email(mail message) as .eml using ‘Save Mail Message Activity’
  3. Open the saved .eml file using start process activity. (it will open in outlook)
  4. Ctrl + A (Select) and Ctrl +C (Copy) on the email page and Paste the text into Excel file.
  5. Save the Excel file and get the content into Datatable using Read Range Activity.

If​ i​ use​ extractdatatables From Html.
Can​ use​ or​ not?

@fairymemay

Yes, you can !

You can use VB code to extract Datatables directly from Outlook email. I attached screenshot and VB code here. The input to the Invoke VB Code is html text.

Use Assign activity to get html from email body
HTMLText=mail.Headers(“HTMLBody”).Replace(System.Environment.NewLine ," ")

'Extract tables from HTML String and create datatables
'Input is HTML string, output is Datatable
'Reference system.data for datatable, system.Text.RegularExpressions for regular expression

VB Code in Invoke code activity
Dim table_pattern As New System.Text.RegularExpressions.Regex("<table.?>(.?)") ‘extract table
Dim tr_pattern As New System.Text.RegularExpressions.Regex("<tr.?>(.?)")’ extract row ‘tr’
Dim td_pattern As New System.Text.RegularExpressions.Regex("<td.?>(.?)") ‘extrac column ‘td’
Dim html As String = in_HTMLString ’ HTML string.
Dim DataTable As System.Data.DataTable
Dim DataTables As New List (Of system.Data.DataTable) ‘define a list of Datatables
Dim DatatableRow As New List (Of String)
Dim table_matches As System.Text.RegularExpressions.MatchCollection’
Dim tr_matches As System.Text.RegularExpressions.MatchCollection’= tr_pattern.Matches(table_matches(0).Value)
Dim td_matches As System.Text.RegularExpressions.MatchCollection '= td_pattern.Matches(tr_matches(0).Value)
Dim column_index As Integer
Dim table As system.Text.RegularExpressions.Match
Dim row As system.Text.RegularExpressions.Match
Dim column As system.Text.RegularExpressions.Match

table_matches= table_pattern.Matches(html)
'msgbox(table_matches.Count.tostring)
For Each table  In table_matches
'Add a datatable
    tr_matches=tr_pattern.Matches(table.ToString)
    If tr_matches.Count>0 Then ' There is at least one row
		td_matches=td_pattern.Matches(tr_matches(0).value)
		If td_matches.Count>0 Then 'There is at least one column
			DataTable =New System.Data.DataTable
			'add column names  to datatable
			For  column_index=0 To td_matches.Count 
				 Datatable.Columns.Add("Column"+column_index.ToString,GetType(String))
			 Next
			 'Add row and and 
			For Each row In tr_matches
				'Add datatable To datatable list					
				For Each column In td_pattern.Matches(row.ToString)
					'Remove the bracket <> and keep the values only	
					DatatableRow.Add(System.Text.RegularExpressions.Regex.Replace(column.tostring,"<(.*?)>",""))
				Next			
				'Add a new row			
				Datatable.Rows.Add(DatatableRow.toarray)
				DataTableRow.Clear 'clear list for the next row
			Next				
		   	DataTables.Add(Datatable)
		End If
    End If
Next
out_DataTables=Datatables.ToArray 'ouput datatables