Inquiry about Power Automate Capability

[alvin1999]

Hi guys,

I am new to Power Automate and planning to use it.  I would like to seek advise whether this tool can fulfil my requirements without having the license. 

My requirements are:
- Able to process data from an Excel workbook with 2-3 worksheets. The maximum data rows of the Excel is 9000+. The data processing steps including apply IF Else statement, Extract 1st word from a column, capitalize column value, extract date from timestamp, calculate due date using formula.

-After data processed, I would like to connect to Outlook mail. I have 3 sets of email templates. Each of the email will be determined by a rule based on the due date calculated. The email templates have a picture, static body message and some information (in body message, email recipient and CC recipient) need to fill up based on the processed columns in previous steps, hyperlink to a website and email, bold and color certain words.  

-The ready email need to pop out on user screen to verify correctness and they can click the send button themselves.

-A summary of the report about the sent email in Excel (the column can come from the processed columns)

I have tried this building this task using Macro but it takes about 1 hours to present the email for user to verify the content. Thus, kindly advise whether I am able to use Power Automate Desktop to complete this task and also what are the things that need to aware of. 

Thank you.

[WillSG]

Hi @alvin1999  I hope you are doing well.

Here below you will find my comments to your inquiries.

Basically, you can do this with PAD (Power Automate Desktop), however keep this in consideration.

First Process: The Excel calculation (any kind of formula) must be done with Excel, so keep Excel perform what it is good at (all the formulas even Macros) and leave the rest to PAD.

So again the Excel calculation you have to use Excel formulas.

**Extract 1st word from a column= you will have to use 1st formula “Search” and then 2nd formula “Left” to get the 1st word.

**Capitalize column value= you will have to use formula “Upper” to convert all the values to the Uppercase letters.

And so on with the rest of the excel formulas.

Second Process: The Outlook part can be done with a “For Each”
 action on PAD after you have all your spreadsheet data.

Now, please take into consideration that it can be done with PAD, BUT it takes a few milliseconds, even 1 second to process each email plus the user time to review the email information.

Third Process: Review the ready email can be done (as far as I know) with a “Display Message” action, the message will look clear but without the html format. Once reviewed, the user can click YES or Ok, so the Bot can continue.

My recommendation here also is once the email has been sent, PAD can write back into to the spreadsheet in the specific row that he was working on, this serves as traceability, you can even track how long is it taking to send each email. As a result, you can estimate how long it will take to complete X number of rows.

Hope this helps,

If I have addressed your inquiry successfully, kindly consider marking my response as the preferred solution. If you found my assistance helpful, a 'Thumbs Up' would be greatly appreciated.

Additionally, I offer specialized consultancy and development services leveraging PAD. If you're interested in exploring these services further, feel free to DM me, and we can initiate a discussion.

Kind regards,

Will SG

Managing Director & Automation Lead

RAMS CR (Recruitment & Automation)

LinkedIn Profile

[alvin1999]

@WillSG thank you for your advise. 

The data processing part was taking about 1 hour to process using macro so  I would like to find another way to replace this process. Popping out email can be done within 1 minutes (so this is no the focus). As such, could PAD or any Microsoft tools (without using license) can replace the function of Excel formula and macro before generate the email?

Also, any programming languages need to incorporate or the workflows of the PAD can meet my requirements?

[WillSG]

@alvin1999  Based on my experience, PAD can meet your requirements, I don't see the need to incorporate additional programming languages.

My concern is the Macro processing part, it should not be taking that long neither, but there are some formulas that are delaying the Macro automation process, the =Today() formula is one those that needs to refresh for every single row, these are called "Volatile formulas" these are formulas in which the value changes each time the cell is calculated. These formulas recalculate every time Excel recalculates, hence the delay, also the the usage of the Select function into the Macro. Each time a cell is selected in Excel, every single Excel add-in (including think-cell) is notified about this selection change event, which slows down the automation. 

Now, if PAD needs to go over the 9k rows of data that will take a lot of time, at least 2.5 hours to complete the Data Processing part. That's why the Macro needs to be improved.

So, the high-level process should be as follow:

Step1-Data Process: this can be done manually (just drag and drop formulas) OR PAD can RUN the Macro Action which will do the same (Clean the data, and drag and drop formulas)

Step2-PAD reads the data from the spreadsheet, loops each row at a time and creates the email and the user approves the email, email sent.

Hope this helps,

If I have addressed your inquiry successfully, kindly consider marking my response as the preferred solution. If you found my assistance helpful, a 'Thumbs Up' would be greatly appreciated.

Additionally, I offer specialized consultancy and development services leveraging PAD. If you're interested in exploring these services further, feel free to DM me, and we can initiate a discussion.

Kind regards,

Will SG

Managing Director & Automation Lead

RAMS CR (Recruitment & Automation)

LinkedIn Profile