Creating mail merge letters using stored procedures

In the following example, letters are being prepared to mail to the parents of each future student who has an interview booked with the Head of Campus, using the crspFutureActivityPrint stored procedure.

An activity has been created on the Activity tab of Future Student Maintenance. See Future Student Maintenance - Activity tab in the Future students manual.

In the example, the interview is booked with Sarah Adamski and her parents, as displayed on the:

• Activity tab (selected above)
• Modify Activity window (seen below).

Activities are selected for the mail merge based on their Activity and the Mail Request field. However, if the Mailed Date and Mailed By fields are populated with values, this means that this activity has already has been printed.

The Activity field codes are maintained in the luFutureActivity lookup table. In this example, INTBOOK has been created for interviews that have been booked. See Maintaining lookup tables in the System maintenance manual.

To create mail merge letters using a stored procedure, such as the crspFutureActivityPrint procedure:

1. Open Microsoft Word.
2. Select Tools > Letters and Mailings > Mail Merge (Microsoft Excel 2003).

   Step 1 of the Mail Merge tab is displayed on the document window.

   

3. Select the type of document you are working on.
4. Click Next: Starting document.

   Step 2 of the Mail Merge tab is displayed on the document window.

   

5. Select the starting document.
6. Click Next: Select recipients.

   Step 3 of the Mail Merge tab is displayed on the document window.

   

7. Click Browse.

   The Select Data Source window is displayed.

   

8. Select Tools > MS Query.

   The Choose Data Source window is displayed.

   

9. Select the Synergetic database you plan to use. That is:
   • SynergyOne for mostly non-financial data
   • SynergyOneFinance for finance data.
10. Clear the Use the Query Wizard to create/edit queries field.

    

11. Click .

    The SQL Server Login window is displayed.

    

12. Type in your username and password.

    Note: This is your usual Synergetic username and password.

13. Click .

    The Add Tables window is displayed.

    

14. Click .
15. Click .

    The SQL window is displayed.

16. Type the stored procedure and its parameters. See Stored procedures for details of each procedure. In this example, type:
    • exec
    • the procedure name, crspFutureActivityPrint
    • the first parameter, @Activity='<code>', where <code> is the activity code required
    • the second parameter, @Campus='<campus>', where <campus> is the campus required.

    

    Note: In this example we have omitted the campus parameter.

17. Click .

    The following dialog window may be displayed.

    

18. Click .

    The Microsoft Query window is displayed with the data returned by the stored procedure.

    

19. Select File > Return Data to Microsoft Word.

    The Mail Merge Recipients window is displayed.

    

    Note: Ensure that you return this data as soon as possible, as the data from Microsoft Query is not available for longer than 15 minutes.

20. Click .

    Step 4 of the Mail Merge tab is displayed on the document window.

    

21. Click More Items.

    The Insert Merge Field window is displayed.

    

22. Locate and select the field to insert into the document.
23. Click .

    The field is inserted in the document.

24. Repeat steps 22 and 23 until all of the required fields are added to the document.
25. Close the Insert Merge Field window.
26. Add other text and format the letter.

    

27. Click to view the merged data.

    

28. Format any database fields, such as date fields with the time included.

    Tip: Right click and select Toggle Field Codes, to format the date, such as \@ "d/mm/yyyy".
    
    

29. Preview the output and merge the letters.
30. Save the document.

    This document can form the basis of other similar letters based on future student activities.

Last modified: 7/11/2011 1:25:16 PM

© 2013 Synergetic Management Systems. Published 15 October 2013.