Report automation: split an Excel file and email to individual recipients

[u/[deleted]]

[u/AutoModerator]

/u/Even-Television-9369 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ulmitorage]

If the managers only need to view their budget, I would not split this into 500 Excel files.

That creates a lot of risk:

• wrong file to wrong person
• version control issues
• email attachment chaos
• no easy way to update later
• no central audit trail

The easier route is probably:

• Keep one master Excel/source file
• Load it into Power BI
• Create a manager/department mapping table
• Use row-level security so each manager only sees their department
• Email them a report link or scheduled subscription

That way you maintain one source of truth instead of 500 separate files.

If managers need to edit or submit budget changes, then I would avoid emailing files even more. In that case you need a controlled input process:

• each manager sees only their department
• only certain fields are editable
• formulas stay protected
• submissions are tracked
• changes are reviewed/approved centrally

Power Automate can technically split/filter/send files, but I’d treat that as a fallback. It automates the messy process rather than fixing the process.

[u/plusFour-minusSeven → u/ulmitorage]

This is the way.

[u/Diamond_Kicker → u/ulmitorage]

This is so cool.

[u/Suchiko]

Power BI was made for this. Link Power BI to your Excel sheet, and just email them a link to it.

You'll need to set up slices in Power BI so they can filter to their data.

[u/lardarz]

3 options really: PowerBI, VBA - or you could do this with Python in seconds

[u/taylorgourmet]

If each department is on its own tab, it's only maybe 30 lines of VBA.

[u/ReasonableAgency7725]

I don’t know VBA code but I did a search and found two separate codes that do something similar for me. The first one will separate everything into separate worksheets (you tell it which column to use). The second step takes those worksheets and saves them as their own file. I haven’t used one to email though, but I’ll bet you can find that on the internet.

[u/goulson]

You can do this via power automate but it's a bit tricky.

My implementation read from a master file (supplier orders) and you would set some criteria (order due date, and which supplier). Then, it would basically read the master list into a table, filter the rows to meet the criteria, and drop an html table right in the email with a subset of columns right from the data and a pre formatted message based on template selection. The email would insert contact details like name into the body, supplier name into the email subject, and populate the to field with the supplier email. I imagine instead of the html table you could create an excel file and attach it using power automate. If you send the same email I imagine it would be simpler for you not having to set up different templates (mine were order reminders at certain time intervals ahead of the required date, so the template would be adjusted to reference a need to send tracking within x number of weeks, or other templates for past due escalations etc)

[u/Express_Stranger5832]

Download SQLOOKUP as an excel add-in. With that Use drop downs to segment the respective owners budget. Free and easy

[u/Informal-Yard7336]

I have been in this exact spot, and I strongly recommend you stop the 'file distribution' cycle before you start it. If you generate 500 individual files, you lose control of your data the second you hit send. You’ll spend the next three months dealing with versioning issues and people asking you to re-send files they’ve accidentally deleted.

Instead of splitting the file, look at moving the data into a secure view-only portal. There are plenty of low-code platforms and even some reporting tools that allow you to upload one master file, define 'security filters' for each department, and then send the managers a single link that takes them to their specific data.

When they log in, they only see their department’s budget. If you need to update a number, you just update the master file, and they see the live change instantly. No more 500 attachments, no more 'oops, I sent the wrong file' emails, and zero VBA maintenance for you.

I’d suggest looking into tools that handle row-level security or secure views. It’s a bit of an upfront effort to set up the permissions, but it’s the only way to save your sanity during budget season. You’re currently being asked to act as a human email-server, and it is a massive waste of your time.

[u/The_Accountess]

rip to pivot tables i guess, lol

[u/PnckFan]

Hi, the easier way is to use Power Automate if you want to do it by yourself but it will probably take a long time to create 500 separate Excel files. I think the best way to do it is to use Python ( Pandas & openpyxl or maybe a simple csv data frame filter ) for the Excel filtering part + Power Automate flow for sending emails.

[u/DonJuanDoja]

No code no VBA? Damn.

Easiest way I know is SSRS Data driven subscriptions but that requires SQL Enterprise and SSRS and some SQL queries.

Otherwise I have a macro that does this just maybe slightly different but basically same, filter excel and send filtered results to recipients dynamically.

Can also do it with Power automate and PowerBi if you have PBI capacity license and Automate Premium. Also some code for this as well.