A common desire is to easily send invoices to customers when their orders reach the Sales status. This can be invoked manually or automatically when the status is changed.

This How-To covers how to set up Control to manually send invoices to all new orders in Sale since the last email was sent. For information on automatically sending invoices when the order is made a sale, see how-to_email_invoices_on_orders_in_sale_automatically.

The basic approach used is:

  • Create an Order UDF to track which orders we've already sent invoices to.
  • Create a Saved Query to pull up all of the Sale Orders that haven't been sent invoices.
  • [Optional] Exclude all existing Sale orders by setting in the UDF value.
  • Create a Macro to send this group and invoice and set the UDF.
  • This macro uses the Order contact's email.
  • This macro does not send the invoice for orders that are already closed (fully paid), though it could be easily modified to pull Closed orders also.
  • Make sure before you run this for the first time update the UDF on all the older Sale orders that you don't want to receive an invoice.

Create an Order UDF called “Invoice Emailed” of type Date. This will be used to track who has received the email already.

  1. Click on Sales & Marketing | User Defined Field Setup.
  2. In the left panel, choose Transaction Under “User Defined Fields”
  3. Click on the “Add” button in the middle panel
  4. Enter “Invoice Emailed” as the Field Name.
  5. Select “Date” as the Data Type.
  6. Enter a Description and any category names if desired.
  7. Make sure that “Show For Orders” is checked.
  8. Make sure that “Show For Service Tickets” is checked (if you use service tickets).
  9. Make sure that “Clear When Cloned” is checked.
  10. Save the UDF.

  1. Restart Control so the UDF will be created.

Create an advanced query to pull all orders in Sale where the UDF is not set.

  1. Open Order Explorer
  2. Clear all of the criteria except “Sale” so that only orders in Sale are showing.
  3. Click on the “Advanced” button to switch to the Advanced Query setup.
  4. Click on the “Modify Criteria…” button.
  5. In the left panel, expand Order Criteria | User Defined Fields | All Fields and click on the “Invoice Emailed” UDF. The center panel will fill with date criteria for this UDF.
  6. In the center panel, double click Double the criteria “Not Set”.
  7. In the bottom right panel, change the “Show:” range to All.

  1. Click OK.
  2. Check that the query results look correct.
  3. In the left panel, click on the “Save…” button. Enter “Invoices Not Emailed” as the query name.

[Optional] Set the “Invoice Emailed” UDF for all the existing Sale orders so that these are excluded from the initial emails.

  1. With Advanced Explorer showing all the results, click on the down-arrow by “Run Macro Action” on the action menu.
  2. Select “Run User Field Macro”. A dialog window will appear.
  3. Select “Transcation Fields” as the Category.
  4. Select “Invoice Emailed” as the User Field Name.
  5. Select “Set the field to the value below”
  6. Select “Current Date When Executed” to set the UDF to todays date.
  7. Click Execute to set the UDFs for all the orders selected in Explorer.

Create a Macro Using this query that Emails an Invoice

  1. Click on “Marketing Home” on the main Toolbar.
  2. Click on “Macro Setup”. This will open the Macro Setup Window.
  3. Click on “Order Macros” in the left panel.
  4. Click on “New Macro” at the top of the left panel.
  5. Enter “Email New Sale Invoices” 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, “Invoices Not Emailed” 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:

This macro will automatically email invoices for orders that were marked as Sale since the last time it was run.

  1. Check the box “Allow user to cancel macro execution with this message” so they user will have an OK & Cancel buttons and not just an OK button.
  2. Click on the Notify og 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 (SQL)” 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 –> Invoice (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 (Order_Invoice01 for example), or if you use a Custom Invoice choose Custom Report and select the invoice 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 Contact | Email Address. 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 (for example “Invoice - ”)
    • 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 invoice options corresponding to your preferences.
  4. Add an action to update the Order UDFs (so they are excluded next time). In the center panel, click on “User Defined Field Macro” and click the “Add” button above it. This will add a new action on the right.
  5. 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.
  6. Select “Transcation Fields” as the Category.
  7. Select “Invoice Emailed” as the User Field Name.
  8. Select “Set the field to the value below”
  9. Select “Current Date When Executed” to set the UDF to the date which the query is executed.
  10. 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 | Order Macros.
  3. Double-click on “Email New Sale Invoices”.
  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 “Email New Sale Invoices” 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

You could leave a comment if you were logged in.