Excel and emailing

[mmldogs665]

Hi Guys 
I didnt know where else to put this . Issue came up today to find the best way to email from an excel spreadsheet. Not an expert in excel so any advice is welcome.
My issue is I have a spreadsheet that is 2500 lines long and is run monthly so the information varies. Each provider has several client numbers which identifies the client number that the provider is doing work for. I need to be able to email the provider with an update of his or her client numbers. Each provider has an email address which doesn't come in the sheet .

So in the example provider 1123 should get an email that contains the client numbers 89965 and 85447
I have around 190 providers in this sheet .

I have no idea how to do this and it has to be done on a regular basis. Can it be done from excel ? Heres hoping

providers           client number     discipline     subclass     year level
1123                  89965                     x                 even                5
1123                   85447                    y                 over                 3
55655                  123                       p                  under              4
5565                     8787                   q                    even                5
855555                 654                       z                    under             2

[#2]

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.

[#3]

thanks Ed 

I thought that might be the case .....is it possible to get some assistance in this forum ? Not sure where to ask 

[#4]

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:

1. Get first user info
2. determine what records that user has access to
3. generate a report with that information
4. send that file/sheet to that user
5. 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.