VBA code to transfer cells to Word letter

[helpcharity]

Hi,

I work for a charity near Liverpool (UK) and need some help please.

When we send our template introduction letter to our beneficiaries we have to include a selection of 5-10 (out of around 50) standard paragraphs of text to insert into a letter. Currently we use Word 2016, and set up a document with 50 "building blocks", but we're finding that a very inefficient process.

None of us are VBA literate, but so far we've worked out how to create a macro which we can run from excel and which then;
a) opens a "new" word document, and
b) copies the content of a single cell into the blank document

But ideally, we would like some help to create a macro which;

a) copies the contents of the selected cells in the excel worksheet
b) opens our template letter (in Word 2016)
c) pastes the contents of the cells into the letter at specific sections (e.g. at bookmark points)... Ideally we would also like to copy the formatting and colour of the text in the cells... instead of it just being copied as black text.
d) renames and saves the amended template letter (i.e. before the template letter is overwritten).

Hope all this makes sense.... and hope someone can help.

[macropod]

What you're describing is essentially a mailmerge, for which see:
https://support.office.com/en-us/article/Mail-merge-using-an-Excel-spreadsheet-858C7D7F-5CC0-4BA1-9A7B-0A948FA3D7D3
https://support.office.com/en-us/article/use-mail-merge-for-bulk-email-letters-labels-and-envelopes-f488ed5b-b849-4c11-9cff-932c49474705

Instead of trying to replicate the formatting of Excel cells in Word, though, you'd do better to have all the variable text embedded in IF fields in the mailmerge main document, so that all it need do is test particular Excel cells in order to determine which paragraphs to output.

See also the Mailmerge Tips & Tricks thread at: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html. There you'll find a lot of useful info about configuring a mailmerge main document, plus macros to output separate files from a mailmerge and split existing mailmerge output files.

[macropod]

Cross-posted at: https://www.mrexcel.com/forum/excel-questions/1095704-vba-copy-excel-into-template-word-document.html
Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3