Automatically Send Invoices to Client Based on Information in Master Excel File.

[Pbayer14]

Sending invoices is a very labor-intensive process, and I hope there is an easy way to do it. My current process is as follows:

• I save all invoices to the current monthly folder that is titled “Month, Year”.
• The title of the invoice PDF is saved as “Month Invoice – Client Name” ex: January Invoice – Mr. Peter.pdf
• I maintain a master client excel file that has the name of the client and an associated email address to send the invoice to.

Is it possible to develop a power automate flow that can automatically send the invoices to the correct client? I am thinking that flow would identify and match the client name on the title of the saved PDF with the client name within the excel document and then send to the associated email in the excel file. Is this or a variation of this process possible?

[VictorIvanidze]

Yes that's possible. You can simplify the task if you change the name convention -

instead of "January Invoice – Mr. Peter.pdf" create "January Invoice – peter.doe@contoso.com.pdf"

[grantjenkins]

I would suggest trying to use a proper invoicing system rather than Power Automate here since I would imagine it would be very bad to send a client invoice to the wrong client. However, if this is your only option, then hopefully this will get what you're looking for.

Please ensure you perform thorough testing and use at your own risk.

For this example, I'm using the following Excel Table that has the Client Name and Email. You will need to ensure you never have a duplicate Name here, otherwise an invoice may be sent to the wrong client.

See full flow below. I'll go into each of the actions. Note that I've also built in some logic, so you get an email if the client's name wasn't found in your list.

When a file is created (properties only) will trigger when a new file (invoice) is added to my Client Invoices folder. You would point this to your Folder that contains all the sub folders for each year and month.

Get a row will try to retrieve the Client's email from the Excel file. The expression used to get the Client Name from the Invoice is below:

trim(last(split(triggerOutputs()?['body/{Name}'], 'Invoice -')))

If it successfully finds a matching record it will go into the parallel branch on the left (Get file content and Send an email (V2)).

Get file content retrieves the content of the Invoice using the Identifier from the trigger.

Send an email (V2) uses the Email returned from Get a row, and for the Attachments it uses File name with extension from the trigger and File content from Get file content.

If Get a row doesn't find a matching record it will throw an error. In our parallel branch to the left (Send an email (V2) Client not found and Terminate) we would Configure Run after properties to has failed on Send an email.

Send an email (V2) Client not found will send an email to you (or the team that manages the invoices) informing them that the client wasn't found and invoice not sent.

Terminate will ensure our flow will complete as successful, not as an error.

Below is a sample of the email that the client would receive.

And a sample of the email when the client's name wasn't found.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[Pbayer14]

This is AMAZING! Thank you! I think I will be ok using this strategy as my client names are corporations rather than individuals. I think that may help with sending to the wrong person. Is it possible to make the trigger a manual entry rather than when a file is created? Sometimes we have to adjust invoices, and I don't want them being sent multiple times. 

Could the trigger be a manual entry where I have to enter in the current "Year" and "Month" to initiate the flow?

THANKS AGAIN!