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)

  1. Begin with Template (Excel Document)
  • See example file: Step 1 PriceList_DetailMaintenance.xlsx
  1. Record one iteration of the Steps to create a record within the applicable module
  • See example file: Step 2 Item Price Detail Maintenance.mac

 

KARI1Hint: 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

kari2

4. The word document will open with the recorded Macro data
5. Click on Mailings
6. Select Start Mail Merge
7. Select Letters

kari3

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”

kari4

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.

 

kari5

16. Select Finish and Merge and Edit Individual Letters

Kari6

 

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

kari722. 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

kariplaygraphic

28. Select the Macro to Play that you created in step 2

lastimage

Hint: Do Not minimize the window…this will interrupt the Macro and it will stop running.

 

Mail Merge Macro with Scrolling Detail Window

  1. Begin with Template (Excel Document)
  • See example file: Step 1 Price List Maintenance.xlsx
  1. 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  

 

kari21

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.

 

kari22

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

kari23

7. The word document will open with the recorded Macro data
8. Click on Mailings
9. Select Start Mail Merge
10. Select Letters

kari24

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”

kari25

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.

 

kari26
19. Select Finish and Merge and Edit Individual Letters

kari27

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

kari29

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

kari30

31. Select the Macro to Play that you created in step 2

kari31

Hint: Do Not minimize the window…this will interrupt the Macro and it will stop running.

 

The following two tabs change content below.
Related Posts
Did You Know You Can Create a More User Friendly Sales Inquiry Screen?
Grow Your Business with Microsoft Dynamics Marketing
GP Security – Exploring more with SQL Server
TOP 8 REASONS TO UPGRADE TO DYNAMICS GP 2013

Leave Your Comment

Your Comment*

Your Name*
Your Webpage