Please consider the following console app prototype designed to send a bulk email mail-merge style newsletter document based on webpage content.
I'd really appreciate any critical comments about code style, methodologies, error trapping (or lack of), general efficiency, consistency, etc.
This code uses a free third party SMTP dll to replace System.Web.Mail to derive more speed from the server (initial tests indicated 1.5-2.0 times speed increase).
My goal through this post is to share my methods while opening the code for improvement and help me learn to be a better coder!
Thanks in advance for any feedback!
'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.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Math
Imports System.Net
Imports System.Text.RegularExpressions
Imports System.Web
Imports System.Web.HttpUtility
Imports Quiksoft.FreeSMTP
Namespace Mail
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 = "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 articles 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 = "DEV@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_field) 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 = "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 = "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 = "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