Mail Merge Macro Overview in Dynamics GP

There are many ways to get data into Microsoft Dynamics GP. Integration Manager has been around for a long time, and many clients have used that for years. SmartConnect is my preference, it has a lot of additional functionality not found in Integration Manager, allows for scheduling, pulling from a wider range of data sources, and even integrating out to Web Applications and CRM.

Sometimes, however, we come across situations where you want to integrate to a module without a node or work directly through the GP interface without building a full integration. In these situations, a Mail-Merge Macro can be a perfect fit. The Macro functionality in GP is typically used to run a task and can be assigned a shortcut in the Menu Bar. I’ve seen clients open a window and change a document type to the default they want and use that macro as a shortcut to open the window. You can expand the power of macros by combining them with the Mail-Merge functionality in Word.

In doing this, you can make bulk data changes to any window in GP, through the GP interface. You’ll quite literally see GP making these changes through the UI while you sit back without touching a key.

To get started, you first need to record a macro file, and perform a series of steps. Below, I’ll record a macro to update the Order Point, Order-up-to Level, Minimum Order and Maximum Order in the Item Resource Planning screen.

One of the key things to remember when recording a Macro that will be used with Mail-Merge later is to ensure a clean loop. The goal is to record the macro once, and repeat the steps multiple times, replacing the values with those in our spreadsheet.

I like to do this by clicking the “Clear” button before starting, and again at the end of my recorded macro.

The window above shows a clear screen, ready to record.

Select Tools>Macro>Record to begin recording your macro file. This file will record all the steps that you perform until you stop recording.

GP will first prompt for a location to save the macro file. These files will be in the .mac file format, though we’ll be working with them primarily as .txt files.

I prefer to save these files in a single folder (organized into subfolders as necessary). This helps you to find them later, and even re-use them in the future if needed.

I also like to avoid spaces and include the suffix “_Capture” to separate this initial macro recording from the Mail-Merged version we’ll play back later.

Above, you can see that this is in my Documents folder, in a subfolder named Macros. I’m on a single local machine, but in a network environment it would be a good idea to save this in a persistent network folder that is backed periodically.

To record your macro, step through the various fields that you’d like to update.

Using scroll buttons or lookups can be problematic, so it’s easier to type-in the values.

You also need to ensure you populate the key values that identify your records. In the Item Resource Planning screen, I entered the Item Number, then selected the Site ID radio button, and typed in the WAREHOUSE value into the site field.

This opened my existing record. I followed up by entering a value into the Order Point Qty, Order-Up-To Level, Minimum and Maximum fields.

Finally, I saved the record, hit the Clear button, and cleared the item number field by hitting Backspace on my keyboard. This left the screen in the initial clear state where I started the macro.

Now we can stop the macro by going to Tools>Macro>Stop Record on the Action Pane.

In Window Explorer, select Change Folder and Search Options to ensure that extensions are shown for known file types.

This will ensure that we can rename the files from .mac files to .txt files, and back to a .mac file after we’re done processing the file in Word.

The way to get to this window has changed over the years through different versions of Windows. In Windows 10, it is available from the View menu in Windows Explorer. In earlier versions of windows, you may need to go to the Control Panel, then to Folder Options.

In all versions, the Folder Options window has a checkbox to “Hide extensions for known file types” that needs to be UNCHECKED to display the extensions.

Rename the .mac file recorded to replace the extension with .txt

This will allow us to open the file in Word.

Right-click the file and choose Open With, then Word.

The macro file describes all the different steps that were performed. First # DEXVERSION=…. Specifies the version of GP used.

CheckActiveWin… specifies the window that is active for this portion of the macro.

The TypeTo and MoveTo commands navigate the form and enter values. Here, you can see our first value is the item 1-A3261A. Next we move to the Display Option field, and select Open 1, Site ID. This changes our Radio button to Site ID.

We then type in WAREHOUSE for our site, and populate the Order Point Qty, Order-Up-To Level, Minimum Order Qty, and Maximum Order Qty fields.

Then we save and clear the form, preparing for the next record.

On the Mailing tab, select Start Mail Merge, and then Step-by-Step Mail Merge Wizard…

This will open the Mail Merge Wizard on the far right-hand side of the screen (to the right of the document).

On the bottom of the wizard, select Next: Starting Document

On the next screen, select Use the Current document, and then select Next: Select recipients to choose the records to merge.

Select Browse on the next screen to select the Spreadsheet where the data is stored. You must ensure that the spreadsheet is closed before merging.

A window will be displayed allowing you to select the proper Worksheet from your spreadsheet, if more than one is available. Select the proper sheet and click OK.

Another window will open to confirm the column names and display a sample of the data in your spreadsheet. Click OK to continue.

Select Next: Write your letter at the bottom of the wizard.

Highlight the first item to be replaced (leaving the apostophe’s in place) and select the More Items… button on the wizard.

A window will open to allow you to select a field from your spreadsheet. Insert that field then click close to return to the document.

It is important that the apostrophe (single quote) is left in place around this field. Typing this character will result in a slanted apostrophe that will not function properly. Only the vertical single quote field will work.

Replace all the fields in the macro file with the appropriate fields in your spreadsheet and select Next: Preview your letters in the wizard.

The record selectors on the top of the wizard allows you to scroll through various records from the spreadsheet and see the results in the document.

If these values look correct (ensure the single quote characters surround each value, select Next: Complete the Merge in the wizard.

The next step on the wizard will allow you to print or edit individual records. We’ll select Edit individual letters to compile all these into one single document.

On that new document, select Save As… to save the file as a new .txt file.

I prefer to add the suffix “_Upload” to the new file, to differentiate between the original capture macro and the new upload macro.

Select the default file conversion options. Just click OK on this window when it opens.

Rename that new .txt file to .mac to allow the file to be run in GP. Open the window in GP, select Tools>Macro>Play Macro, and select your new macro file.

Sit back, and watch GP update all your data automatically. It will loop through the original macro once for each record in your spreadsheet, updating the records accordingly.

Mousing away from the GP window, or opening another program will cause the macro to fail. Generally, the best thing to do at this point is let it run while you continue to work on your laptop or another machine.

Once you’ve mastered this, you can start to get creative. Add a column in your spreadsheet that contains macro lines, rather than data. You can dynamically update fields or run processes based on the data and formulas in Excel.

Taking it another step further, you can create SQL select statements that include macro commands, and dynamically build a Macro based on complex T-SQL statements. We do this frequently to migrate users from one GP instance to another.

Remember, with great power comes great responsibility. Run these macros in your test company first, and make sure you know what you’re updating before you proceed. While you can easily make wonderful changes and updates quickly, you can ruin your GP data just as fast. Testing is important, and it never hurts to have a current backup just in case. If you have any questions about Dynamics GP, please get in touch!