Need VBA code to popup Email Depends upon userform of Segment

Do you want to select companies one by one or do you want to run the report for the companies all at once?

Here is a version that lets you mail the reports one at a time. I notice that tables 3 and 4 are "fixed."

I added a Mail Setup sheet to do lookups and some computations.

The table in columns A:C are used to select the companies and to get the email addresses for the TO and CC parts of the email. As you add or delete information, the lookups will change to show what is current.

The mail flag can be either true or false. If it is false, the mail is prepared and put on the screen, but not sent. This gives to an opportunity to edit it. If the flag is true, it goes out automatically.

I also have a Mail Body sheet which collects and formats the data for the selected company.

The Raw Data Records is a check calculation. The pivot table filters can't be set if there is no data for the company, so I make a check for that and give you a warning.

I also do a check to see if there is no address for the TO block in the email.

Select the company for which you want a report and click on the mail report button.

I have Excel 365 which has XLOOKUP. If 2019 doesn't have this command, you can use VLOOKUP.

You can use the MailMain and RangeToHTML modules as "plug and play" for future projects. All you have to do is pass the parameters.

i want make send for all companies in one shot in cmd button ?Popup with email body of each company. then i click send to fly.
i want run code from new sheet as index, dashboard sheet should be hide becoz logic is other team have no clue of others data

I am a little bit confused by the requirement.

You can hide whatever you want, but it still needs to be there to run "behind the scenes."

The user form seems to be set up for one company at a time. I see where the combobox is, but I don't know what the other three fields in the form are for.

Here is a version that launches the form. At the moment I have the code "stubbed out." You can select all which means that mail will be sent to all companies, or you can select a specific company which means that mail is sent ONLY to that company. The form stays open until you click the exit button. Let me know if you want to pursue this design.

I assume that you are ok with the overall design, so here it is "hooked up" to the mailer.

I hid all the sheets except for the index sheet. You will still have to configure the companies in the mail setup sheet. You can delete the Expected_Result sheet

The error message doesn't make sense. It's saying that what is in cell G2 on the Mail Setup sheet (Named Mail_To) is something other than a string. What kind of data do you have in the To column on the Mail Setup sheet? Also, does it fail on the first record or if some other record, what line does it fail on and what does that data look like?