Mail Merge Using SharePoint List as Data Source


I have found several articles on the web that made mail merge using a SharePoint 2010 list as the data source possible which has spawned this post. The instructions I found online were great, up to a certain point, however. Most of the instructions assumed the users had some basic understanding and thus many steps were omitted which reduces the target audience that can use their instructions considerably. These instructions are intended for folks who have not worked with the Visual Basic Macro editor and don’t have a lot of interest in learning how to do it. They just want to get their work done.

Setup Info

I have created the following test documents and test SharePoint lists for these instructions. You will need to do the same for your project.

Files/Data Used

  • Create Data Source list in SharePoint 2010
  • Open Notepad and save an empty file as TestMergeSource.odc to the following location
    • C:\Program Files (x86)\Microsoft Office\Office14\QUERIES
  • Open Microsoft Word (2007 or 2010)
  • Save Word document as TestMergeDocument.docx to SharePoint 2010 document library location noted above
  • In Word document click View tab
  • Click View Macros (see below)
  • Enter a name in the Macro name: field (no spaces)
    • I named mine Test_Merge_Source
  • Select All active templates and documents in the Macros in: drop down (see below)
  • Click Create button
  • Paste the below code into the Visual Basic Editor replacing bolded sections with your filenames
    • (NOTE: If you have more than one macro, your editor may have multiple Sub sections, this is ok)
Sub Test_Merge_Source()
Test_Merge_Source Macro
‘ Combines files to produce form letters, mailing labels, envelopes, and catalogs
‘ List name
Const strList As String = “Mail Merge Data Source
‘ Site URL
Const strDatabase As String = “http://your.domain.com
‘ Office Database Connection file
Const strODC As String = “C:\Program Files (x86)\Microsoft Office\Office14\QUERIES\TestMergeSource.odc
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.OpenDataSource _
Name:=strODC, _
Connection:= _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & strDatabase & “;” & _
“Mode=Share Deny None;” & _
“Extended Properties=””WSS;HDR=NO;IMEX=2;” & _
“DATABASE=” & strDatabase & “;” & _
“LIST=” & strList & “;””;”, _
sqlstatement:= _
“SELECT * FROM [” & strList & “]”
 
End Sub
 
  • Your completed code will look like the image below with no other Macros present, if you do have others your code will look different
  • Click Save when done
  • Close the Visual Basic Editor screen
  • Back in Word click the Macros button and click View Macros again
  • You should now see the newly created Macro named Test_Merge_Source (or whatever you named yours… see below)
  • Select your Macro and click Run
  • Click the Mailings tab
  • Click the Edit Recipients List button in the Start Mail Merge section
  • Validate the recipients list is accurate (see below)
  • If all is correct, create your document as you normally would and then save the document.
  • You will not need to repeat these steps unless you move to a different computer or remove the code added above

You can also use this macro for other Word Documents that you want to use the same data source. This is why I recommend saving the macro with a name specific to the data source (SharePoint List).

 If these instructions were helpful please leave a comment or if you had issues understanding/following them please leave feedback so I can fix them.
Advertisements

