How to Import Data Into Front End GP with Mail Merge

Endeavour Solutions No Comment Jul 28,2014 Microsoft Dynamics GP, Uncategorized

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