Creating overdue letters by mail merge

Owned by Former user (Deleted)
Last updated: Feb 25, 2017 by Former user (Deleted)

If you do not want to use the Debtor Overdue Letters Crystal Report, you can create the letters using an SQL query in Microsoft Excel.

Important Note: Make sure that no one is making changes to any debtor records while you are running this program.

Note: Debtors with active arrangements do not receive overdue letters.

To create overdue letters:

  1. Select overdue accounts to send letters to. See Processing overdue letters.
  2. Select Update Overdue Letter History. This automatically updates each debtor's overdue letter code, total overdue and date. See Debtor Maintenance - Debtor Comms tab.
  3. Print the Debtors Trial Balance as at the letter processing date, from the Crystal Reports menu.
  4. Using Microsoft Excel - create a new Query - in the SQL statement insert the script below.
    Note: You will need to change the letter date to match the date that you processed the overdue letters.
    SELECT DOLH.LetterDate, DOLH.DebtorID, DOLH.DebtorStudentID, DOLH.LetterCode, vDebtors.DebtorMailName,
    vDebtors.DebtorMailSalutation, vDebtors.DebtorAddressFull, DOLH.TotalOverdue
    FROM DebtorOverdueLetterHistory DOLH
    LEFT JOIN vDebtors vDebtors
    ON vDebtors.DebtorID = DOLH.DebtorID AND vDebtors.DebtorStudentID = DOLH.DebtorStudentID
    WHERE DOLH.LetterDate='30 MAR 2007' 
  5. If you are running over multiple sites (using different Debtor Statement Groups to identify Debtors associated with each site) then you will need to add the following statement to the SQL query above (in case the other site(s) have also generated letters on the same day)
    AND (vDebtors.DebtorStatementGroup = 'statementgroup1'
    OR vDebtors.DebtorStatementGroup = 'statementgroup2' ….) 
  6. Return data to Excel - ensure that the option in Data | Data Range Properties to Save Query Definition is selected.
  7. Format money fields as required
  8. Save the spreadsheet (remember name & location)
  9. Open Microsoft Word and create a mail merge document (for letters done in the future you will only need to call up your previously saved letter(s) - see (12) below
  10. Get data - the Excel Spreadsheet saved in (6) above
  11. Insert text and merge fields as required
  12. In Tools > Mail Merge > Query Options - select LetterCode equal to ODUE5 for letter 5 - five periods overdue, or ODUE4 for four periods overdue and so on. (depending upon what letter you are creating). You can use the OR condition to have a combined letter for (say) the overdue 4's and 5's
  13. Save the letter - using an appropriate name to identify the letter. For example, OverdueLetter2, OverdueLetter3 and so on (only do this if you are creating or have made any changes to the letter).
  14. Merge the letters to your printer.