Forum Discussion
Excel and emailing
This is a pretty big project. I did something similar a few years ago where Excel would grab info from an Access database, refresh pivot tables, then email itself to the responsible sales manager. The best route is to use Power BI honestly and let it manage the security based on logins and row level security.
To do it from Excel would require a decent bit of VBA code and queries linking the email-able file with the source file, and then a table of end users and email addresses. And then with 190 providers, separate machine to run it on as absolute best case, it might crank out one per minute.
thanks Ed
I thought that might be the case .....is it possible to get some assistance in this forum ? Not sure where to ask
You can certainly ask, but you are right now talking about a fairly large project. For example, just emailing the files is a chore given the way Outlook blocks automated emailing due to security risks. You can look at this page for help on that, or use the CDO model, which will bypass Outlook and its security warnings.
Then you need to decide how to store the data so a macro can be written to:
- Get first user info
- determine what records that user has access to
- generate a report with that information
- send that file/sheet to that user
- Was that the last user? If not, go to 1
As I said, I have an old project that does this, but it is very tightly integrated with an Access database that was linked to an AS/400 database, complete with SQL statements in the VBA code. {shudder}
But that is a bit different from what you are doing, which seems 100% Excel. It would need to involve Power Query at a minimum, and possibly Power Pivot if you are going to report in something other than a raw table.
If you feel you are up to the task, break the project down into manageable components, and then ask specific questions on specific parts. I'd start with the email tests and links I provided above. If you can get that working, the rest seems very manageable, but this assumes you have knowledge of VBA. There is no way to do this without VBA.