|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VBA Code to automatically complete mail-merge document for upto 200 recipients
Hi All, Not sure this is even possible but I've got so far in a project as a novice I'm now wanting to push the boundaries that little bit more! The scenario: I have 200 rows of data - one row for each person; I have upto 200 column headers (in the first row), with an individual's data in the columns if relevant (if not relevant, the column for that row is set to blank) - the cells are filled in based on that individual's results and not all columns are always used. The columns are based on what a person can/cannot do and have one of three titles (one for each skill, 'Well#', 'OK#'' or 'Bad#') - it is this information I want to put into a single table for the individual (where# is a number between 1 and 54 for the possibility of 54 skills requiring analysis) I could manually create a mail-merge document using word/publisher that pulls in the things the person can do ('Well#'), the things they can start but not finish ('OK#') and the things they cannot do ('Bad#') - essentially putting all the well comments in one column of a feedback table, OK comments in the middle column and Bad in the end column...however, whilst I am capable of creating the mail-merge document manually, I am mindful that not everyone will be able to who might wish to use it. Is there any chance VBA (button) can be created/manipulated to automatically produce the tables (one for each row). The data table is on a sheet called 'Breakdown' and has columns surname, forename, Well 1, well 2,...well 54 (for the things they do well) OK1, OK 2,...,OK54 (for the OK skills) and Bad 1, Bad 2, ..., Bad 54 (for the the things they don't do well on - Well 1 relates to the same skill as OK1 and Bad 1 so only one of these will ever be filled in for each row. The required table has three columns - one for the comments in the Well section, one for the OK section and one for the Bad section. I'm a novice with VBA, but I've picked, plucked and manipulated basic code well enough to give something 'more exciting' a go. I hope the scenario makes sense! Thanks for any help! |
#2
|
||||
|
||||
You should indeed create the mailmerge main document with all the boilerplate text and field coding you require. Trying to code all this into a macro will end up generating something that's a nightmare to maintain. User's really shouldn't have any difficulty opening the mailmerge main document, answering 'Yes' to the SQL prompt, then finishing the merge. If you're wedded to the mailmerge being run from Excel, the mailmerge main document that you do the set-up on could then be automated.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Quote:
I know it is easy generate the mailmerge using the options through word/publisher, however I have been tasked to try and make the process 'bulletproof'. The excel sheet created and merged documents need to be used by upwards of 50 members of staff, most of which have very basic (if any) concepts of using a computer beyond typing a word document. I've taken bulletproof to mean 'can you create a button which will automatically generate the merge documents so that these *idiots* won't be able to do it incorrectly...' At the minute I use a publisher template just because I found inserting the fields (nearly 100 of them at most so far) a whole lot quicker than in word. Any ideas to make it bulletproof so that the incompetent colleagues don't get too confused!?!? |
#4
|
||||
|
||||
What's confusing about answering 'Yes' to a mailmerge SQL prompt, then clicking on Finish & Merge>Edit individual documents? Your *idiots* attitude to your co-workers reflects more on you than on them...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Recipients can not view the inserted object (PDF) from my mail merge document | Stacy | Mail Merge | 1 | 02-26-2015 11:24 PM |
Mail Merge only sends to a select amount of recipients. | Lb145 | Outlook | 2 | 05-07-2014 02:06 AM |
Mail Merge only sends to a select amount of recipients. | Lb145 | Mail Merge | 11 | 05-06-2014 03:15 PM |
mail merge multiple recipients per page | bmobrien | Mail Merge | 3 | 02-23-2012 10:47 PM |
Mail Merge Recipients List | TanMan | Mail Merge | 1 | 10-13-2011 06:36 PM |