Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-20-2018, 12:26 PM
etcib etcib is offline VBA Code to automatically complete mail-merge document for upto 200 recipients Windows 7 64bit VBA Code to automatically complete mail-merge document for upto 200 recipients Office 2007
Novice
VBA Code to automatically complete mail-merge document for upto 200 recipients
 
Join Date: Apr 2018
Posts: 2
etcib is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 04-22-2018, 04:42 PM
macropod's Avatar
macropod macropod is offline VBA Code to automatically complete mail-merge document for upto 200 recipients Windows 7 64bit VBA Code to automatically complete mail-merge document for upto 200 recipients Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 04-23-2018, 12:38 PM
etcib etcib is offline VBA Code to automatically complete mail-merge document for upto 200 recipients Windows 7 64bit VBA Code to automatically complete mail-merge document for upto 200 recipients Office 2007
Novice
VBA Code to automatically complete mail-merge document for upto 200 recipients
 
Join Date: Apr 2018
Posts: 2
etcib is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
Thanks,

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!?!?
Reply With Quote
  #4  
Old 04-23-2018, 02:23 PM
macropod's Avatar
macropod macropod is offline VBA Code to automatically complete mail-merge document for upto 200 recipients Windows 7 64bit VBA Code to automatically complete mail-merge document for upto 200 recipients Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

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
VBA Code to automatically complete mail-merge document for upto 200 recipients mail merge multiple recipients per page bmobrien Mail Merge 3 02-23-2012 10:47 PM
VBA Code to automatically complete mail-merge document for upto 200 recipients Mail Merge Recipients List TanMan Mail Merge 1 10-13-2011 06:36 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft