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

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
210
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture_Stock Email Template.PNG
    Capture_Stock Email Template.PNG
    15.9 KB · Views: 16
  • Capture_Source tab data.PNG
    Capture_Source tab data.PNG
    28.6 KB · Views: 14
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!
 

Attachments

  • Capture_Percentage format.PNG
    Capture_Percentage format.PNG
    53.3 KB · Views: 7
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Replace the current part of the code with this:
VBA Code:
.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."
 
Upvote 0
Hello Mumps, I pasted the section above in the correct area. The results are attached. The percentages are off still. So close.
 

Attachments

  • Capture_Percentage_not quite right.PNG
    Capture_Percentage_not quite right.PNG
    51.7 KB · Views: 9
Upvote 0
Try:
VBA Code:
.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
 
Upvote 0
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.
 

Attachments

  • Capture_results with % sign and no 100 in code_good result.PNG
    Capture_results with % sign and no 100 in code_good result.PNG
    59.9 KB · Views: 7
  • Capture_results with % sign and no 100 in code_result for 100percent shows as 1percent.PNG
    Capture_results with % sign and no 100 in code_result for 100percent shows as 1percent.PNG
    68.6 KB · Views: 9
Last edited:
Upvote 0
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.
 
Upvote 0
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.
VBA Code:
& "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>" _
 

Attachments

  • Capture_results w juicymusic math changes1.PNG
    Capture_results w juicymusic math changes1.PNG
    55.8 KB · Views: 10
  • Capture_results w juicymusic math changes2.PNG
    Capture_results w juicymusic math changes2.PNG
    65.8 KB · Views: 9
Upvote 0
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.
 
Upvote 0
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,:)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top