infinitizon
MIS
Hell guys,
The closer I get to the completion of this project, the more complex it seems to get.
Please help me look at this code
The challenge is that I want the created excel file to be attached in the mail (that is sent to the user) on the fly without first saving it to the user system or to my application.
What can I do
____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?
Think about it.
The closer I get to the completion of this project, the more complex it seems to get.
Please help me look at this code
Code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Configuration
Imports System.Net.Mail
Imports Class1
Imports Microsoft.Office.Interop
Partial Class HR_v_rpt
Inherits System.Web.UI.Page
Private HRconnectionString As String = ConfigurationManager.ConnectionStrings("HRConnectionString").ConnectionString
Private ConnectionString As String = ConfigurationManager.ConnectionStrings("NAVConnectionString").ConnectionString
Private tblConnectionString As String = ConfigurationManager.ConnectionStrings("tblConnectionString").ConnectionString
Protected Sub btn_getRpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_getRpt.Click
getRpt("rpt_individual_OVT")
End Sub
Protected Function getRpt(ByVal theCommand As String, Optional ByVal opt As String = "what") As Boolean
Dim dt As New DataTable()
'Dim dr As SqlClient.SqlDataReader
Dim connection As New SqlConnection(HRconnectionString)
Dim sqlCmd As New SqlCommand(theCommand, connection)
Try
connection.Open()
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Parameters.AddWithValue("@staff_id", txtStaffId.Text)
Dim reader As SqlDataReader = sqlCmd.ExecuteReader()
Dim recipientEmail As String
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
Dim r As Integer = 1
While reader.Read()
xlWorkSheet.Cells(r, 1) = reader("over_id")
xlWorkSheet.Cells(r, 2) = reader("staff_id")
xlWorkSheet.Cells(r, 3) = reader("dept_id")
xlWorkSheet.Cells(r, 4) = reader("apply_date")
recipientEmail = reader("email")
r += 1
End While
xlWorkBook.Close()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
sendMailToUser(recipientEmail, attach) 'Send the excel file in an attachment to the applicant
If System.IO.File.Exists(loc) = True Then
System.IO.File.Delete(loc)
End If
Catch ex As Exception
ShowAlertMessage("Operation Failed! " & ex.Message)
Return False
End Try
connection.Close()
Return True
End Function
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Protected Sub sendMailToUser(ByVal recipient As String, ByVal dattachment As String)
'mail 1st level supervisor
Dim SmtpServer As New SmtpClient()
Dim mail As New MailMessage()
mail.From = New MailAddress("noreply@ourcompany.com")
mail.To.Add(recipient)
mail.Subject = "Overtime report request"
Dim attachment As System.Net.Mail.Attachment
attachment = New System.Net.Mail.Attachment(dattachment)
mail.Attachments.Add(attachment)
mail.IsBodyHtml = True
SmtpServer.Send(mail)
End Sub
End Class
The challenge is that I want the created excel file to be attached in the mail (that is sent to the user) on the fly without first saving it to the user system or to my application.
What can I do
____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?
Think about it.