Mail Merge Excel



Mail merge displays 'next record'.I am merging an excel spreadsheet to word to print labels. I have been trying to merge an excel spreadsheet to word in order to print labels. I have had no success and can't figure out why it is doing this. Learn how to perform a mail merge from Microsoft Excel to Microsoft Word in order to produce mass mailings to a group of people. If you ever need to send for.

You can use Word's mail merge to insert content from a spreadsheet, table or database into your document. When you insert a percentage into Word from Excel during a mail merge, the number's format changes. The percent symbol is removed and extra zeros are inserted. Thankfully, you can retain your value's percentage during a mail merge, so your document appears the way you want. You can access the mail merge field codes and add switches to them during the mail merge setup.

Step 1

Open a new document in Word and select 'Tools,' 'Letters and Mailings' and 'Mail Merge' from the toolbar. A Mail Merge task pane will open on the right.

Step 2

Pick the type of document you want to create and select 'Next' from the bottom of the task pane.

Step 3

Choose to 'Use the current document' as a starting document and select 'Next.'

Step 4

Choose to 'Use an existing list' as your selected recipients, then click on 'Browse' and find the Excel file you want to use.

Step 5

Choose the correct worksheet from the 'Select Table' dialog box and click 'OK.' The contents of the worksheet will be displayed in the 'Mail Merge Recipients' dialog box. Click 'OK' to accept them and select 'Next' from the task pane.

Step 6

Click on 'More items...' and insert the fields you want to add, then click 'Close.'

Step 7

Go to your Word document and arrange the fields on your page so they are positioned the way you want.

Step 8

Find the field that includes the percentages from Excel. Press 'Alt + F9' on your keyboard to reveal the merge field codes. Alternatively, you can select 'Tools' and 'Options' from the toolbar, choose the 'View' tab, add a check mark to 'Field Codes,' and click 'OK.'

Step 9

Place your cursor at the end of the field name, just before the ending brace where the percentages will be displayed. Add '# .00%' there. Your code should resemble:

{ MERGEFIELD 'Percentage' # .00% }

Step 10

Press 'Alt + F9' again to hide the merge field codes. Return to the 'Mail Merge' task pane and select 'Next' to preview your merged data.

Step 11

Use the left and right arrows (in the top of the task pane) to view the data. Your percentages from Excel will be retained.

Select 'Next: Complete the merge' and then 'Print' or 'Edit individual letters...' to complete the merge.

References

Writer Bio

Foye Robinson is a freelance writer and Web designer with Precision Web Crafting. She shares her love for family/relationships, fitness and Web design in her writing. Robinson holds a Bachelor of Science degree from Webster University and also writes miscellaneous articles and novels.

In this article, we are going to learn how to automate the mail merge by using the VBA in Microsoft Excel.

Mail Merge: - This is the source to merge the data’s information into text and then print the document. To perform such operation, we use Microsoft Word.

Let’s understand with a simple exercise:-

We have a letter format in Report sheet, and we want to apply mail merge through VBA in which we want to change the letter details as well.

We have 2 sheets. 1 sheet contains data with details to whom we want to give letters. In first data, column A contains Name, column B contains street address, column C contains city, column D region, and column E and column F contain postal zip. There is one command button to move in the report sheet.

2nd sheet is having the letter format with 2 command buttons; one button to move on the data sheet and second command button is to work for mail merge

Firstly, we will write the VBA code for command button of Main Data. We need to follow below given steps:-

  • First we will insert command button in the worksheet.
  • Go to Developer tab and then insert Command button from Activexcontrol.
  • Rename the Command button with the name “Letter” , and now assign below mentioned macro:-

Private Sub Main_data_Click()

Worksheets('Report').Activate

Range('A19').Show

End Sub

Now, we will insert the second command button in the Report sheet and assign the macro to move on the first sheet. We need to follow below given steps:-

  • Rename the Command button with the name “Data” , and assign below mentioned macro:-
Excel

Private Sub CommandButton2_Click()

Worksheets('Main_Data').Activate

Range('A1').Show

End Sub

Now we will write the main code for mail merge by following below given steps:-

Insert the command button and rename it as “Letter Print”, and then assign the below mentioned code:-

Private Sub CommandButton1_Click()

Dim StartrowAs Integer, lastrow As Integer

Dim MsgAs String

Dim TotalrecordsAs String

Dim name As String, Street_AddressAs String, city As String, region As String, country As String, postal As String

Totalrecords = '=counta(Main_Data!A:A)'

Range('L1') = Totalrecords

Dim mydate As Date

Set WRP = Sheets('Report')

mydate = Date
WRP.Range('A9') = mydate

WRP.Range('A9').NumberFormat = '[$-F800]dddd,mmmm,dd,yyyy'

WRP.Range('A9').HorizontalAlignment = xlLeft

Startrow = InputBox('Enter the first record to print.')

lastrow = InputBox('Enter the last record to print.')

If Startrow>lastrow Then

Msg = 'ERROR' &vbCrLf& 'Starting row must be less than last row'

Msgbox Msg, vbCritical, 'ExcelTip'

End If

For i = Startrow To lastrow

name = Sheets('Main_data').Cells(i, 1)

Street_Address = Sheets('Main_data').Cells(i, 2)

city = Sheets('Main_data').Cells(i, 3)

region = Sheets('Main_data').Cells(i, 4)

country = Sheets('Main_data').Cells(i, 5)

postal = Sheets('Main_data').Cells(i, 6)

Sheets('Report').Range('A7') = name &vbCrLf&Street_Address&vbCrLf& city & region & country &vbCrLf& postal

Sheets('Report').Range('A11') = 'Dear' & ' ' & name & ','

CheckBox1 = True

If CheckBox1 Then

Mail Merge Excel To Pdf

ActiveSheet.PrintPreview

Else

ActiveSheet.PrintOut

End If

Mail Merge Excel 2019

Next i

End Sub

Code Explanation: - First, we will define the variables then we will define the date and date format, then we will define the last row and start row. Then we have created message box for transmitting the message. Then we will define the data and range that we want to capture in letter.

  • To run the code, press key F5 on the keyboard.
  • Then you have to enter first record point. After that, you will get new message box to enter the last record of point.
  • And, then you will get the below shown document

Mail Merge Excel To Labels

  • Letter will get updated according to the mentioned details in main data.

Mail Merge Excel 365

This is the way we can automate mail merge through VBA in Microsoft Excel.





Comments are closed.