Export to Excel and Email SharePoint Lists in a Timer Job

Problem Summary: SharePoint Lists need to be exported as Excel Worksheets and emailed to users on a scheduled basis so that they could be accessed via any device at any time.

The idea was communicating with team members without direct access to the list through a browser due to the nature of their job and also because people generally find it much simpler when information is pushed to their email accounts instead of them having to logon to a portal, click a few times and then be able to get the information. 

For example, a busy Sales Executive who travels a lot and is used to only carrying a mobile device for most purposes. 

Even though the OOTB Export to Excel is a great feature, it cannot be called over an API.  

Solution:

We had to write a custom timer job that would do the following:

- Run on a scheduled basis and find all the lists that need to be emailed as Excel Worksheet (the List Names could be read from a Config list). These lists may be spread across several Site Collections so the timer job needs to read the list name from the Config List.  

- Find the Views that need to be exported for each such list (the view name needs to be read from the Config list). 

- Export each List to a separate Excel Worksheet, using the View Name for Column List. 

- Either save the exported Excel Worksheet to a document library, or send the worksheet as an email, with a custom Email Message (the Email To, Subject and Body could be read from the Config list). 

- We also added a feature where the timer job would dynamically add columns such as an Item Url that don’t exist in the View, just in case the user wanted to access the Item directly by clicking the link. 

Code for the Timer Job can be accessed here. The code will deploy the timer job and feature to all Site Collections. All you need to do is activate the feature on Site Collections you want to run this timer job on. Activation of the feature will provision the Config List, using which you can specify all the fields mention in the explanation above. When the solution is deployed it also copies a template excel file to a folder in the Layouts folder. That was required in order to make the Export to Excel Utility work.