How to Import Data Into Front End GP with Mail Merge
Mail Merge Macro with Non-Scrolling Detail Window
(Mail Merge with scrolling detail is seen on the second part of this blog)
- Begin with Template (Excel Document)
- See example file: Step 1 PriceList_DetailMaintenance.xlsx
- Record one iteration of the Steps to create a record within the applicable module
- See example file: Step 2 Item Price Detail Maintenance.mac
Hint: Do a Save as on this file and call it something indicating it is the Final version.
3. Copy and Paste the Macro body into a word document
See example file: Step 3 MAC_Word Document for Merge.docx
When pasting in the data this message will pop…click on No
4. The word document will open with the recorded Macro data
5. Click on Mailings
6. Select Start Mail Merge
7. Select Letters
8. Click on Select Recipients
9. Browse to the location on your network where you stored the file and select
10. Throughout the recorded macro you will see the points where input was required this is evident by the words “Type To”
11. Highlight the typed text i.e 841200 being careful to not highlight the single quotes around the text
12. In the header click on Insert Merge Field
13. Select the column name in the excel document that relates to this set of data e.g. Item Number
14. Once you have selected to insert all the relevant column data from the spreadsheet, it is best to preview the result.
15. You should expect to see the appropriate column name replace the actual typed information originally typed.
16. Select Finish and Merge and Edit Individual Letters
17. Select to Merge ALL when the option box appears
18. Click OK
19. MS Word will run the Merge Routine
20. Once complete Save As <xxxxx.mac> and Select the format as .txt
21. Select OK at the file conversion to text window
22. Allow time for the conversion and then close the merged document and you will be prompted to save the original Word Document. Save As <xxxxxWORKING.docx.
23. Open the .txt document and Select All then Copy
24. Paste the data into the Document you created in step 2.
25. Save and Close the final version and you are ready to launch the window in Dynamics GP that you wish to populate
26. Ensure that you place the cursor in the window at the exact same place that you began your macro with.
27. Select Tools > Macro > Play
28. Select the Macro to Play that you created in step 2
Hint: Do Not minimize the window…this will interrupt the Macro and it will stop running.
Mail Merge Macro with Scrolling Detail Window
- Begin with Template (Excel Document)
- See example file: Step 1 Price List Maintenance.xlsx
- Record a complete iteration of the Steps to create as many lines that will fit into the detail window and stop recording at one line past the scroll. Ensure that your Recording captures up to the point of completing the line and advancing to the next line to continue typing.
- See example file: Step 2 Price Sheet Maintenance.mac
Hint: Do a Save as on this file and call it something indicating it is the Final version.
3. Open the Recorded Macro using notepad.
4. Take note that there are fields indicating the line No. Also note that after the 8th record (in my example) the 9th line actually still says 8.
Note: In a scrolling window Dynamics GP does not recognize the subsequent lines once the window advances beyond the scroll.
5. Ensure that you add a column into the spreadsheet to accommodate populating the line sequences. Once you have 8 then repeat 8 until the end of the document.
6. Copy and Paste the Macro body into a word document See example file: Step 3 MAC_Word Document for Merge.docx When pasting in the data this message will pop…click on No
7. The word document will open with the recorded Macro data
8. Click on Mailings
9. Select Start Mail Merge
10. Select Letters
11. Click on Select Recipients
12. Browse to the location on your network where you stored the file and select
13. Throughout the recorded macro you will see the points where input was required this is evident by the words “Type To”
14. Highlight the typed text i.e 841200 being careful to not highlight the single quotes around the text
15. In the header click on Insert Merge Field
16. Select the column name in the excel document that relates to this set of data e.g. Item Number
17. Once you have selected to insert all the relevant column data from the spreadsheet, it is best to preview the result.
18. You should expect to see the appropriate column name replace the actual typed information originally typed.
19. Select Finish and Merge and Edit Individual Letters
20. Select to Merge ALL when the option box appears
21. Click OK
22. MS Word will run the Merge Routine
23. Once complete Save As <xxxxx.mac> and Select the format as .txt
24. Select OK at the file conversion to text window
25. Allow time for the conversion and then close the merged document and you will be prompted to save the original Word Document. Save As <xxxxxWORKING.docx.
26. Open the .txt document and Select All then Copy
27. Paste the data into the Document you created in step 2.
28. Save and Close the final version and you are ready to launch the window in Dynamics GP that you wish to populate
29. Ensure that you place the cursor in the window at the exact same place that you began your macro with.
30. Select Tools > Macro > Play
31. Select the Macro to Play that you created in step 2
Hint: Do Not minimize the window…this will interrupt the Macro and it will stop running.
Endeavour Solutions
Latest posts by Endeavour Solutions (see all)
- Why implement an ERP in 2021 with Microsoft Dynamics ERP - January 8, 2021
- Embedding Power BI Dashboards and KPIs with Endeavour Solutions - October 2, 2018
- Performing miracles remotely and other remote Microsoft Dynamics GP consulting - November 1, 2016