VB -- Function sends email

email_function.vb

Imports System.Net.Mail

 Sub aspireReference1Email()

        'Get all contents from database for emailing and PDF 
        Dim credentials As String = Session("UserID")
        Dim dt As New DataTable
        Dim ASPIRE As String = "ASPIRE"
        Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("CRTCConnectionString").ConnectionString)
        sqlConn.Open()
        Dim sqlCmd = New SqlCommand("SELECT ApplicantID,FirstName,LastName,Email,Reference1_FirstName,Reference1_LastName,Reference1_MI,Reference1_Organization,Reference1_Title,Reference1_Phone,Reference1_Email FROM Applicant WHERE Email = '" & credentials & "' AND Program_Type = '" + ASPIRE + "'", sqlConn)
        Dim sqlAdapter = New SqlDataAdapter(sqlCmd)
        sqlAdapter.Fill(dt)

        'Check if applicant has submitted an application previously
        Dim sqlCmd2 As New SqlCommand("SELECT * FROM Document WHERE Email = '" & credentials & "' AND Program_Type = '" & ASPIRE & "'", sqlConn)
        Dim sqlDA As New SqlDataAdapter(sqlCmd2)
        Dim dt2 As New DataTable
        sqlDA.Fill(dt2)

        'If the document table returns any rows that indicates they have been past this section before
        'So exit this sub and don't send an email 
        If (dt2.Rows.Count > 0) Then
            Exit Sub
        Else


            'Send Email Reference
            Dim id As Integer = dt.Rows(0)("ApplicantID").ToString()
            Dim link As String = "vm-domweb2.dom.wustl.edu/CRTC/admin/ref1-upload.aspx?applicantid=" & id
            Dim recipient As String = dt.Rows(0)("Reference1_Email").ToString()
            Dim applicantFName As String = dt.Rows(0)("FirstName").ToString()
            Dim applicantLName As String = dt.Rows(0)("LastName").ToString()
            Dim mail As New MailMessage()
            mail.To.Add(recipient)
            mail.From = New MailAddress(ConfigurationManager.AppSettings("EmailFrom").ToString())
            Dim smtp As New System.Net.Mail.SmtpClient(ConfigurationManager.AppSettings("MailServer").ToString())
            mail.IsBodyHtml = True
            mail.Subject = "Letter of Recommendation Request for ASPIRE Program Applicant"
            mail.Body = "<p>" & applicantFName & " " & applicantLName & " " & "is applying to the ASPIRE program and has requested a Letter of Recommendation from you" & "<br/><br/>" + ControlChars.NewLine &
            "Please use offical letterhead and sign your letter (a digital or scanned signature is sufficient)." & "<br/>" & "Letters that are not on letterhead or those which are unsigned, will not be considered." & "<br/>""<br/>" + ControlChars.NewLine &
            "Please complete and upload your signed Letter of Recommendation by clicking:" & " " & "<a href=" & link & ">here</a>" & "<br/><br/>" & ControlChars.NewLine &
            "*Note: If the link does not work, copy and paste it into your web browser. This link is unique to this particular" & "<br/>" & "appliant and may NOT be used to upload documents for other applicants." & "<br/>""<br/" + ControlChars.NewLine &
            "If you are the Department or Divison Chair for this applicant, please address the following items in your letter:" & "<br/><br/>" & ControlChars.NewLine &
            "- Guarantee to provide a minimum of 75% protected time for the applicant (50% for sugrgeons) for two years." & "<br/><br/>" & ControlChars.NewLine &
            "- Your commitment to the applicant if he/she is not acccepted into the ASPIRE Program." & "<br/><br/>" & ControlChars.NewLine &
            "- The applicant's potential for an independent clinical research career in academics." & "<br/><br/>" & ControlChars.NewLine &
            "- The applicant's current status and his/her potential to obtain a faculty appointment." & "<br/><br/></p>" & ControlChars.NewLine &
            "Thank you," & "<br/""<br/>" & ControlChars.NewLine &
            "Clinical Research Training Center" + "<br/>" & ControlChars.NewLine &
            "Washington University Insitute of Clinical and Translational Sciences" & "<br/>" & ControlChars.NewLine &
            "Campus Box 8051" & "<br/>" & ControlChars.NewLine &
            "660 South Euclid Avenue St.Louis, Missouri 63110" & "<br/>" + ControlChars.NewLine &
            "Phone: 314-454-8224" & "<br/>" & ControlChars.NewLine &
            "Email:<a href = ""[email protected]"">[email protected]</a>" & "<br/>" & ControlChars.NewLine &
            "Web:<a href = ""crtc.wustl.edu"">crtc.wustl.edu</a>"
            Try
                smtp.Send(mail)
            Catch exc As Exception
                Response.Write("<script>alert('Email send fail');</script>")
            End Try
        End If
    End Sub