Imports System
Imports System.Net
Imports System.Net.Mail
Imports System.Net.Mime
Imports System.Threading
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Mail
Imports System.IO
Imports System.Math
Imports System.Text.RegularExpressions
Imports System.Web
Imports System.Web.HttpUtility
Imports Quiksoft.FreeSMTP

'This console app prototype written in VB.Net(1.1) is designed to send a customized HTML newsletter to a list of recipients similar to a mail merge.
'Features:
'Interacts with SQL server.
'Uses Quicksoft.FreeSMTP DLL to replace slower and less stable(?) System.Web.Mail.
'Includes latest news item headline from DB in Subject of email.
'Provides TEST or SEND parameter for testing purposes prior to each mailing. "TEST" Sends a sample of the list to admin.
'Reads http request and uses find/replace with tokens in HTML to customize each email for the recipient.
'Validates email address format using RegEx and logs bad email addresses to a table.
'Logs each mailing with statistics at the beginning & end.
'Namespace MyColor
'Public Class Color
'Sub Color()
'' Do something
'End Sub
'End Class
'End Namespace
'You can also use the Color object somewhere else in your program like this:
'Dim c As New MyColor.Color
'c.Color()

Namespace Mail_hel
    Public Class usage
        Public Sub DisplayUsage()
            Console.WriteLine("Usage: mail.exe test|send")
        End Sub
    End Class

    Public Class Start
        Public Shared Sub Main(ByVal func_arg() As String)

            'FOR ARTICLE-BASED SUBJECT LINE
            ' Dim conn_str As String = "Data Source=TAHER-PC\SQLEXPRESS;Initial Catalog=newslatter;Integrated Security=True"

            '"server=SQL.DOMAIN.COM;Initial Catalog=DATABASE;UID=USER;Pwd=PASSWORD;application Name={0}"

            'Dim sql_conn As New SqlConnection(conn_str)
            'sql_conn.Open()
            'Dim sql_string, article_title As String
            'sql_string = "SELECT TOP 1 title FROM test ORDER BY stamp DESC"
            'Dim sql_command As New SqlCommand(sql_string, sql_conn)
            'Dim sql_datareader As SqlDataReader
            'sql_datareader = sql_command.ExecuteReader()
            'If sql_datareader.Read Then
            'article_title = CatchNull(sql_datareader.Item("title"))
            'End If
            'sql_datareader.Close()

            'GENERAL VARIABLES
            Dim subject_str = "News : " '& article_title
            Dim mail_doc As String = "newsletter"
            Dim page_url As String = "http://SERVER/PAGE_URL.ASP"
            Dim test_email As String = "TAHER@DOMAIN.COM"
            Dim from_email = "NEWS@DOMAIN.COM"
            Dim from_alias = "MY COMPANY"
            Dim recipient_view As String = "RECIPIENT_VIEW"
            Dim recip_alias As String = "SUBSCRIBER"
            Dim test_quantity As Integer = 300

            'SCRIPT ROLE/FUNCTION FROM ARGUMENT (TEST OR SEND) MODIFIES RECIPIENT AND LENGTH OF LIST
            Dim func As String
            Dim top_str As String
            Dim sel_prefix As String
            Dim sel_suffix As String
            Try
                func = func_arg(0)
                Select Case func
                    Case "test"
                        sel_prefix = "SET ROWCOUNT " & test_quantity
                        sel_suffix = " SET ROWCOUNT 0"
                    Case "send"
                        sel_prefix = ""
                        sel_suffix = ""
                    Case Else
                        Dim use As usage = New usage()
                        use.DisplayUsage()
                        Throw New ApplicationException("Usage Error.")
                End Select
            Catch e As IndexOutOfRangeException
                Dim use As usage = New usage()
                use.DisplayUsage()
            End Try

            ' READ WEB PAGE
            Dim page_result As String = read_webpage(page_url)

            ' COUNT RECIPIENTS
            'Dim tot_recips As Integer = 0
            'sql_string = sel_prefix & " SELECT COUNT(email) AS recordcount FROM " & recipient_view & sel_suffix
            'sql_command = New SqlCommand(sql_string, sql_conn)
            'sql_datareader = sql_command.ExecuteReader()
            'If sql_datareader.Read() Then
            'tot_recips = sql_datareader.Item("recordcount")
            'End If
            'sql_datareader.Close()

            ' SELECT RECIPIENTS
            'sql_string = sel_prefix & "SELECT email_field FROM " & recipient_view & sel_suffix
            'sql_command = New SqlCommand(sql_string, sql_conn)
            'sql_datareader = sql_command.ExecuteReader()

            ' INITIALIZE
            Console.WriteLine("Function: " & func)
            Dim counter As Integer = 0
            Dim begin_time As DateTime = Now()
            Dim Message As New EmailMessage()
            Dim log_bad, user_email, user_source, user_id, user_ident, user_pass As String

            'log_mailing("Start", 0, 0, tot_recips, mail_doc)

            'While sql_datareader.Read()

            ' CHECK EMAIL FORMAT
            'user_email = CatchNull(sql_datareader.Item("EMAIL_FIELD"))
            If valid_email(user_email) Then
                counter = counter + 1

                ' SETUP MAIL INSTANCE AND PROPERTIES
                Message = New EmailMessage()
                Dim mail_body As String = replace_content(user_email, mail_doc, page_result)

                With Message
                    If func = "send" Then
                        .Recipients.Add(user_email, recip_alias, RecipientType.To)
                    Else
                        .Recipients.Add(test_email, recip_alias, RecipientType.To)
                    End If
                    .Subject = subject_str
                    .BodyParts.Add(mail_body, BodyPartFormat.HTML, BodyPartEncoding.None)
                    .From.Email = from_email
                    .From.Name = from_alias
                End With
                Dim smtp_server As New SMTP("MAIL.DOMAIN.COM")
                smtp_server.Send(Message)

                ' LOG EMAIL ADDRESS SENT
                log_email(user_email, mail_doc, counter)

                ' PROVIDE OCCASIONAL COMMAND LINE FEEDBACK
                If counter Mod 1000 = 0 Then
                    Console.WriteLine("Sent: " & counter)
                End If
            Else
                ' LOG IF BAD EMAIL
                log_bad_email(user_email, mail_doc)
            End If
            Message = Nothing
            End While
            sql_datareader.Close()
            sql_conn.Close()

            ' END & CALCULATE
            Dim end_time As DateTime = Now()
            Dim total_time As System.TimeSpan = end_time.Subtract(begin_time)
            Dim tot_secs As Integer = total_time.TotalSeconds
            Dim tot_mins As Single = Round(total_time.TotalMinutes, 2)
            Dim per_minute As Single = Round(counter / tot_mins, 2)
            Dim per_second As Single = Round(counter / tot_secs, 2)

            Console.WriteLine("MAIL REPORT")
            Console.WriteLine("-----------")
            Console.WriteLine("begin_time: " & begin_time)
            Console.WriteLine("end_time: " & end_time)
            Console.WriteLine("tot_secs: " & tot_secs)
            Console.WriteLine("tot_mins: " & tot_mins)
            Console.WriteLine("per_minute: " & per_minute)
            Console.WriteLine("per_second: " & per_second)
            Console.WriteLine("total_sent: " & counter)

            ' LOG MAILING FINISH
            log_mailing("Finished", tot_mins, per_minute, counter, mail_doc)
        End Sub

        Public Shared Sub log_mailing(ByVal log_status, ByVal log_total_mins, ByVal log_per_minute, ByVal log_count, ByVal log_doc)
            ' LOG MAILING - THIS IS RUN AT THE BEGINING & END OF A MAILING
            Dim conn_str As String = "Data Source=TAHER-PC\SQLEXPRESS;Initial Catalog=newslatter;Integrated Security=True"
            '"server=SQL.DOMAIN.COM;Initial Catalog=DATABASE;UID=USER;Pwd=PASSWORD;application Name={0}"
            Dim sql_conn = New SqlConnection(conn_str)
            sql_conn.Open()
            Dim sql_str As String = "" & _
            "INSERT INTO MAIL_LOG(log_status, log_total_mins, log_per_minute, log_count, log_doc) " & _
            "VALUES(@log_status, @log_total_mins, @log_per_minute, @log_count, @log_doc)"
            Dim sql_comm As New SqlCommand()
            sql_comm.Connection = sql_conn
            sql_comm.Parameters.Add("@log_status", Data.SqlDbType.VarChar).Value = log_status
            sql_comm.Parameters.Add("@log_total_mins", Data.SqlDbType.Float).Value = Round(log_total_mins, 2)
            sql_comm.Parameters.Add("@log_per_minute", Data.SqlDbType.Float).Value = Round(log_per_minute, 2)
            sql_comm.Parameters.Add("@log_count", Data.SqlDbType.Int).Value = log_count
            sql_comm.Parameters.Add("@log_doc", Data.SqlDbType.VarChar).Value = log_doc
            sql_comm.CommandText = sql_str
            sql_comm.ExecuteNonQuery()
            sql_comm.Connection.Close()
        End Sub

        Public Shared Sub log_email(ByVal email_field As String, ByVal doc As String, ByVal counter As Integer)
            'LOGS EACH EMAIL ADDRESS INTO A DATABASE EACH TIME A NEWSLETTER IS SENT
            Dim conn_str As String = "Data Source=TAHER-PC\SQLEXPRESS;Initial Catalog=newslatter;Integrated Security=True"
            ' "server=SQL.DOMAIN.COM;Initial Catalog=DATABASE;UID=USER;Pwd=PASSWORD;application Name={0}"
            Dim sql_conn As SqlConnection = New SqlConnection(conn_str)
            Dim sql_str As String = "INSERT INTO MAIL_SENT(email_field, document, delivery_count) " & _
             "VALUES('" & email_field & "','" & doc & "', " & counter & ")"
            Dim sql_command As New SqlCommand(sql_str, sql_conn)
            sql_command.Connection.Open()
            sql_command.ExecuteNonQuery()
            sql_command.Connection.Close()
        End Sub

        Public Shared Sub log_bad_email(ByVal email_field As String, ByVal doc As String)
            'CHECK FOR BAD EMAIL ADDRESS IN BAD-EMAIL TABLE AND LOG IF NOT FOUND ALREADY
            Dim conn_str As String = "Data Source=TAHER-PC\SQLEXPRESS;Initial Catalog=newslatter;Integrated Security=True"
            '"server=SQL.DOMAIN.COM;Initial Catalog=DATABASE;UID=USER;Pwd=PASSWORD;application Name={0}"
            Dim sql_conn As SqlConnection = New SqlConnection(conn_str)
            sql_conn.Open()
            Dim sql_string As String = "SELECT TOP 1 email_field FROM bad_mail WHERE email_field = @email_field"
            Dim sql_command As New SqlCommand()
            sql_command.Connection = sql_conn
            sql_command.Parameters.Add("@email_field", Data.SqlDbType.VarChar).Value = email_field
            sql_command.CommandText = sql_string

            Dim sql_datareader As SqlDataReader
            sql_datareader = sql_command.ExecuteReader()
            Dim found_flag As Boolean = False
            If sql_datareader.Read() Then
                found_flag = True
            End If
            sql_datareader.Close()

            If found_flag = False Then
                sql_conn = New SqlConnection(conn_str)
                sql_conn.Open()
                Dim sql_str As String = "INSERT INTO BAD_MAIL(email_field, document) VALUES(@email_field, @document)"
                Dim sql_comm As New SqlCommand()
                sql_comm.Connection = sql_conn
                sql_comm.Parameters.Add("@email_field", Data.SqlDbType.VarChar).Value = email_field
                sql_comm.Parameters.Add("@document", Data.SqlDbType.VarChar).Value = doc
                sql_comm.CommandText = sql_str
                sql_comm.ExecuteNonQuery()
                sql_comm.Connection.Close()
            End If
        End Sub

        Public Shared Function read_webpage(ByVal page_url As String) As String
            'GET CONTENT OF WEB-PAGE/NEWSLETTER TEMPLATE
            Dim web_Response As WebResponse
            Dim web_Request As WebRequest
            web_Request = System.Net.HttpWebRequest.Create(page_url)
            web_Response = web_Request.GetResponse()
            Dim stream_reader As New StreamReader(web_Response.GetResponseStream())
            Dim result As String = stream_reader.ReadToEnd()
            stream_reader.Close()
            Return result
        End Function

        Public Shared Function valid_email(ByVal email_str As String) As Boolean
            'CHECK FOR EMAIL FORMAT - THIS PATTERN REQUIRES MORE THEN 1 CHARACHTER PRECEDING THE @ SYMBOL??
            Dim reg_exp As New Regex("^[\w][\w\.-]*[\w]@[\w][\w\.-]*[\w]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$")
            valid_email = reg_exp.IsMatch(email_str)
        End Function

        Public Shared Function replace_content(ByVal user_email As String, ByVal mail_doc As String, ByVal page_result As String) As String
            'FIND CONTENT TOKEN IN NEWSLETTER TEMPLATE STRING AND REPLACE WITH DYNAMIC CONTENT/EMAIL ADDRESS (UNSUBSCRIBE STRING, TRACKING STRING)
            Dim find_str, replace_str, unsub_str, this_message, unsub_url

            this_message = page_result

            find_str = "<!-- |EMAIL_TOKEN| -->"
            replace_str = user_email
            this_message = this_message.Replace(find_str, replace_str)

            unsub_url = "http://WWW.DOMAIN.COM/subscrip.aspx?email_field=" & user_email
            unsub_str = "To remove " & user_email & " from our recipient list, click <a href='" & unsub_url & "'>unsubscribe</a>."
            find_str = "</body>"
            replace_str = unsub_str & track_str & find_str
            this_message = this_message.Replace(find_str, replace_str)

            Return this_message
        End Function

        Public Shared Function CatchNull(ByVal db_val As Object) As Object
            'GENERIC FUNCTION TO PREVENT ERROR FROM NULLS IN DATA
            If db_val Is Nothing Or db_val Is DBNull.Value Then
                Return Nothing
            Else
                Return db_val
            End If
        End Function

    End Class
