Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail merge one to many from Access (Office 2k, win 2k)

    Hello All

    I've got a question, is it possible to mail merge an Access query so that the resulting document has one of field 'A' and as many of fields 'B' through 'Z' as are required.

    To try and make things more clear if it was a report on a person and their possessions, field 'A' could be a the name of a person, field 'B' a car registration (this person owns many cars), field 'C' is the address (many houses) etc.

    I can do it in an Access report, but want (read the Boss wants) to email an editable document to people, and the .rtf file loses almost all the formatting, hence my crash course in Word and mail merge.

    Hope this isn't a daft question, but I couldn't find any reference to one to many mail merges by searching the lounge.

    Thanks

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    Hello Ian,

    MSKB article WD97: How to Merge Conditional Number of Records to the Same Page might give some inspiration, although I don't think it will work for several fields, each with a different number of values.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    Hans

    Thanks a lot, I'm beginning to think of you as my sanity's guardian angel <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I hadn't realised that the MS knowledge base contained this sort of tip (guess that's another one for my browser's favourites list <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>).

    As you said, the article doesn't do exactly what I want, but after a quick read through, it appears to have the basics I need to achieve a working result. The ability to list all projects for each employee is the bit I needed, the rest is just formatting the Word document (I hope that these aren't 'famous last words <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ).

    Thanks again

    Ian

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    There is another approach we sometimes use, especially if we are creating a single (or just a few) documents. It involves using Automation from Access to create a Word document, and as a result you have almost total control over what gets put where and when it gets put. That lets you easily mimic a subreport as you might do with an Access report. The downside is that you loose the Merge to Email that you get with the Word merge, so you also have to use Automation to create an email with an attached Word document. All in all, it sounds like a fun project.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    Wendell

    Yup, it's a 'real fun project' OK.....

    If I was to delve into the Access automation route, I'm thinking over the next few months while I improve my programming skills, would I be able to use a Word template to place all the relevant fields into? The resulting Word document is going out from the company I work for and has to have the company letter head on it.

    The maximum likely number of letters to be produced during the merge procedure is around 40, would this be too much for automation to handle?

    Thanks for the information

    Ian

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    Hi Ian,
    Actually when using Automation, you don't have to start with a template, since conceptually you are actually "typing" into Word. And you don't really need fields anymore, though you will find Bookmarks very useful. Putting the company letter head on it may be a challenge, depending on the logos involved, graphics, etc, so that would be a good reason for a template. 40 letters wouldn't be a problem - in some cases Automation is quicker than a Word mail merge, but you would need to program the looping to create multiple documents one after the other. One of the next tutorials we plan to do for our web site is on Automation, but it isn't done yet. You might want to look at The Access 2000 Developers Handbook (2nd volume I think) which has a fairly extensive chapter on using Automation to drive Word, Excel and Outlook. You might also want to look at MS Knowledge Base articles ACC2000: Sending the Current Record to Word 2000 with Automation and HOW TO: Use Automation to Send a Microsoft Outlook Message using Access 2000.

    The bottom line is either approach will work - both involve some learning and exploring. Good luck.
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    Ian,

    If you want to look at a couple of articles that deal with Word Automation from Access, have a look at the 2 articles, that I wrote, at http://www.databaseadvisors.com/news...s/news0207.htm

    The first deals with just the basics of Automation, how to open Word, how to create a new blank document and how to insert some text.

    Article 2 deals with selecting a record from an Access Form and putting the data in a prebuilt Word document.

    Yopu could very easily substitute a Word Template for a document.

    40 documents should be no problem for automation to handle, you would just need to create a loop to create the different number of documents.

    Automation isn't really all that difficult to do, once you learn the Word Object Model.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  8. #8
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    Wendell / Brian

    Thanks for the information on automation, I downloaded the files and spent last night reading them. I doubt I'll be able to get it working in the current project by my deadline, but as it's a company database I can make the modification when I'm confident with this new method.

    It's not the automation bit that's really putting me off, it's the multiple records. Even using the article that Hans pointed me at I get strange results sometimes so can't use it until I know what the cause of the variation is (don't know if it's my use of the Word fields, or my data set).

    Thanks for the help

    Ian

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    Understood - the Word object model isn't intuitively obvious for people who work with Access. In fact I suspect it isn't for most people.

    You might try an expedient of merging a single email at a time, and use the catalog style merge so all of the records for one person are included. For 40 people that might get you through what you need to do by your deadline.
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge one to many from Access (Office 2k, win 2k)

    Wendell

    I've promised the boss to try the automation route over the next three months, and he's accepted the current solution of one email per primary reference, this will mean some folk getting several emails at once, but with a max of 40 and an average contact base of 20 then I doubt any one is going to be too buried by emails for the next few months.

    I should be receiving Litwin, Getz et al's Developers set for 2000, along with their VBA book. Hopefully these, along with the support I get on here, will solve my problems.

    Besides, I don't want to offer too much, because every time I show them I can do 'the impossible' they think up some thing even more devilish <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Thanks

    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •