| sameer_havakajoka |
22Jan2010 16:16 |
Create .csv file and send mail on a particular day of week
Introduction
This is a windows service where It will create a .csv file and sends mail on a particular day of week
Background
This solution is used for creating .csv file from datatable and send it on a particular day of week. As it is a windows service no manual intervention is needed.
The code
Code:
Imports System.Data
Imports System.IO
Imports System.Net
Imports System.Net.Mail
Imports System.Configuration
Imports System.Collections.Generic
Imports Microsoft.Office.Interop
**********************************************************************
Method to create Csv file and sent it on a particular day of week.
************************************************************************
Public Class ProcessBatchFile
Public Sub PassToCSV()
Try
Dim ToAddr As String = "Email To Address"
Dim sMessage As String = "Please find attached batch file"
Dim sSubject As String = "Batch File"
Dim sFilePath As String "D:\Batch.csv")
Dim objDataFactory As New clsDataFactory
create object for dataclass
Dim oDT As DataSet = New DataSet
If Now.DayOfWeek <> DayOfWeek.Tuesday Then
ControlVariable("MailSent") = "" 'Add one flag in database
End If
If Now.DayOfWeek = DayOfWeek.Tuesday Then
If Not ControlVariable("MailSent") = "Sent" Then
'check if mail has already been sent
oDT = objDataFactory.GetDetails()
'Get Data from datatable
If File.Exists(sFilePath) Then
File.Delete(sFilePath)
End If
CreateCSVFile(oDT, sFilePath)
SendMail(ToAddr, sMessage, sSubject, sFilePath)
ControlVariable("MailSent", "Sent") = "Sent"
'Add one flag in database when mail is sent
End If
End If
Catch ex As Exception
LogErrormesaage
End Try
End Sub
#Region "Export Grid to CSV"
' Create the CSV file to which grid data will be exported.
Public Sub CreateCSVFile(ByVal ds As DataSet, ByVal strFilePath As String)
Try
Dim sw As StreamWriter = Nothing
sw = New StreamWriter(strFilePath, False)
'sw.AutoFlush = True
Using sw
Dim dt1 As DataTable = ds.Tables(0)
Dim dt2 As DataTable = ds.Tables(1)
' First we will write the headers.
'DataTable dt = m_dsProducts.Tables[0];
Dim iColCount As Integer = dt1.Columns.Count
For i As Integer = 0 To iColCount - 1
sw.Write(dt1.Columns(i))
If i < iColCount - 1 Then
sw.Write(",")
End If
Next
sw.Write(sw.NewLine)
' Now write all the rows.
For Each dr As DataRow In dt1.Rows
For i As Integer = 0 To iColCount - 1
If Not Convert.IsDBNull(dr(i)) Then
sw.Write(dr(i).ToString())
End If
If i < iColCount - 1 Then
sw.Write(",")
End If
Next
sw.Write(sw.NewLine)
Next
sw.Write(sw.NewLine)
' Now write all the rows.
For Each dr As DataRow In dt1.Rows
For i As Integer = 0 To iColCount - 1
If Not Convert.IsDBNull(dr(i)) Then
sw.Write(dr(i).ToString())
End If
If i < iColCount - 1 Then
sw.Write(",")
End If
Next
sw.Write(sw.NewLine)
Next
sw.AutoFlush() = True
sw.Flush()
sw.Close()
sw.Dispose()
End Using
sw.Close()
Catch ex As Exception
Throw
End Try
End Sub
#End Region
'This Method is used to send created .csv file to respective person
Public Function SendMail(ByVal sToAddr As String, ByVal sMessage As String, ByVal sSubject As String, ByVal sAttachment As String, Optional ByVal sCCAddr As String = Nothing) As Boolean
Try
Dim sAccount As String = "Email Account"' Specify Email account()
Dim sPassword As String = "Email Password"' Specify Email Pwd()
Dim sServer As String = "Email Server" Specify Email Server
Dim sMail_FrmAddress As String = "Email From Address" Specify Email from address
Dim mail As MailMessage = New MailMessage()
mail.From = New MailAddress(sMail_FrmAddress)
Dim sToAdrCol As String() = sToAddr.Split(","c)
If sCCAddr <> "" Then
Dim sCCAdrCol As String() = sCCAddr.Split(","c)
'<-------------------Adding CC Addresses--------------->
For Each s As String In sCCAdrCol
If s.Trim <> "" Then
mail.CC.Add(s.Trim)
End If
Next
End If
'<-------------------Adding To Addresses--------------->
For Each s As String In sToAdrCol
If s.Trim <> "" Then
mail.To.Add(s.Trim)
End If
Next
mail.Subject = sSubject
mail.Body = sMessage
mail.Attachments.Add(New Attachment(sAttachment))
'<-------------------Sending The mail--------------->
Dim smtp As SmtpClient = New SmtpClient(sServer)
smtp.Credentials = New NetworkCredential(sAccount, sPassword)
smtp.Send(mail)
Return True
Catch ex As Exception
Throw ex
Return False
End Try
End Function
End Class
|