End Namespace


'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Module email()
    Sub Main()
        Dim strMSG As String
        Dim strArgs() As String = Command.Split(",")
        Dim blnSMTP As Boolean = False
        Dim blnCC As Boolean = False
        Dim blnAttachments As Boolean = False

        'get the product name, version and description from the assembly
        strMSG = vbCrLf + vbCrLf + _
          System.Diagnostics.FileVersionInfo.GetVersionInfo( _
      System.Reflection.Assembly.GetExecutingAssembly.Location).ProductName _
              + " v" + System.Diagnostics.FileVersionInfo.GetVersionInfo( _
      System.Reflection.Assembly.GetExecutingAssembly.Location _
            ).ProductVersion + vbCrLf + _
         System.Diagnostics.FileVersionInfo.GetVersionInfo( _
         System.Reflection.Assembly.GetExecutingAssembly.Location _
              ).Comments + vbCrLf + vbCrLf

        If UBound(strArgs) < 3 Then
            strMSG = strMSG + "Usage: EPSendMail from@email.com, " + _
                   "to@email.com, subject, message, [smtp Server]," + _
                   "[cc1@email.com;cc2@email.com;...], [attachment1;" + _
                        "attachment2;...]" + vbCrLf
            Console.Write(strMSG)
            Exit Sub
        End If

        strMSG = strMSG + "Sending email message" + vbCrLf + _
            "  From        --> " + Trim(strArgs(0)) + vbCrLf + _
            "  To          --> " + Trim(strArgs(1)) + vbCrLf + _
            "  Subject     --> " + Trim(strArgs(2)) + vbCrLf + _
            "  Message     --> " + Trim(strArgs(3)) + vbCrLf
        If UBound(strArgs) >= 4 Then
            If Len(Trim(strArgs(4))) > 0 Then
                blnSMTP = True
                strMSG = strMSG + "  SMTP Server --> " + Trim(strArgs(4)) + _
                   vbCrLf
            End If
        End If
        If UBound(strArgs) >= 5 Then
            If Len(Trim(strArgs(5))) > 0 Then
                blnCC = True
                strMSG = strMSG + "  CC          --> " + Trim(strArgs(5)) + _
                   vbCrLf
            End If
        End If
        If UBound(strArgs) >= 6 Then
            If Len(Trim(strArgs(6))) > 0 Then
                blnAttachments = True
                strMSG = strMSG + "  Attachments --> " + Trim(strArgs(6)) + _
                   vbCrLf
            End If
        End If
        Console.Write(strMSG)

        'send the email
        Try
            Dim insMail As New MailMessage()
            With insMail
                .From = Trim(strArgs(0))
                .To = Trim(strArgs(1))
                .Subject = Trim(strArgs(2))
                .Body = Trim(strArgs(3))
                If blnCC Then .Cc = Trim(strArgs(5))
                If blnAttachments Then
                    Dim strFile As String
                    Dim strAttach() As String = Split(strArgs(6), ";")
                    For Each strFile In strAttach
                        .Attachments.Add(New MailAttachment(Trim(strFile)))
                    Next
                End If
            End With
            If blnSMTP Then SmtpMail.SmtpServer = Trim(strArgs(4))

            SmtpMail.Send(insMail)

            Console.WriteLine("Successfully sent email message" + vbCrLf)

        Catch err As Exception
            Console.WriteLine("EXCEPTION " + err.Message + vbCrLf)
        End Try

    End Sub
