I thought I had this one figured out but it still doesn't seem to be working 100%
I am trying to use PAD so that it loops through the data in excel and sends one email with all matching records (rows) per unique email address.
The data set could be very large with 1000 - 3000 rows and the data is sorted by email.
Ideally when all matches for the email has been found (next row would not be a match due to it being sorted by email), stops and sends the email, then moves onto the next email in the list and repeats the process.
I'm very new to PAD so any help would be appreciated.
System.TerminateProcess.TerminateProcessByName ProcessName: $'''excel'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\man\\Documents\\TMS.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: 1 StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: True FirstLineIsHeader: True RangeValue=> ExcelData
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: 1 StartRow: 1 EndColumn: $'''D''' EndRow: FirstFreeRow - 1 ReadAsText: True FirstLineIsHeader: True RangeValue=> ExcelData2
Variables.RetrieveDataTableColumnIntoList DataTable: ExcelData ColumnNameOrIndex: 4 ColumnAsList=> EmailList
Variables.RemoveDuplicateItemsFromList List: EmailList IgnoreCase: False
Excel.CloseExcel.Close Instance: ExcelInstance
SET MatchOne TO 0
SET MatchTwo TO 0
LOOP FOREACH Email IN EmailList
SET TableBody TO $'''
<html>
<style>
th, td {
text-align: left;
padding: 8px;
}
tr:nth-child(even){background-color: #f2f2f2}
th {
background-color: #04AA6D;
color: white;
}
</style>
<body>
<table border =\"1\"><tr>
'''
LOOP FOREACH CurrentColumn IN ExcelData2.Columns
Text.AppendLine Text: TableBody LineToAppend: $'''<th>%CurrentColumn%</th>''' Result=> TableBody
END
Text.AppendLine Text: TableBody LineToAppend: $'''</tr>''' Result=> TableBody
LOOP FOREACH CurrentItem IN ExcelData
IF MatchOne = 0 THEN
LOOP FOREACH CurrentColumn IN ExcelData2.Columns
IF Email = CurrentItem[4] THEN
Text.AppendLine Text: TableBody LineToAppend: $'''<td style=\"width:220px\">%CurrentItem[CurrentColumn]%</td>''' Result=> TableBody
SET MatchTwo TO 1
ELSE
SET MatchOne TO MatchTwo
END
END
Text.AppendLine Text: TableBody LineToAppend: $'''</tr>''' Result=> TableBody
ELSE
SET MatchOne TO 0
SET MatchTwo TO 0
Text.AppendLine Text: TableBody LineToAppend: $'''</html>''' Result=> TableBody
Outlook.Launch Instance=> OutlookInstance
Outlook.SendEmailThroughOutlook.SendEmailFromMailbox Instance: OutlookInstance Account: $'''send@statsgroup.com''' SendFrom: $'''send@email.com''' SendTo: $'''recieve@email.com''' Subject: $'''TMS Profile''' Body: $'''<p><span style=\"font-size:16px\">Hi,</span></p>
%CurrentItem[4]%<P>
%CurrentItem[5]%<p>
<p><span style=\"font-size:16px\">Here is a copy of your current Training Profile.</span></p>
<p>
%TableBody%
<p>
''' IsBodyHtml: True
END
END
END
