Word, Outlook, and Excel Team Up to Email You
Updated: Feb 26, 2022
Not long after I began this website, I mentioned my dissatisfaction with the website's built-in notification method to advise of new posts. I learned it was tracking who opened the notification message, who visited links on the site, etc. I hate being tracked online and I certainly did not want you to be tracked. I've been experimenting and testing and have found a way to use Excel and Word together to create and send individual notifications to you directly from my website's email account.
Here's a quick summary of the process.
I created a master Word document file and connected it to a master Excel file.
The Word file is the email document I send once or twice a month to indicate new blog postings.
The master Excel file has two columns - Name and EmaiI. The Name column contains the text "GSRLC Subscriber" and the Email column contains subscriber email addresses.
For each notification message, I download subscriber email addresses from the website into a separate Excel Comma Separated Variable (CSV) file. I replace the email addresses in the master Excel file with the newly downloaded email addresses, using a copy/paste operation.
I have multiple email accounts in Outlook on my PC. I have to temporarily change the default data file in Outlook to the file associated with my GSRLC email account. This sends my messages from that account, rather than another account you, as subscribers, will not recognize. If you are using Outlook to send individual email messages, you should first be sure of the default data file you wish to use. This data file will be used for the sending email address in your email merge.
Word is supposed to use Excel files, Word documents containing tables (table must be the very first item in the Word document), Access databases, Outlook contact lists, and probably other types of documents.
I created a Word document containing a table and saved it on OneDrive. Word refused to open the file, saying it was in use by another program. My Excel data file is also saved on OneDrive and it opened with no issues. I moved the Word file to the Desktop folder on my computer and everything worked as expected. This has to be a bug of some sort in Word.
In Step 4 below, choosing the data file, another of Microsoft's "We know best" practices emerges. Microsoft automatically opens the Documents\My Data Sources folder (I wonder how many users know this folder exists) by default. This caused extra navigation steps to my file's location. As a test, I tried to change my data source, to see if Windows would remember the last file location I used - its usual habit. Again, Windows opened the Documents\My Data Sources folder.
Here's how I made all this work.
1. I opened a new document in Word, clicked The Mailings ribbon, then Start Mail Merge, then Step by Step Mail Merge Wizard. This opens a window on the right side of the screen.
2. Inside the first window, at the top, I selected Email Message as the document type. There are several other options available, depending on the type of merge you need. I then clicked Next: Starting document at the bottom.
3. On the Starting Document window, I chose the current document, the new document I just created. I clicked Next: Select recipients at the bottom.
4. I chose use an existing list. In the middle of the window, I clicked Browse and navigated to the master Excel file.
6. The small window below opened and I clicked the OK button.
7. Next, the window below opened. You can see the name of the file in the Data Source column, and the Name/Email columns. The remaining columns are of no consequence because they contain no usable data. I clicked the OK button, then clicked Next: Write your email at the bottom of the right-hand window.
8. I typed Dear and a space in the upper left corner. In the middle of the window on the right, I clicked More Items and chose Name from the small window that opened. That caused <<Name>> to appear after the word Dear. Please note you cannot manually type <<Name> in the message and make it connect to the Name column in the Excel master file. You must use the links in the middle of the right-hand window.
I did not continue typing the email message at this point, because I was not yet sure what it would contain. Instead, I clicked Next: Preview your email message at the bottom of the right-hand window.
9. Using the links at the bottom of the right-hand window, the next step is to preview the email. At the top of the right-hand window, there are buttons you can click to view the contents of your email message. as you move forward or backward through the list, the fields you have entered into the body of your message will change, to reflect the current recipient's information.
7. The next step is to complete the merge. In my case, the only option was email message, since that is the document type I chose in the beginning.