Change Microsoft Word document mail merge source location for Sharepoint excel file

Question

Hello,

I understand there is no practical way to perform a mail merge in Microsoft word by linking to a SharePoint Excel file. There is, however a workaround. I have used this workaround before, but have forgotten how to apply it. Please note, I am not asking for a VBA solution. There is a way to access/open the source code for a word document, to be able to change the file location for mail merge data. I cannot recall how to do this, as to whether it is in Word or via another method.

If anyone has insight into how I can change the file location for mail merge Word documents, by somehow opening the source code of the document, can you please remind me how to do this?  Thank you! Again, this is not a VBA solution, but it does enable Word documents to successfully mail merge Sharepoint stored Excel files.

Answer 1

See:

https://social.technet.microsoft.com/Forums/sharepoint/en-US/409baa88-659a-473d-bee3-f878370bc831/mail-merge-with-sharepoint-list-as-datasource

and the response from Joanne Li in:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice\_sharepoint-mso\_winother/word-mail-merge-with-excel-document-in-a/81cd913e-9a57-4bf8-99ca-a766b57b2531

Answer 2

Thank you. Unfortunately this is wrong. I stated that I have done this before and it worked. Fortunately I figured it out. This approach successfully mail merges sharepoint files indefiintely. 

1. Set up a mail merge in the Word document with a local file.
2. Change the Word document extension to .zip from .doc or .docx
3. Extract the zip file into a new folder. 
4. Open the word\settings.xml file or word\settings\settings.xml file in notepad
5. In notepad, change the merge file source location and name information in both (two) places with the url of the sharepoint source file, and save (example: target=‘file:///c/documents/myfile.xlsx’ to target=‘https://sharepoint/folder/filename.xlsx’)
6. Repeat 4,5 for the file rels\setttings.xml.rels
7. Rezip the extracted folder and change the extension back to .doc or .docx OR

7b. replace the files in the zipped folder and change the extension back to .doc or .docx

8. Your word document is now successfully mailmerged with a sharepoint source file. And it works perfectly. 

It’s not ideal. But it’s a work around to Microsoft’s decade of unwillingness to address productivity and collaboratoin needs with its platforms.