CRM 3 - Investor Mailing Mail Merge

Initial Setup and Navigation for Investor Mailings

  • Accessing the Module:

    • Login to the system and navigate to the top menu bar.

    • Select "My Work" then click on "Investor Mailings."

  • Creating a New Mailing:

    • Click the "New" button and wait for the page to load.

    • Naming: For the purpose of the training, the name was set to "capital."

    • Preparation Options:

      • E-mail attachments: Used for sending a simple document directly to LPs.

      • Portal upload: The standard choice routinely used to upload documents directly into the investor portal.

    • Templates: You can apply existing templates, such as the "cap call T1 account schedule," to streamline the creation process for new mailings.

Defining Data Sources and Contacts

  • Get Sources: This step is used to import the specific list of Limited Partners (LPs).

  • Get Document Number: This is identified as the most efficient method for selecting LPs. It ensures that only the LPs associated with a specific posted document are included.

    • Example: For a capital call, if only certain LPs were allocated the call, the document number captures only those individuals and excludes the rest.

    • Use the "Search documents" function to identify and select the relevant posted transaction.

  • Get Contacts: This option exists to pull contact information for the selected LPs, though it was not the primary focus of this specific training session.

Dynamic Elements and Account Schedules

  • Default Dynamic Elements: The system automatically includes certain fields, such as Investor Name, Fund Name, and any ACRM (Advanced CRM) fields.

  • Account Schedules: For capital calls, data is frequently pulled from an account schedule.

    • Date Range vs. Document Number:

      • The speaker typically uses the "Document Number" because it is already associated with the posted document.

      • If the source is changed to "Date Range," the parameters for the dynamic element will change, requiring a reconfiguration of the range.

  • Understanding Dynamic Element Naming: A dynamic element is identified by its string components. For example, in a name like ABCL + CC 51 Description:

    • ABCL + CC: Represents the Account Schedule name.

    • 51: Represents the specific row number within that account schedule.

    • Description: Describes the content in relation to the capital call (e.g., "Current Capital Call Due").

  • Standish Database Schedules:

    • The cap call schedule used in this demonstration is titled "cap call T1."

    • To find specific fields, search the account schedule by name and row number to avoid scrolling through extensive lists.

    • The Alias: The alias is the most critical part of the dynamic element. It serves as the merge field name when the data is brought into Microsoft Word.

Mail Merge Execution and Word Document Setup

  • Exporting Data: Click "Export to Excel" from the investor mailing interface. This generates the data list (including the aliases as column headers) used to populate the Word template.

  • Step-by-Step Mail Merge in Word:

    • Navigate to Step 33 in the Word Mail Merge wizard.

    • Click "Browse" and select the Excel file downloaded from the system.

  • Editing the Template:

    • Delete the static placeholder text in the Word doc and replace it with the corresponding merge fields.

    • Example replacements:

      • "Investor Name" placeholder $\rightarrow$ investor_name merge field.

      • "Transaction Amount" placeholder $\rightarrow$ current_capital_call (or specific row, such as Row 5151: Current Cap Call Due).

      • "Unfunded" placeholder $\rightarrow$ unfunded_commitment merge field.

    • Field Accuracy: When replacing fields, ensure no extra characters or spaces are caught inside the merge field markers (symbols resembling arrows).

    • Consistency: If you change an alias in the system (e.g., renaming a row to "transaction_amount"), you must re-export the Excel file to ensure the mail merge list recognizes the new field name.

Formatting with Mail Merge Codes (Switches)

  • Decimal Issue: System-generated numbers often contain an excessive amount of decimal points (e.g., more than 22). These must be formatted for the final mailing.

  • Toggling Field Codes: Use Alt + F9 to toggle between the merge field name and the underlying field code.

  • Applying Format Codes:

    • Right-click the merge field and select "Edit Field."

    • Check "Preserve formatting during updates."

    • Click "Field Codes" and paste the specific format code string.

    • Standard Code for Two Decimals: The code typically involves a sequence like \# "#,##0.00".

    • Alternative Manual Entry: You can manually add a space and the code (e.g., \# "#,##0.00") directly within the code brackets after toggling with Alt + F9.

  • Fixing Spacing and Alignment:

    • If a field appears with an unwanted leading space, it may be due to using a # (hash) in the front of the code. Changing the code to start with a 0 (zero) often resolves this alignment glitch.

    • Finalizing Fields: Always right-click and select "Update Field" after making changes to the code.

  • Font Formatting: Merge fields often default to a different font or size than the rest of the template.

    • Use Ctrl + A (Select All) and set the font (e.g., size 11 pt11\text{ pt}) to ensure the entire document is uniform.

Generating and Reviewing Final Documents

  • Conversion Parameters:

    • Select "Convert to PDF."

    • Use "Word Merge" to ensure the system processes the internal mail merge logic.

  • File Naming with Tags:

    • Use dynamic tags to name the output files automatically.

    • Example: [fund_name] Capital Call [date] [investor_name].

    • Warning: Avoid using illegal characters in file names, such as slashes /, as the system will reject them and fail to generate the files.

  • The Generation Process:

    • Click "Generate Files." This initially creates the Word documents.

    • Perform a "Spot Check" on the resulting PDFs to ensure numbers, decimals, and alignments are correct.

    • Bulk Review: Use the "Download Merge PDF" option to view all generated documents in a single file for faster quality assurance.

Questions & Discussion

  • Question from Shannon: Does the order in the Account Schedule match the order in the new Capital Call?

    • Response: Yes and no. They generally match and share roughly the same descriptions, but some consolidation of account schedules may result in slight variations in the exact sequence.

  • Question from Katie: How do you know which fields need decimal points?

    • Response: Review the format code source. Fields requiring decimals are identified by the .00 suffix in the code string, while those requiring commas use the #,## notation.

  • Question from Katie regarding "Switches": Katie asked about adding switches, but later clarified she had no further questions on the topic.