Mail Merge Using Excel

  1. Mail Merge Using Excel Spreadsheet
  2. How To Mail Merge Using Excel
  3. Mail Merge Using Excel Data

Topics Map > Office 365 > Word

In your Excel data source that you'll use for a mailing list in a Word mail merge, make sure you format columns of numeric data correctly. Format a column with numbers, for example, to match a specific category such as currency. If you choose percentage as a category, be aware that the percentage format will multiply the cell value by 100.

Merge

The merge fields from both Excel files are now available under the Insert Merge Field button on your mail merge toolbar. To insert the merge fields in your document, click the Insert Merge. This video shows you how you can use your Excel information to mail merge information into your Microsoft PowerPoint presentation. One slide for each name in.

Personalize names, addresses, and much more in bulk email or documents. Any data stored in an Excel workbook can be used to personalize communications.


Note: These steps only cover what's shown in this video. For detailed steps on how to do a mail merge, please see Mail merge using an Excel spreadsheet.

Use an Excel spreadsheet for mail merge contacts

  1. Open the Word document that you want to mail merge.

  2. Select Mailings > Select Recipients.

  3. Select Use an Existing List.

  4. Select the Excel spreadheet you want to use, and select Open.

  5. Select the worksheet that you want to use, and select OK.

    Each worksheet name ends with $.

  6. Select Edit Recipient List to view the data source, select the checkboxes for the people you want to send to, and select OK.

  7. Save your Word file, to save the connections you've made.

Want more?

If you have any questions, come by the Help Desk at Hardman & Jacobs Undergraduate Learning Center Room 105, call 646-1840, or email us at [email protected]

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

Mail Merge Using Excel Spreadsheet

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

How To Mail Merge Using 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)

Outlook mail merge using excel

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 & ','

Mail merge using excel 2016

CheckBox1 = True

Mail Merge Using Excel Data

If CheckBox1 Then

ActiveSheet.PrintPreview

Else

ActiveSheet.PrintOut

End If

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
  • Letter will get updated according to the mentioned details in main data.

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