Create .csv file and send mail on a particular day of week

Discussion in 'ASP' started by sameer_havakajoka, Jan 22, 2010.

  1. sameer_havakajoka

    sameer_havakajoka New Member

    Joined:
    Sep 14, 2009
    Messages:
    271
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Sleeping
    Location:
    Hava Ke Paro Me

    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
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  3. auctions97

    auctions97 New Member

    Joined:
    Feb 9, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.seozip.com
    thanks for the tip
     
  4. Mohamed IBrahim

    Mohamed IBrahim New Member

    Joined:
    Oct 25, 2010
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    thanks for tips
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice