Dynamics GP Mail Merge Macros By Carol Kossoris

Dynamics GP Mail Merge Macros By: Carol Kossoris, ERP Consultant Heartland Business Systems ckossoris@hbs. net

Is a Mail Merge macro the right fit for your GP data update? Cost Benefit analysis: How many manhours to update the data manually, versus how many manhours to create a mail merge macro? 1 hour for simple macro: one screen 2 -3 for complicated macro: List type screens Third party screens Talking point-Examples where a mail merge macro makes sense. ERRORS: Multiple screen updates in one macro Any screen update with popups Keep it simple to avoid macro errors

Step 1: Prepare a List Use Smartlist to prepare a list of data that will represent the screen you will be updating. One line of data for each screen to be updated. List must include the necessary data to populate the screen for lookup purposes.

Step 2: Export your list to Excel to add updated data Prepare data to use for updating GP. Numbers should be the same decimal precision as in GP. Use =ROUND. Vendor ID Vendor Item Description Vendor Item Number Last Originating New Cost-2% Round Cost increase ADVANCED 0001 Green Phone 100 XLG 28. 46000 29. 0292 ADVANCED 0001 Generic 1 Ghz processor 1 GPROC 120. 00000 122. 4 ADVANCED 0001 Generic 2 Ghz processor 2 GPROC 250. 00000 255 ADVANCED 0001 Phone Cord - 12' White CRD-12 WH ACCS-CRD-12 WH 3. 29000 3. 3558 29. 03 122. 400 255. 00 3. 369

Step 3: Save as CSV file When your data is completed in Excel, save the file as a CSV file to retain the decimal precision needed. This is the spreadsheet we will be using in Word for the macro. Save it in a place you can remember and browse to. Vendor ID Vendor Item Description ADVANCED 0001 Green Phone Generic 1 Ghz ADVANCED 0001 processor Generic 2 Ghz ADVANCED 0001 processor Vendor Item Number 100 XLG 1 GPROC 2 GPROC ADVANCED 0001 Phone Cord - 12' White CRD-12 WH Last Originating New Cost-2% Round Cost increase 28. 46 29. 0292 29. 03 1 GPROC 120 122. 4 2 GPROC 250 255 ACCS-CRD-12 WH 3. 29 3. 3558 3. 36

Step 4: Record a macro for a one item update Open a blank screen Position cursor Open Excel with Value Data Tools-Macro-Record Use your keyboard to move and type-do not use copy/paste from excel for this recording • # DEXVERSION=18. 0010. 000 2 2 • Check. Active. Win dictionary 'default' form 'IV_Item_Vendors' window 'IV_Item_Vendors' • Type. To field 'Item Number' , '100 XLG' • Move. To field '(L) Location GB' item 0 # 'All' • Click. Hit field '(L) Location GB' item 1 # 'Assigned' • Move. To field 'Vendor ID' • Type. To field 'Vendor ID' , 'ADVANCED 0001' • Move. To field 'Vendor Item Number' • Move. To field 'Last Originating Cost' • Type. To field 'Last Originating Cost' , '2903' • Command. Exec dictionary 'default' form 'IV_Item_Vendors' command 'Save Button_w_IV_Item_Vendors_f_IV_Item_Vendors'

Step 5: Open a new Word Doc Copy all but first 2 lines of the macro into the Word Document.

Step 6: Insert Mail Merge Make sure your CSV spreadsheet is closed. Mailings-Start Mail Merge-Step by Step Mail Merge Wizard Next: Starting Document Next: Select recipients Browse to Excel Spreadsheet with values Click OK-data should appear Click OK again Next: Write your letter Select a piece of data in Word that will be replaced by the Excel data. More data? (repeat as needed)

Step 7: Complete the Mail Merge in Word Next: Preview your letters Next: Complete the merge Edit individual letters OK to merge All lines A new Word Doc will open with a page for each value to be updated.

Step 8: Almost done Add back in first two lines of macro Save as Plain Text-accept the defaults and close Rename the file with the extension. mac

Step 9: Test your macro Refresh your TEST company if you have one, or make a backup of your LIVE company if you do not Open up the screen you want to update Tools-Macro-Play Select the macro you just created Completed message means success! Review your data for integrity

Step 10: Troubleshooting your macro Pay close attention to the data in the screen the error occurs. It will clue you in on the error. Use the Find function in the macro to remove or correct bad records.