18 thoughts on “Mail Merge Using SharePoint List as Data Source

  1. Pingback: Making the Most of the Person List Field « TechChucker's Brain Blast

  2. Hi, you seem to be missing a step with your ODC file. When I follow your instructions, Word prompts with a Macro problem of “Word cannot open TestMergeSource.odc as a data or header source because it contains no data.” (not that the file isn’t found). I’m using Word 2013 and SharePoint 2013.

      • I have the same issue, did you get a change to test with SP2013 and Office 2013? Had it working in SP2010 / Office 2010 but not with 2013 😦

      • Unfortunately, we’ve abandoned the use of Mail Merge and SharePoint and instead use Workflow. We decided that the formatting controls we got with Mail Merge were just not benefiting us enough to warrant continuing the use of it. Using workflow we have very few issues. Sorry.

  3. Brilliant! Been looking for this for a week. There were some copy and paste issues with the macro text, but once these were sorted it worked a dream. It was a bit unclear about strList value but I worked that out.

  4. Arrrgh .. it runs but comes back with a run time error: 5922 Word was unable to open the data source.

    Using Word2010 & Sharepoint 2010

    Thanks

  5. First, thank you for posting this. It took me a bit of fooling around, but I have it working…just about. Turns out that my “list” is in a document library (Using an infopath form to publish to a document library). Can I somehow alter the Extended Properties to get it to connect to my library instead of a list? (I did test and can connect to a list). Any help would be greatly, and desperately appreciated!

  6. To follow-up, I found a fix for this and it work’s great now. I had to reference the UID to access the document library…for some reason using the name won’t work.

    Here’s the rub…it worked great for about 2 weeks. Now it crashes word as soon as it tries to execute the ActiveDocument.MailMerge.OpenDataSource statement. I am assuming this may be due to a security patch or something that was recently installed on my machine. Has anyone else run into this??

    • I haven’t run into that issue, but I’ve since abandoned using mail merge with SharePoint lists and have gone to strictly using SharePoint workflow. My use case doesn’t require a document record of what I send so I use it to send out formatted communications to our users. It works much better than the Mail Merge ever did, though at the time of this post, the solution worked great for what I was doing.

  7. Code that asks users for site and list dynamically below:

    Sub Merge_From_SharePointList()

    ‘ Combines files to produce form letters, mailing labels, envelopes, and catalogs

    ‘ Site URL
    Dim strDatabase As String
    strDatabase = InputBox(“Please specify the url to the site the list is in”, “Site URL”, “http://yoursitehere”)

    ‘ List name
    Dim strList As String
    strList = InputBox(“Please specify the title of the list you want to merge from (case sensitive!)”, “List Name”, “Contacts”)

    ‘ Office Database Connection file

    Const strODC As String = “C:\temp\TestMergeSource.odc”

    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

    ActiveDocument.MailMerge.OpenDataSource _
    Name:=strODC, _
    Connection:= _
    “Provider=Microsoft.ACE.OLEDB.12.0;” & _
    “Data Source=” & strDatabase & “;” & _
    “Mode=Share Deny None;” & _
    “Extended Properties=””WSS;HDR=NO;IMEX=2;” & _
    “DATABASE=” & strDatabase & “;” & _
    “LIST=” & strList & “;””;”, _
    sqlstatement:= _
    “SELECT * FROM [” & strList & “]”

    MsgBox “Your mail merge data has been retrieved.” & vbCrLf & “Please click ‘Edit Recipient List’ to verify the data is correct.” & vbCrLf & “When you are happy with the data, use the ‘Insert Merge Field’ to edit the document.”, vbOKOnly, “Ready!”

    End Sub

    Extelligent Design – SharePoint Consulting, Canberra Australia

    • The macro starts out fine but then I get a runtime error. Run-time error’9105′: String is longer than 255 characters.

      any way to resolve this?
      I’m using SP2010 and Word 2010

      • My guess is you’d need to see if you can reduce the amount of text in a path or something. String is longer than 255 could be referring to the path you’re using. I’ve moved away from using Word Mail Merge and now use workflow exclusively for sending canned messages. You might want to look into converting to a workflow instead of using this method.

  8. Oh, and you can add the following lines under the
    “Const strODC As String = “C:\temp\TestMergeSource.odc””
    line if you want the script to create the ODC file. this will make the macro simple to implement – no need to worry about the odc file. not every computer has a “c:\temp” directory though – so you may want to change the folder. I deliberatly did not use the “QUERIES” folder since not all users have permissions to create files in it…but usually the temp folder is ok.

    Set fs = CreateObject(“Scripting.FileSystemObject”)
    Set a = fs.CreateTextFile(strODC)
    a.Close

    Extelligent Design – SharePoint consulting in Canberra Australia

    • Hey this is pretty cool. Thanks for sharing. I’ve moved away from using the mail merge with a sharepoint list as I’ve been able to convert all of our business requirements into workflows instead, but great to see a way to not have to hard code some stuff. Thanks.

  9. To make it even better for usability, you can either add a macro button to the ribbon, or (what I am doing) save the macro in a template (.dotm file) and add the following code to the “ThisDocument” object in the VBA project. This will make it so that every time the user creates a new file from the template, word will automatically prompt the user for the site and list.

    Private Sub Document_New()
    Merge_From_SharePointList
    End Sub

    Extelligent Design – SharePoint consulting in Canberra Australia

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s