Last post May 03, 2018 02:13 AM by Deepak Panchal
May 02, 2018 09:10 AM|amerta|LINK
hello guys , i have a question please , i want to export data from sql server to excel ( with specific template : i want to prepare the excel model file from the start ) and i send it directly with email . how can i do that ?
May 02, 2018 09:58 AM|vahid bakkhi|LINK
first you have to fetch your data from database
then write them into excel file and store in a path in your server
finally send excel file with email
here is a good sample that you want , please follow below link:
May 02, 2018 11:58 AM|amerta|LINK
yes i know but i want to do this directly in sql server , do all this step ( export data to template excel and send it to customer ) on one procedure ? CAN I DO THIS ?
May 02, 2018 12:28 PM|vahid bakkhi|LINK
yes you can do it,
first export your data to excel and next send email with sql server
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\test.xlsx;;HDR=YES',
'SELECT * FROM [Sheet1$]')
select * from myTable
follow below links:
for send email you can try :
@profile_name = 'nationstar University',
@recipients = 'firstname.lastname@example.org',
@query = 'select * from myTable' ,
@subject = 'Test email',
@attach_query_result_as_file = 1,
@query_result_separator=' ' -- tab
please refer to below links :
also here are many example from both of them together
May 02, 2018 02:40 PM|amerta|LINK
thank's for your answer , but my problem is how can i export data to specific template excel ( prepare template file ( title , header ...) from the started , after i want to export data to this file ) ?
May 03, 2018 02:13 AM|Deepak Panchal|LINK
You had asked that,"how can i export data to specific template excel ( prepare template file ( title , header ...) from the started , after i want to export data to this file ) ?"
Are you available to use VSTO or Excel VBA Object Model?
If yes, then you can create an Excel file from template from scratch, Then insert data in it from SQL Server and then send mail.
With VSTO you can use C# or VB.Net and with VBA you need to write code in VBA Editor in Excel.
Both VSTO and VBA share the same Object Model.
You can try to refer links below may help you.
Object model (Excel VBA reference)
Excel Object Model Overview
Example code to create Excel file from template.
Dim newBook As Workbook
Dim currBook As Workbook
Dim currSheet As Worksheet
Dim rCnt As Integer, cCnt As Integer
Dim dogNum As Integer, savePath As String, TemplatePath As String
savePath = "C:\Sample\" ' Set as needed
TemplatePath = "C:\Sample\sample.xltx" ' Set as needed
Set currBook = ActiveWorkbook
Set currSheet = currBook.Sheets("Sheet1")
Application.ScreenUpdating = False
Set newBook = Workbooks.Add(TemplatePath)
newBook.SaveAs savePath & "dog_c" & dogNum & ".xlsx"
Example to fetch data from SQL Server to Excel.
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=pubs;"
'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"
'Now open the connection.
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT * FROM Authors"
' Copy the records into cell A1 on Sheet1.
' Tidy up
Set rsPubs = Nothing
Set cnPubs = Nothing
How to import data from Microsoft SQL Server into Microsoft Excel
Example to send mail from Excel.
Private Sub CommandButton1_Click()
Dim xStrFile As String
Dim xFilePath As String
Dim xFileDlg As FileDialog
Dim xFileDlgItem As Variant
Dim xOutApp As Outlook.Application
Dim xMailOut As Outlook.MailItem
Application.ScreenUpdating = False
Set xOutApp = CreateObject("Outlook.Application")
Set xMailOut = xOutApp.CreateItem(olMailItem)
Set xFileDlg = Application.FileDialog(msoFileDialogFilePicker)
If xFileDlg.Show = -1 Then
.BodyFormat = olFormatRichText
.To = "<span class="cloaked_email">email@example.com</span><script>emailProtector.addCloakedMailto("ep_51d57ec9",0);</script>"
.Subject = "test"
.HTMLBody = "test"
For Each xFileDlgItem In xFileDlg.SelectedItems
Set xMailOut = Nothing
Set xOutApp = Nothing
Application.ScreenUpdating = True
How to send email with multiple attachments attached in Excel?
Mail the whole workbook
Same thing you can perform using VSTO.
Note: Office Automation will not properly on Server Side. So If you are working on server side then you may consider to use Open XML or other third party libraries.