Need Help: Automated E-Mail for data acquisition

[Anonymous]

Hey everyone,

I have to do a project for my final exam of my apprenticeship to a merchant in e-commerce. My project has the topic process optimization in the area of data acquisition.

To optimize the processes i had the idea to automate the data acquisition of suppliers and manufacturers by using power automate. I never used power automate before and therefore do not know how to automate what I want to automate and i also have no prgramming experience. First of all let me explain you which idea I have and then follow up with the questions i want to ask you all.

I want to send a fully automated email to a single or to several recipients. The recipient should be selected out of an excel list by its unique ID number (every recipient has its own unique ID, that will be picked out of the column), so that the email will be sent to the current recipient. When the email has to be sent, there are different files, that have to be attached to the mail automatically. These files will be updated automatically out of our database.

The mailtext is already written, except some placeholders like the recipient and the name in the opening. The placeholders should be automatically filled with the informations that are attached to the unique ID of the recipient.

The aim is to have two different options in the end:

Option a) To have a manual trigger (Button) to send the complete email (with the attachment)

Option b) To send the email fully automated by fixed times or timeframes. For Example: 3 times in a year at a certain day

Conclusion:

So in my eyes there are these processes, which I´d like to automate with power automate:

1. Fill the placeholders in the email with the correct information out of the excel sheet from the unique ID - The name of the recipient - The correct mail address

2. Attach the right files automatically to the email

3. Send the email automatically to the recipient

a) with manual trigger

b) automated by selected time

Now my questions:

Can I automate all these processes with power automate?

If yes, where can I find the information to these processes. I tried to find them in the guides/tutorials, but there is simply too much information for me, so that I get easily overflooded and loose the overview.

And if I can´t automate everything of this with power automate, which programs should I use additionally?

I really need some help, some hints….anything, please.

Am really glad if someone can help me.

[Rhiassuring]

Answers to your questions:

Can you do all this with Power Automate?  Yes, you can.

Where to find the information?  Here, basically.  You will either need to take the time to learn step by step on your own, or, hire someone to do the flow for you.

I can try and dig up posts with solutions that will be most relevant for you, and the general basis of your flow is pretty normal, but when you say "These files will be updated automatically out of our database", we will need to know what your database is. Is it SharePoint? Is it SQL? Is it an On Premise tool only? That may require the use of Power Automate Desktop.

Are you able to give some more detail around the database?

Cheers,

Rhia

[Anonymous]

Hello Rhia,

it would be awesome, if you could dig up some posts with solutions, if it doen´t bother you too much or doesn´t take too much time. I don´t mind learning it at all...i mean thats what i want to do. In the meanwhile i will search for in this forum for answers, too.

The Excel files (that need to get attached) i talked about above, are generated by a sql query out of the database that is connected to our erp system. Some of the files have to get edited manually after creating them, but as long as the filename is the same or the location of the file is always the same, it shouldn´t be a problem to use them for the flow, right?

Also the excel files with the informations (mail adress, ids and so on) of the suppliers and manufacturers will be generated out of this database. This file will not get edited afterwords (especially not the IDs).

So there is no problem in creating and saving these files. I just have to get them automatically attached to the mail.

Thanks for taking your time and effort to help me.

[Rhiassuring]

That's all good, then! And yeah, Excel files - even if the name changes - not a problem. 

Let's get you started with some good practices. 

Since you want to use two triggers - "Manual" or "Scheduled" - you'll probably want to keep your 2 flows together in a Solution. You'll find this in the left hand side of your Power Automate portal. This means you'll be able to call another flow from within the solution, rather than rebuilding it twice / copying it / trying to keep them in sync. 

For example, if I create a Solution called "Run Report" and then within I create a Scheduled Flow called RunReportScheduled, then a manual flow called RunReportNow, I can call "RunReportNow" from within "RunReportScheduled" - so really we have one flow that does any work (RunReportNow), and the other one is just a timer, really.

So the main flow, RunReportNow, will have a "Respond to PowerApp or flow" action in it, just so it can interact with the recurring trigger - and pass dates or info that you might want.

From here, well, it's all about Excel.. if you're just getting the files and attaching them, you'll just tell Power Automate to get file content and file name from where they are, and construct the attachments that way. You'll note in an email action there's a space for File Name and File Content - the File Content thing is what trips most folks up. 

If you need to get data out of Excel, you can use List Rows Present in a Table and use the Filter Query to find what you need. 

That should get you started...