A common desire it to easily send monthly account statements to customers that have a balance outstanding at the beginning of the month.

This How-To covers how to set up Control to manually send Account Statements to all companies that have orders in Sale and have a balance outstanding on them.

The basic approach used is:

• Create a Saved Query to pull up all of the companies that have balances outstanding on orders in Sale.
• Create a Macro to send this group an account statement.

• This macro uses the Company Billing contact's email.
• This macro does will only send statements to companies that have balances outstanding on orders that are marked sale. It will not send it to companies that have balances outstanding but their orders are in WIP.
• The concepts shown below can be adapted to email or print invoices that are past due. For example, you will see that this uses the “Quick Email' feature of the macro report setup but, if changed to “Quick Report”, it would generate printed reports on the printer of choice which could be tied to a query to find orders, say, 40 days past due. Tied to an Activities Calendar macro with methods described in another “How To” in this section, the creation of past dues invoices can be triggered by the “number of days past” and automatically printed for mailing (used when Billing Contact email addresses are not available or email is not the preferred contact method.). If you have access to Crystal Reports, you can use a custom Past Due Invoice format for the report generated.

Create an advanced query to pull all companies that have a balance outstanding on orders in Sale.

1. Open Company Explorer
2. Click on the “Advanced” button to switch to the Advanced Query setup.
3. Click on the “Modify Criteria…” button.
4. In the left panel, expand Company Criteria and click on the “General Criteria” option. The center panel will fill with different options.
5. In the center panel, double click the criteria “Company is ACTIVE”.
6. Back on the left panel, expand Order Criteria | Sales Amounts and click on the “Balance Outstanding” option. The center panel will fill with different comparison options.
7. In the center panel, double click “Greater than…” and set the number to 0.00, then click on OK.
8. Back on the left, click on “Status”, and the center panel will fill with the different order statuses.
9. Double click on “Sale” in the center panel.
10. In the bottom right panel, change the “Show:” range to All.
11. Click OK.
12. Check that the query results look correct.
13. In the left panel, click on the “Save…” button. Enter “Balances Outstanding” as the query name.

Create a Macro Using this query that Emails an Account Statement

1. Click on “Marketing Home” on the main Toolbar.
2. Click on “Macro Setup”. This will open the Macro Setup Window.
3. Click on “Company Macros” in the left panel.
4. Click on “New Macro” at the top of the left panel.
5. Enter “Monthly Account Statement” as the Macro Name on the Description Tab.
6. Click on the Trigger Tab.
7. We want to run this manually, so click on “Run this macro manually or on a schedule”.
8. Choose to “Use a saved query” under this otpion.
9. Select the query we saved above, “Balances Outstanding” from the list.
10. Click on the Actions Tab
11. The macro tab automatically starts with two pop-up message prompts. We will use before we run and one after.
12. Click in the “Message Text” box under the “Prompt to Begin” action tab. Type a message such as:

1. Check the box “Allow user to cancel macro execution with this message” so the user will have an OK & Cancel buttons and not just an OK button.
2. Click on the Notify on Completion macro action tab. Type a message such as:

1. Add an action to send the emails. In the center panel is a list of macro actions. Click on “Report Macro (Crystal)” and click the “Add” button above it. This will add a new action on the right. Note: Even though we want to send an email, what we are sending is the report so we will use a Report Macro in this case.
2. First, let's move this action before the completion message. Click the “Move Up” button in the middle column once to move this action up in the sequence. You will notice that the order of the tabs has now changed.
3. Fill in these fields in the Report Macro action sub-tabs:
   • General Sub-Tab Options
   • Menu Item Name –> Account Statement (required field, but any name will do)
   • Print Option –> select Quick Email. (necessary so it just auto-sends the invoice without prompting or previewing first)
   • File Location –> Choose System Report and select the standard invoice template you use from the drop-down (Accounting_AllAccountStatement01 for example), or if you use a Custom Statement choose Custom Report and select the statement you use from the browse dialog.
   • Options Sub-Tab Options
   • Click on the “Show Detail” button under Email
     • Click in the To: box
     • Click on the “Merge Fields” button and choose Company | Accounting Contact Email. You will notice that Control inserted the code in the To: field. This will be replaced with the email address of the order's contact.
     • Click in the Subject Line and enter a subject. You can use merge fields in this as well.
   • Click in the Message box and enter a meaningful message. Again, you can use mail-merge fields by clicking on the “Merge Fields” button above. For a larger editing area, click on the “Show Exanded” button. Close the expanded message area by clicking on the “X” at the top right of that pop-up.
   • Set the other statement options corresponding to your preferences. (Leave the Customer Field as “all”)
4. Save the Macro by clicking “Save” on the action toolbar.

To run the macro at any point in time,

1. Click on Marketing Home on the action toolbar.
2. Expand Existing Macros | Company Macros.
3. Double-click on “Monthly Account Statement”.
4. Click “Yes” when prompted to run this macro.

You may schedule this macro to run at a particular time using the following steps.

1. Click on “Activity Manager” on the main toolbar to open your calendar.
2. Right-click on the calendar and choose New | Macro Activity.
3. Enter a description that will appear on your activity manager.
4. Select “Monthly Account Statement” as the macro.
5. Select to Automatically Run “only if logged in”. While email macros from the server are possible, you will need to set up the system email information with the default. Until you are successfully using the macro from a manned station, it is not recommended you move it to running in the background on the server.
6. Select a date and time to run.
7. If desired, you can make this a recurring activity. Caution, however, that recurring activities incorrectly created (so they occur with incredibly high frequency) can slow down the entire system.
8. Click OK to save macro activity.

Contributor: Cyrious Software

Date: 7/30/2009

Version: Control 4.0 and higher