Create tool to auto create a newsletter

[dori2o]

I've been asked to look at creating a tool which will allow users to input information which is to be shown in a newsletter.

I already have a termplate for the newsletter.

Does anyone know of any examples online of something like this?

Alternatively, my idea, is to have an excel sheet with:

Dropdowns for the section heading of the newsletter, i.e. Best Practice, Important information/updates, diversity, l9ocal information, etc
large boxes for text (the content of the newsletter)

Then at the bottom a button press which will paste the information into the newsletter template and add a fancy border around each area of text.

I know I can add the text based on bookmarks etc, but is there a macro which will allow me to select the 'next available bokmark, just as you can select the next available line/last line in an excel document?

I personally don't understand why the tool is wanted given they have a template, but it is and I've been volunteered to look into it.

Any help would be much appreciated.

[MarvinP]

Hi dori,

I'd be using Microsoft Word instead of Excel for this problem. If you open the newer versions of Word it gives Templates you can use for various types of documents. There are a few labeled news.

https://templates.office.com/?legRed...gs=CT010104328

[dori2o]

I agree, but I've been told to look at making an excel based tool that performs these tasks and effectively produces the newsletter for you.

I know how to add text to a word document from excel and I've made a tool in the past which allows you to send proforma letters with text included from excel,

I just wondered if there is an example of what they're asking me to produce anywhere.

Alternatively would it be possible to use a Userform in Word?

The text would be entered into the userform into the relevant boxes depending on subject, then at the click of a button the text is input into framed text boxes, colours changed to suit topic, and all necessary editing completed.

Would this not be an easier alternative?

If so can anyone point me in the right direction for writing the VBA code for such a userform.

[MarvinP]

Hi dori,

Excel is used with Word to do Mail Merge. See if these articles help and what it would take to do that using VBA.

https://support.microsoft.com/en-us/...e-a-mail-merge

https://www.thoughtco.com/spreadshee...-merge-4010446

About your "I've been told" statement above; there are lots of problems that can't be solved given the requirements assumed. One of my favorite statements after giving a VBA answer to a question is: "Can you do this in a formula and not use VBA?". Word and Publisher (both Microsoft products) are the tools for producing articles. Excel is the tool for dealing with numbers in tables and a simple database program. What if your boss wanted you to use a calculator to write articles instead of a typewriter? How should you respond?