Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail merge (2000)

    Hi again.
    Im not sure how i should go about doing my next task, mabey someone has done something similar or at least has some tips.
    We have a large table of customers, but there are different types of customers and they wont all always need to be included in the mail merge.
    I wanted to make a system where there is a form with check boxes so we can check the types of customers to be included in the mail merge.
    Then i suppose it would generate a table/query or file that could be used for the mail merge.
    Thinking about it now... i just dont know how to go about it... we could probably make a query and just change it whenever the list needs to be different, but some of the employees here dont know how to edit queries.
    Can Word be used as the email editior for something like this?
    Thanks in advance
    Adrian

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

    Re: Mail merge (2000)

    You can use check boxes or a multi-select list box on a form to create the SQL statement that selects the customers. You can then use this SQL statement to open the data source for the mail merge, or save it as a query and use that as data source for the mail merge.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Mail merge (2000)

    What you could do is to introduce a field in the Customers table that tells what kind of customer it is. You have another table of customer types (called tblCustomerTypes). Introduce a multi select list box on the form that has the Cusytomer Types as it's source. Then you mark on the multi select list box the customer types you require, then when you press a button to do the merge you build a query based upon the types chosen and use this as the source of the mail merge.

    Another way to do this is to include a Yes/No field in the CustomerType table that is set when you analyse all the selections that you have made in the list box. Then just run the mail merge based upon two tables joined on CustomerType where the Yes/No field is set to a yes. Obviously you would have to clear all the Yes/No field records in the Customer Type table prior to running this function.

    How you would use Word as the email editor for something like this is beyond me. that's for someone else to tell you.

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

    Re: Mail merge (2000)

    What about using a maketable query? You could design the query to accept parameter input from combo boxes on a form, or check boxes.

    You then run the query and open the Word Mail Merge document from a line of code. All your user has to do then is to make the appropriate selections on the form (use screen tips to guide them) and click two buttons (the one to run the query and launch Word and the one in Word to run the mail merge.

    The table made from the make table query can either be left until the next time (and over written) or deleted using VBA. You will need to set the warnings to off.

    Hope this helps

    Ian

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

    Re: Mail merge (2000)

    In a single-user environment, this should work OK.
    In a multi-user environment, you must be very careful, for you don't want user A to overwrite the temporary table just created by user B. So in that case, you should give the temporary table a unique name.

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

    Re: Mail merge (2000)

    Whoops, sorry, missed that one out. The only DB I use this technique on is a front end back end set-up (front end on local PC, back end on network server) and produces the 'new' table in the local front end. I was told this would minimise the chances of corruption in the main back end of the database. It also got over the problem of two users wanting to do the same job at the same time.

    I have a small BAT file on each PC that the users are instructed to use when ever I change the front end of the system, or a related document like the Word mail merge file.

    Alternately, you can use OutPutTo in VBA to create an Excel file on the local HDD (DoCmd.OutputTo acOutputQuery, "MyQueryName", acFormatXLS, "C:MyStorageLocationMyFileName", False) and set the Word Merge document to use the Excel file you generate as the merge data source, the example code will not open the Excel file, you probably don't want it to do that as the Word merge file will open the data source.

    To open a Word document I've used this code before, it was suggested by some one on here and works fine.

    Set appword = CreateObject("word.Application")
    appword.Visible = True
    appword.Documents.Open "C:FileLocationMailMergeDocName.doc"

    Neither technique is high tech (I can't do high tech), but I've used both of them and they work.

    HTH

    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
  •