Read Excel sheets and compare to data table to send emails

Help
1

I am trying to read the table in the sheet titled “Email” and then get all of the sheet names in the workbook. If a name in Column A in the Email Sheet matches a sheet name, then I need copy the contents of that sheet and send an email to the address in Column B of the Email sheet.

I am getting all of the information from the Email Sheet and the sheet names but I am getting confused on how to compare them together. Any help would be appreciated.

Send Emails.xaml (9.1 KB)

Legend_Test_Forum.xlsx (17.5 KB)

2

Hi
Hope these steps would help you resolve this
—use a EXCEL APPLICATION SCOPE and pass the filepath of the excel as input
From this excel application scope activity get the output with a variable named out_workbook from the property workbook.
—inside the scope use a READ RANGE activity and get the output with a variable of type datatable named dt where the sheetname mentioned is “Email”

—then inside the same scope use a assign activity and mention as
arr_sheetname = out_workbook.GetSheets.ToArray()

Where arr_sheetname is a variable of type array of string

—next to this assign inside the scope use a FOR EACH activity and pass the variable arr_sheetname as input and change the type argument as string

—inside the loop use a FOR EACH ROW activity and mention the input as dt
—inside the for each row loop use a IF condition like this
row(“Tab Name”).ToString.Contains(item.ToString)

—if that is true it will go to THEN part where we can use a
—Read range activity and mention the input sheetname as item.ToString and get the output with a variable of type datatable named Finaldt
—then use a WRITE RANGE from workbook activities (search as workbook in activity panel) and mention as Finaldt and the sheetname you want with the file path of file as well

Then inside the same THEN part of if condition use SEND OUTLOOK MAIL ACTIVITY where mention the TO part as row(1).ToString and in ATTACH FILES property mention the filepath of that above file created

—next to this send outlook mail activity inside the then part use a BREAK activity to get out of looping once if any match found

—then next to this FOR EACH ROW loop but still inside the For each loop use a Assign activity and mention as
Finaldt = Nothing

Where Finaldt is the actual datatable variable defined in the variable panel with default value as New System.Data.Datatable that we send along the mail with excel

Cheers @rgardner5564

3

Thank you @Palaniyappan. Your solution got me about 99% of the way there. The only thing I am still having a problem with is the email is returning VBA instead of the actual contents. Am I just missing something simple in that final piece of the process?

Process

Email

1 Like
4

Yes buddy
We missed this part of Write range from workbook activities next to this read range inside the THEN part

Cheers @rgardner5564

5

Hi @Palaniyappan. I actually don’t want to attach the Excel file to the email. I just want to copy the contents of that tab and paste the contents of it into the body of the email.