Dynamic Email Generation via VBA - Extracting & Sending Data Across Multiple Sheets

[Jess1234556]

Hi there!

I'm working on a shared Excel tracker used by six team members. Each of us has an individual tab where we log incoming requests, and each request is assigned to a specific team, recorded in column I. Since workload fluctuates, requests for the same team may appear across multiple team member's tabs.

At the end of the day, we send updates to these teams, summarising their requests and statuses. Right now, this process is manual and prone to errors if we overlook requests logged in another team member's tab.

What I want to achieve:
I'd like to automate this process using a macro that:

1. Dropdown & Button
   • A dropdown menu on the dashboard tab allows the user to select a team name.
   • A button that triggers the macro.
2. Filtering Requests
   • The macro loops through all individual tabs, collects requests assigned to the team (from column I)
   • It extracts only relevant columns (e.g, request date, client name, manager, status, comments - all these columns exist in the individual tabs)
3. Email Generation
   • The extracted data is inserted into email as a formatted table.
   • The email starts with a greeting (e.g., "Hi, here's today's update") followed by the table.
4. Email Recipient & Sender
   • Each team has a designated recipient list (some have a shared inbox, others require sending to multiple individual addresses)
   • The "From" address must be the person responsible for that team.
   • The CC field is our team's email address.
   • I assume I can create a table in another sheet containing: team names, corresponding recipients' emails, and the "From" address.
5. Email Subject Format
   • [Team name], update DD/MM (e.g., Team A Update 06/02)

Questions:

• Is this feasible within Excel VBA?
• What would be the best approach for structuring this macro?
• How can I proceed? (I'm still a newbie at this!)

Any guidance would be greatly appreciated!

This is how every individual tab (A,B,C,D,E,F) is set up

This is how I'd like to set up the updates button (on the dashboard tab)

And this is an example of how I'd like our emails to be generated.

View attachment 122108