I wish to refresh an excel sheet and email it to a user.
The excel file has a connection to a SQL Database so the file must be refreshed and then emailed out to a user
I have previously done this using some Vb script in a BAT file and triggered this using a Windows scheduled task, but this is a bit legacy now and I want to implement a more professional method.
Below the VB script I previously used:
'Dim objExcel , wb
Set objExcel = CreateObject("[login to view URL]")
set objworkbook = [login to view URL]("c:\ReportDEV\[login to view URL]")
[login to view URL] = False
'Set Wb = [login to view URL] strExcel
[login to view URL]
Some infrastructure and software details
OS= Windows Server 201 R2
SQL Version= Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Visual Studio = MS SQL Server Data tools for Visual Studio 2017 ( SSDT)
MS Office 2016 is also installed on the server
I've done a bit or research and I need this re-written for VS 2017 SSDT and the rest of the package written so that the updated excel file is emailed out using SMTP
I'm very open to better ways to do this, but please note the Excel file contains a lot of formatting so simply extracting the directly from the DB in SSDT is not an option.
I can't supply the file as it includes sensitive information but if you can write and supply the package and demonstrate that it works given the above scenario then that will suffice