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

 

thanks for the tip

 

thanks for tips