Create ready-to-send e-mail in Outlook based on dynamic cell values in Excel

Hello,

I'm pretty new to macros and VBA and would be super grateful for some help with a pet project I've been working on over the past few weeks.

My project is a form where in the first worksheet the user selects various inputs from pre-defined (data validation) lists, which draws corresponding information from other "database" sheets (tables) and compiles it in an "output" sheet, which is basically the entire body of an e-mail, ready to copy and send. The recipient and CC addresses are stored in specific cells in a hidden sheet, and may change depending on the user's input in the first sheet. The from and BCC addresses should also be pre-set, but to a specific e-mail address which will not change depending on user input.

I'm at the point now where the entire form works as intended and generates the necessary output, ready to transfer to Outlook. I've created the command button, but I need help with the attached macro/VBA coding. I've found some simple tutorials for how to generate an e-mail from Outlook, but they all seem to include a fixed recipient, subject, body text etc., and I need those to be dynamic.

Here's the process I need automated:
1. Select cells B3 to B461 in the output sheet (this range will always be the same; empty rows are automatically hidden)
2. Copy the selection
3. Open Outlook and create a new e-mail
4. Set "From" (if possible) and "BCC" to a fixed e-mail address
5. Retrieve the recipient e-mail address from a specific cell in a hidden sheet
6. Retrieve the CC e-mail addresses from a specific cell in the hidden sheet (they are already stringed together in Outlook format, separated by semicolons)
7. Retrieve the e-mail subject from a specific cell in the hidden sheet
8. Paste the selection into the e-mail body
9. If possible, once pasted, convert the pasted selection from table to text using paragraph marks to separate text

I suppose this should be possible to do, and maybe it's not even that complicated, but it's currently too advanced for me to figure out on my own.

Any help would be greatly appreciated!

Thanks, I've created a sample workbook showing as simply as possible what I'm trying to do.

There is so much to find on this topic that I don't expect you to be helped much further by first putting some time into it yourself.

To start somewhere, study the link https://docs.microsoft.com/en-us/pre...ectedfrom=MSDN

I'm still trying to figure out those last two issues if anyone can help (most importantly the automatic conversion from Excel table to text in Outlook).

Not only did you shorten the entire code, it also solves both of my problems. Brilliant; thank you so much!

It's almost perfect now. I'm down to fine-tuning the font size, which is proving to be more difficult than I would have imagined, given that I do have prior knowledge of HTML and CSS.

If I don't address it at all, the text is pasted into the new e-mail as size 10px, even though the default in a new e-mail is 11px (which is what I want).

I've tried different variations of HTML and CSS manipulations, but it appears that the "px" unit or any decimals in the font size are completely ignored.

Thus the closest I've come so far is applying a span tag with inline styling, setting the font size to "15", which returns 11,5px in Outlook. If I set it to "14" it returns 10,5px. If I set it to "14.5" or "14,5", it goes back down to just 10px, i.e. decimals don't seem to work.

At this point I'm only half a font size away from having exactly the solution I imagined, so I suppose I could live with that, but it seems like there should be an easy fix for this too.

Either way, I really appreciate your help with everything so far!

Much appreciated, however I'm still unable to set font size to exactly 11px in Outlook, just like with my own HTML and CSS attempts. I've tried different combinations of values and units, with and without decimals, and it does manipulate the font size, but the closest I can get to 11px in Outlook is 10,5px or 11,5px. It's a very odd problem.