Get OTP from mail

[blazino17]

How can I extract an OTP from the body of a text message in Blue Prism after retrieving emails using the Outlook VBO Get Received Items (Basic) action? The extracted email body is stored in a collection—how do I extract the OTP from it?

Additionally, the action retrieves all emails matching the subject, but I only need the most recent one, which is the last row in the collection, since it's an OTP. How can I ensure only the latest email is processed?

[PvD_Sweden]

Hi B,

About the last row:
The simplest way to get only the last row in a collection is to loop through the collection and save the data for each row to a data item. When you exit the loop after reading the last row, you'll have that last row saved in your data item.

Obviously, this is ok for collection with few rows, if your collection has many rows, you could start with a row count and then access the last row directly by index - which would be the fastest way to do it.

About the OTP:
Can you provide an example what the data looks like so we can determine an easy way to extract the OTP from it?

Happy coding!
-------------------
Paul
Sweden

[blazino17]

Thank you so much for responding and assisting, please , This is a sample dataset please note that the OTP is 849331.

From the last row this is the data;

<style>
_media only screen and (max-width: 600px) {
table[class=body] h1 {
font-size: 28px !important;
margin-bottom: 10px !important;
}
table[class=body] p,
table[class=body] ul,
table[class=body] ol,
table[class=body] td,
table[class=body] span,
table[class=body] a {
font-size: 14px !important;
}
table[class=body] p.large {
font-size: 16px !important;
}
table[class=body] .wrapper,
table[class=body] .article {
padding: 10px !important;
}
table[class=body] .content {
padding: 0 !important;
}
table[class=body] .container {
padding: 0 !important;
width: 100% !important;
}
table[class=body] .main {
border-left-width: 0 !important;
border-radius: 0 !important;
border-right-width: 0 !important;
}
table[class=body] .btn table {
width: 100% !important;
}
table[class=body] .btn a {
width: 100% !important;
}
table[class=body] .img-responsive {
height: auto !important;
max-width: 100% !important;
width: auto !important;
}
table[class=footer] .center-content {
width: 100% !important;
}
}

_media all {
.ExternalClass {
width: 100%;
}
.ExternalClass,
.ExternalClass p,
.ExternalClass span,
.ExternalClass font,
.ExternalClass td,
.ExternalClass div {
line-height: 100%;
}
.apple-link a {
color: inherit !important;
font-family: inherit !important;
font-size: inherit !important;
font-weight: inherit !important;
line-height: inherit !important;
text-decoration: none !important;
}
.btn-primary table td:hover {
background-color: #34495e !important;
}
.btn-primary a:hover {
background-color: #34495e !important;
border-color: #34495e !important;
}
}
</style>

<table border="0" cellpadding="0" cellspacing="0" class="body" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%; background-color: #f6f6f6;">
<tbody><tr>
<td style="font-family: Arial, sans-serif; font-size: 14px; vertical-align: top;">&nbsp;</td>
<td class="container" style="font-family: Arial, sans-serif; font-size: 14px; vertical-align: top; display: block; Margin: 0 auto; max-width: 600px; padding: 10px; width: 600px;">
<div class="content" style="box-sizing: border-box; display: block; Margin: 0 auto; max-width: 600px;">
<span class="preheader" style="color: transparent; display: none; height: 0; max-height: 0; max-width: 0; cpacity: 0; overflow: hidden; mso-hide: all; visibility: hidden; width: 0;">
Join cpay merchant
</span>
<div class="header" style="clear: both;text-align: center; width: 100%;">
<table border="0" cellpadding="0" cellspacing="0" style="line-height: 0;border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%;">
<tbody><tr>
<td class="content-block" style="font-family: Arial, sans-serif; font-size: 12px; color: #999999; text-align: center;">
<img src="https://cpaywebcom.s3-eu-west-1.amazonaws.com/images/email/merchantEmailVerify.png " alt="Welcome to cpay!" width="100%">
</td>
</tr>
</tbody></table>
</div>
<table class="main" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%; background: #fafdff; border-radius: 3px;">
<tbody><tr>
<td class="wrapper" style="font-family: Arial, sans-serif; font-size: 14px; vertical-align: top; box-sizing: border-box; padding: 24px; padding-top: 24px !important;">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%;">
<tbody><tr>
<td style="font-family: Arial, sans-serif; font-size: 14px; letter-spacing: 0.4px; vertical-align: top;">
<p style="font-family: Arial, sans-serif; font-size: 14px; margin-top: 0; color: #474747; margin-bottom: 24px;">
Dear
</p>
<p style="font-family: Arial, sans-serif; font-size: 14px; font-weight: normal; color: #474747;">
</p>
Your cPay verification code is
<p style="font-family: Arial, sans-serif; font-size: 16px; font-weight: normal; color: #3366FF;">
849331
</p>
DO NOT DISCLOSE!!! <br>
Sharing this code will cause account theft and financial loss.
The verification code expires in 5 minutes.
<p style="font-family: Arial, sans-serif; font-size: 14px; font-weight: normal; color: #474747; margin-bottom: 16px;">
If you don't know why you got this email,please contact us:
</p>
<p style="font-family: Arial, sans-serif; font-size: 14px; font-weight: normal; color: #474747;">
Email:

<a style="color:#1dcf9f" href="mailto:ng-support@cpay-inc.com"> ng-support@cpay-inc.com</a>
<br> Phone:
<a style="color:#1dcf9f" href="tel:+23418822222"> 0700 8833333 </a>
</p>
<p style="font-family: Arial, sans-serif; font-size: 14px; font-weight: normal; margin: 0;color: #474747;">
Thank you for choosing cPay
<br> The cPay team
<br> If you received this email by mistake, you can safely ignore it.
</p>

</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td class="wrapper" style="vertical-align: top; box-sizing: border-box; padding: 24px">
<table class="footer" border="0" cellpadding="0" cellspacing="0" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%; border-top:1px solid #bfc5c4">
<tbody><tr>
<td class="content-block" style="font-family: Arial, sans-serif; vertical-align: top; padding-bottom: 8px; padding-top: 8px; text-align: center;">
<p style="width: 100%">
<span class="apple-link center-content" style="font-size: 12px; line-height: 1.33; letter-spacing: 0.4px; color: #bfc5c4; display: inline-block; width: 375px;">
cPay Digital Services Limited, yuyund floor, ttpyul House, Plot 6
<br>Dr Nerurren Olssopqqo tyy, Artusa - Ierja, tygys
</span>
</p>
</td>
</tr>
</tbody></table>
</td>
</tr>
</tbody></table>
</div>
</td>
<td style="font-family: Arial, sans-serif; font-size: 14px; vertical-align: top;">&nbsp;</td>
</tr>
</tbody></table>
</div>

[PvD_Sweden]

Hi B,

Assuming the data always looks like that, I'd probably scan for the element containing 'Your cPay verification code is' using InStr() to get the position. and then read forward to the line with the OTP that follows that element. Whatever comes between '>' and '</p>' after the found position would be your OTP.

Alternatively, if you're handy with Regex (...I'm not...), you could use that to directly extract the OTP från the XML data using similar matching criteria as above.

Happy coding!
-------------------
Paul
Sweden