GENERATING AN EMAIL FOR EACH SHEET (insert sheet data between 2 sentences of email body)

[JuicyMusic]

Good Mooring,

I have been using a code that splits data from the "Source" tab into separate sheets. Each new sheet that is created ends up having the employee name as the sheet name - based on names in column A (starting at cell A3)

I need a code that will generate an email for each employee that has a new sheet. The number of new sheets will vary but never over 35 or so though, in case that is important.
The data from column A thru Z (thru to the last row of data) will need to be inserted in between the 4th and 5th sentence of a stock email from PR. I will provide the email template below. Every one will get the same wording.

The data set for each generated email should appear where I've indicated with a red rectangle on the 1st uploaded image. The user will be getting each employee's email address themselves.
the 2nd image is of the Source tab. I'm sorry that I can't upload this correctly. My company won't allow it.


"Source" tab specifics:
1) Row 1 has the report name and reporting month in cell A1 and B1
2) Row 2 has the headers - from column A thru Z
3) Column E & L & S & Z have no data in them. They are just highlighted black to visually separate 3 sections for data entry. For visual ease only.
4) Column G & I, and N & P, and U & W are hidden columns that contain helper formulas. They will always be hidden.
5) Each data set on each new tab has the exact number of columns - but the number of rows per new sheet WILL vary.

Thank you so much in advance,
Juicy

 

[JuicyMusic]

Mumps, the percentage results are appearing for those 3 new phrases. One thing only. Is it possible to change the percentage format to have a percentage sign and be 2 digits.

Attached shows the result on one of the emails so you can see. Example: 0.66666666667, should be 67%.

I can't think of anything else after this

Thank you!

 

[mumps]

Replace the current part of the code with this:

.htmlBody = "These are the percentages for the month shown below:" & "<br><br>" _
                    & "Performance Results for JSA:  " & Format(ws.Range("K" & lVisRow) / 100, "0%") & "<br>" _
                    & "Performance Results for TAILGATE:  " & Format(ws.Range("R" & lVisRow) / 100, "0%") & "<br>" _
                    & "Performance Results for PANDEMIC:  " & Format(ws.Range("Y" & lVisRow) / 100, "0%") & "<br><br>" _
                    & "Below is the Monthly Safety Compliance Breakdown " & _
                    "and your ISA's. Tailgate Meetings and COVID-19 checklists are attached with feedback for your review." _
                    & "<br><br>" & "Here is the feedabck on your monthly tailgates, ISA's and COVID-19 checklists:" & "<br><br>" _
                    & RangetoHTML(rng) & "<br><br>" & "Please let me know if you have any questions or concerns on any of this information."

 

[JuicyMusic]

Hello Mumps, I pasted the section above in the correct area. The results are attached. The percentages are off still. So close.

 

[mumps]

Try:

.htmlBody = "These are the percentages for the month shown below:" & "<br><br>" _
                    & "Performance Results for JSA:  " & ws.Range("K" & lVisRow) & "%" & "<br>" _
                    & "Performance Results for TAILGATE:  " & ws.Range("R" & lVisRow) & "%" & "<br>" _
                    & "Performance Results for PANDEMIC:  " & ws.Range("V" & lVisRow) & "%" & "<br><br>" _
                    & "Below is the Monthly Safety Compliance Breakdown " & _
                    "and your ISA's. Tailgate Meetings and COVID-19 checklists are attached with feedback for your review." _
                    & "<br><br>" & "Here is the feedabck on your monthly tailgates, ISA's and COVID-19 checklists:" & "<br><br>" _
                    & RangetoHTML(rng) & "<br><br>" & "Please let me know if you have any questions or concerns on any of this information."
                .Display

 

[JuicyMusic]

Good Mooring Mumps, I pasted the section you just sent me, with the % sign and no "100". I only changed the Y you had to a V. Your code works great and the results for the three phrases are good in some instances.

I've attached 2 images.

1) It looks like the 100% comes back as 1%.
2) There shouldn't be a percentage sign in the result if there is a decimal point. For example, 80% should come back as 0.80 (without the sign). 22% should come back as .22 (without the sign).

Can we get the number with the percentage sign and not the 2 digit extension. Thank you for staying with me to the end.

 

[mumps]

Are the cells in columns K, R and V containing the percentages formatted as "percentage"?

There shouldn't be a percentage sign in the result if there is a decimal point.

I'm not sure what you mean by the above statement. Please explain in detail using a few examples.

 

[JuicyMusic]

Mumps, I mean that eighty percent is either written as 80% (without a decimal point before the eight)....or as just 0.80.

I only messed around with the formula that you have in that section of code and I got the results to drop the decimal point but keep the percentage sign. Please let me know if that okay to do it this way - or is it too messy?


I've uploaded 2 results from the math that I tweaked.

& "Performance Results for JSA:  " & Format(ws.Range("K" & lVisRow) / 10 * 10, "0%") & "<br>" _
                    & "Performance Results for TAILGATE:  " & Format(ws.Range("R" & lVisRow) / 10 * 10, "0%") & "<br>" _
                    & "Performance Results for PANDEMIC:  " & Format(ws.Range("Y" & lVisRow) / 10 * 10, "0%") & "<br><br>" _

 

[mumps]

Are the cells in columns K, R and V containing the percentages formatted as "percentage"?

If your code works as you want, then it's great.

 

[JuicyMusic]

YOU! ARE AWESOME!!! Thank you for sticking with me to the end. I love reading thru posts that have a conclusion like this one.
SOLVED!

And the answer to your question is K, R, & Y

Have a wonderful strong end! Juicy,

 

[mumps]

You are very welcome.