End Module

Module send_mail
    Dim SmtpServer As New SmtpClient()
    Dim mail As New MailMessage()
    Public Function mailcall(ByVal mail_id)
        Try
            SmtpServer.Credentials = New  _
            Net.NetworkCredential("taher4488@gmail.com", "*********")
            SmtpServer.EnableSsl = True
            SmtpServer.Port = 587
            SmtpServer.Host = "smtp.gmail.com"
            mail = New MailMessage()
            mail.From = New MailAddress("taher@qline-dubai.com")
            mail.To.Bcc.add(mail_id)
            mail.Subject = "Test Mail"
            mail.IsBodyHtml = True
            mail.Body = "This is for testing SMTP mail"
            'Dim attachment As System.Net.Mail.Attachment
            'attachment = New System.Net.Mail.Attachment("C:\Users\Taher\Pictures\24072011.jpg")
            'mail.Attachments.Add(attachment)
            SmtpServer.Send(mail)
            MsgBox("mail send")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        Me.close()
    End Function
End Module


Partial Public Class sendMail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Me.Visible = False
        'Dim str As String = "Data Source=TAHER-PC\SQLEXPRESS;Initial Catalog=newslatter;Integrated Security=True "
        'Dim myconn As New SqlConnection(str)
        'myconn.Open()
        'Dim DA As New SqlDataAdapter("select *From test", myconn) 'where name='taher'
        'Dim ds As New DataSet
        'DA.Fill(ds, "test")
        'Dim i As Integer
        'For i = 0 To ds.Tables(0).Rows.Count - 1
        'MsgBox(" " + ds.Tables(0).Rows(i).Item(0).ToString)
        'Next

        'MsgBox("mail send" + ds.Tables(0).Rows.Count.ToString)
        'TextBox1.Text = ds.Tables(0).Rows(1).Item(0).ToString()
        'myconn.Close()

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        Dim str As String = "Data Source=TAHER-PC\SQLEXPRESS;Initial Catalog=newslatter;Integrated Security=True "
        Dim myconn As New SqlConnection(str)
        myconn.Open()
        Dim DA As New SqlDataAdapter("select *From test", myconn) 'where name='taher'
        Dim ds As New DataSet
        DA.Fill(ds, "test")
        Dim i As Integer
        For i = 0 To ds.Tables(0).Rows.Count - 1
            send_mail.mailcall(ds.Tables(0).Rows(i).Item(0).ToString)
        Next
    End Sub
End Class