How to use ASP & Export data from database to excel and send email attachment (Excel.Application) This is learn/tutorial asp developers how to using ASP script Export Data from Database to Excel and Send Email Attachment
ShotDev Focus:
- ASP & Export Data from Database to Excel and Report/Print Format.
Example
asp_excel_mail.asp
<%Option Explicit%> <html> <head> <title>ShotDev.Com Tutorial</title> </head> <body> <% Dim Conn,strSQL,objRec Dim xlApp,xlBook,xlSheet1,FileName,intRows Dim Fso,MyFile Set Conn = Server.Createobject("ADODB.Connection") Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("MyXls/mydatabase.mdb"),"" , "" strSQL = "SELECT * FROM customer " Set objRec = Server.CreateObject("ADODB.Recordset") objRec.Open strSQL, Conn, 1,3 If Not objRec.EOF Then FileName = "MyXls/MyReport.xls" '*** Create Exce.Application ***' Set xlApp = Server.CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add '*** Create Sheet 1 ***' xlBook.Worksheets(1).Name = "My Customer" xlBook.Worksheets(1).Select '*** Width & Height (A1:A1) ***' With xlApp.ActiveSheet.Range("A1:A1") .ColumnWidth = 10.0 End With With xlApp.ActiveSheet.Range("B1:B1") .ColumnWidth = 13.0 End With With xlApp.ActiveSheet.Range("C1:C1") .ColumnWidth = 23.0 End With With xlApp.ActiveSheet.Range("D1:D1") .ColumnWidth = 12.0 End With With xlApp.ActiveSheet.Range("E1:E1") .ColumnWidth = 13.0 End With With xlApp.ActiveSheet.Range("F1:F1") .ColumnWidth = 12.0 End With With xlApp.ActiveSheet.Range("A1:F1") .BORDERS.Weight = 1 .MergeCells = True .Font.Bold = True .Font.Size = 20 .HorizontalAlignment = -4108 End With With xlApp.ActiveSheet.Cells(1,1) .Value = "Customer Report" End With '*** Header ***' With xlApp.ActiveSheet.Cells(3,1) .Value = "CustomerID" .Font.Bold = True .VerticalAlignment = -4108 .HorizontalAlignment = -4108 .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(3,2) .Value = "Name" .Font.Bold = True .VerticalAlignment = -4108 .HorizontalAlignment = -4108 .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(3,3) .Value = "Email" .Font.Bold = True .VerticalAlignment = -4108 .HorizontalAlignment = -4108 .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(3,4) .Value = "CountryCode" .Font.Bold = True .VerticalAlignment = -4108 .HorizontalAlignment = -4108 .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(3,5) .Value = "Budget" .Font.Bold = True .VerticalAlignment = -4108 .HorizontalAlignment = -4108 .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(3,6) .Value = "Used" .Font.Bold = True .VerticalAlignment = -4108 .HorizontalAlignment = -4108 .BORDERS.Weight = 1 End With '***********' intRows = 4 While Not objRec.EOF '*** Detail ***' With xlApp.ActiveSheet.Cells(intRows,1) .Value = objRec.Fields("CustomerID").Value .BORDERS.Weight = 1 .HorizontalAlignment = -4108 End With With xlApp.ActiveSheet.Cells(intRows,2) .Value = objRec.Fields("Name").Value .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(intRows,3) .Value = objRec.Fields("Email").Value .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(intRows,4) .Value = objRec.Fields("CountryCode").Value .HorizontalAlignment = -4108 .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(intRows,5) .Value = objRec.Fields("Budget").Value .BORDERS.Weight = 1 End With With xlApp.ActiveSheet.Cells(intRows,6) .Value = objRec.Fields("Used").Value .BORDERS.Weight = 1 End With intRows = intRows + 1 objRec.MoveNext Wend '*** If Files Already Exist Delete files ***' Set Fso = CreateObject("Scripting.FileSystemObject") If (Fso.FileExists(Server.MapPath(FileName))) Then Set MyFile = Fso.GetFile(Server.MapPath(FileName)) MyFile.Delete End If '*** Save Excel ***' 'xlBook.PrintOut 1 '*** Print to printer ***' xlBook.SaveAs Server.MapPath(FileName) xlApp.Application.Quit '*** Quit and Clear Object ***' Set xlSheet1 = Nothing Set xlBook = Nothing Set xlApp = Nothing '**************** Send Email ******************' Dim myMail,HTML,strMsg Set myMail = Server.CreateObject("CDONTS.NewMail") If Trim(FileName) <> "" Then myMail.AttachFile Server.MapPath(FileName) End If myMail.From = "Webmaster <webmaster@shotdev.com>" myMail.Value("Reply-To") = "shotdev@hotmail.com" myMail.To = "member@shotdev.com" myMail.Subject = "My Excel" myMail.MailFormat = 0 myMail.BodyFormat = 0 myMail.Body = "Convert Access to Excel" myMail.Send Set myMail = Nothing '*************** End Send Email ***************' Response.write"Generate Excel and Email Sending." End If objRec.Close() Conn.Close() Set objRec = Nothing Set Conn = Nothing %> </body> </html>
Create a asp file and save to path root-path/myasp/
Run
http://localhost/myasp/asp_excel_mail.asp
Screenshot
1pattern…
…