Contents
Formulation of the problem
We have a table in Microsoft Excel, which stores information about the key clients of our company (e-mail addresses, names, company names, etc.), whom we want to congratulate on the next holiday.
We will congratulate with a simple but touching message containing the name of the client and the name of his company. Moreover, everything must be done in such a way that the recipient (God forbid!) does not think that the letter was made automatically, but is sure that the letter was painstakingly written for him manually.
At the end of all created messages, you need to:
- or send via Outlook to each client to his e-mail
- or print for later sending by regular mail
- or throw in a new file, where each letter will be a separate sheet (section)
In terms of Microsoft Office, this procedure is called merger (Mail Merge). Go…
Preparing a customer list in Excel
A table with a list of customers to send out must satisfy a few simple conditions:
- The table header should be simple – one line with unique column names (no repetitions and empty cells).
- The table must not contain merged cells.
- The table must not have empty rows or columns (single empty cells are allowed).
If you plan to send by e-mail, then, of course, the table should have a column with recipient addresses.
Since Excel and Word will not be able to determine the gender of the client by name, it makes sense to make a separate column with an appeal (mister, madam, etc.) or with a generic ending (-th or -th) for the appeal “Dear(s)…» Alternatively, it is possible to semi-automatically determine the gender depending on the ending of the patronymic (“a” or “h”), implemented by the function RIGHT (RIGHT).
In new versions of Microsoft Excel 2007/2010, it is very convenient to use the tool for such tables. Format as a table(SizeasTable) from the tab Home (Home). You can read more about such “smart tables” here.
Create a message in Word and connect Excel
We open a new empty document in Microsoft Word and type the text of our message there, leaving empty spaces in it for the future insertion of the name of the client and his company.
Then we run the step-by-step Merge Wizard on the tab Newsletters (Mailings) button Start Merge (Start Mail Merge) – Step by step merge wizard (Step-by-Step Merge Wizard):
In Word 2003 and older, this command was available from the menu Service – Letters and Mailings – Merge Wizard (Tools — Letters and Mailings — Mail Merge).
This is followed by a process of 6 stages (switching between them – using the buttons Forward (Next) и Back (Back) in the lower right corner of the task pane).
Stage 1. Selecting the type of document.
At this step, the user must select the type of documents that he wants to receive as an output after the merge. Our option is Letters (Letters), if we want to send the created letters to the printer at the output, or Electronic message (E-mail)if we want to send them by mail.
Stage 2. Document selection
At this step, it is necessary to determine which document will be the basis (blank) for all future messages of the same type. We choose – current document (Current document).
Step 3: Select Recipients
In this step, we are connecting the customer list in Excel to a Word document. Choose List use and click on Review (Browse), after which in the file open dialog box we indicate where our file with a list of clients lies.
After selecting a data source, Word allows you to filter, sort and manually select records using the window Merge Recipients:
Stage 4. Letter creation
At this stage, the user must specify where exactly the data from the connected list should go into the document. To do this, you need to place the cursor at the insertion point in the letter and use the link Other items – it displays a complete set of all list fields, from which we select the desired field to insert:
As a result, after inserting all the merge fields, we should get something similar (the inserted fields are highlighted in red):
If the original table did not have a special column for the ending “th” / “th” to the word “respect” (as in our example), but there was a regular column “Gender” with the classic “husband” and “wives”, then you can use the button Rules (Rules) tab Newsletters (Mailings):
This button opens a list of rules (functions and fields) available for insertion into a document when creating a mailing list. To substitute the desired ending for the word “dear” select the third item IF…THEN…ELSE. In the window that opens, we set the rule for substituting the correct ending depending on gender:
Stage 5. Viewing letters
At this point, we can already preview the results of the merge using the arrow buttons. If necessary, you can also exclude any recipient from the list.
Stage 6. Completing the merge
At the final stage, several options are possible:
Clicking a link print (Print) will cause all merge results to be immediately sent to the printer without being displayed on the screen.
If you need to save the documents created as a result of the merge for further use, or you need to make manual edits to some of the documents, then it is better to use the link Change part of letters (Edit individual letters), which will output the results of the merge to a separate file:
If you are on Step 1 chose the option Electronic messages (E-mail)then on Step 6 you will be prompted to enter a subject for the messages you send and select a table column containing the email addresses to send. Then all created messages will be placed in the folder Outgoing (Outbox) your Outlook email client.
To check the correctness of the created messages, you can temporarily disconnect Outlook from the server in advance so that the letters do not leave immediately and you can look at them and make sure that everything is in order with them. To do this, in the Microsoft Outlook menu, you can select the command File – Offline Mode or even temporarily disable the network connection to your computer. Emails will be temporarily “stuck” in the Outbox folder and you will be able to open and check them before sending.
- Sending an Excel workbook or sheet via email
- Creating emails with the HYPERLINK function
- A tool for mass mailing messages (including with attachments) according to the list from the PLEX add-on