Greetings again experts and sorry for the trouble again.
The task seems simple but it is not working.
The requirement is that out app should send out reminder emails three (3) times a week reminding users of their responsibility to complete a form as required of them. This part works well.
Lately, they wanted us to get the app to also display to the user how many days left to complete the form before expiration date.
For instance, when the user receives an email, the message should start with something like:
Hello, John Doe, there are 20 days left to complete this form.....
Right now, when the user receives an email, the number of days left is not getting displayed.
We have a calculated field called DaysRemaining.
Any idea how to get this to display the value of DaysRemaining on the email body?
The complete code is below.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="EmailReminders.aspx.vb" Inherits="EmailReminders" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<br /><br />
<div style="margin-left: 300px;">
<asp:Label ID="lblTotal" runat="server" Style="font-size: 14px; font-weight: 600; color: firebrick;" Text="Label"></asp:Label><br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="3"
OnPageIndexChanging="OnPageIndexChanging" DataKeyNames="mailID">
<Columns>
<asp:TemplateField HeaderText="Select All">
<HeaderTemplate>
<asp:CheckBox ID="chkAll" runat="server" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="mailID" HeaderText="Id" ItemStyle-Width="30" />
<asp:BoundField DataField="FullName" HeaderText="Name" ItemStyle-Width="150" />
<asp:TemplateField HeaderText="EmpEmail">
<ItemTemplate>
<asp:HyperLink ID="lnkEmail" runat="server" Text='<%# Eval("EmpEmail") %>' NavigateUrl='<%# Eval("EmpEmail", "mailto:{0}") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script type="text/javascript">
$("[id*=GridView1] [id*=chkAll]").on("click", function () {
//Get the reference of Header CheckBox.
var chkAll = $(this);
//Loop through all GridView CheckBoxes except Header CheckBox.
$("[id*=GridView1] [id*=chkSelect]").not("[id*=chkAll]").each(function () {
$(this)[0].checked = chkAll[0].checked;
});
});
</script>
<script type="text/javascript">
$("[id*=GridView1] [id*=chkSelect]").on("click", function () {
//Get the reference of Header CheckBox.
var chkAll = $("[id*=GridView1] [id*=chkAll]");
//Set Header CheckBox checked to true.
chkAll[0].checked = true;
//Loop through all GridView CheckBoxes except Header CheckBox.
$("[id*=GridView1] [id*=chkSelect]").not("[id*=chkAll]").each(function () {
if (!$(this).is(":checked")) {
chkAll[0].checked = false;
return;
}
});
});
</script>
<br />
<asp:Button Text="Send Email Reminder" runat="server" OnClick="SendReminderEmail" /><br /><br />
<asp:Label ID="lblMsg" runat="server" style="font-size: 16px; font-weight: 700; color: green;"></asp:Label>
</div>
</form>
</body>
</html>
Imports System.Net
Imports System.Net.Mail
Imports System.Data
Imports System.Threading.Tasks
Imports System.Data.SqlClient
Partial Class EmailReminders
Inherits System.Web.UI.Page
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Me.AddEmpInfo()
Dim checkboxArray As ArrayList
If ViewState("CheckBoxArray") Is Nothing Then
checkboxArray = New ArrayList()
Else
checkboxArray = CType(ViewState("CheckBoxArray"), ArrayList)
End If
If Me.IsPostBack Then
Dim checkBoxIndex As Integer
Dim checkAllWasChecked As Boolean = False
Dim chkAll As CheckBox = CType(GridView1.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
Dim checkAllIndex As String = "chkAll-" & GridView1.PageIndex
If chkAll.Checked Then
If checkboxArray.IndexOf(checkAllIndex) = -1 Then
checkboxArray.Add(checkAllIndex)
End If
Else
If checkboxArray.IndexOf(checkAllIndex) <> -1 Then
checkboxArray.Remove(checkAllIndex)
checkAllWasChecked = True
End If
End If
For i As Integer = 0 To GridView1.Rows.Count - 1
If GridView1.Rows(i).RowType = DataControlRowType.DataRow Then
Dim chk As CheckBox = CType(GridView1.Rows(i).Cells(0).FindControl("chkSelect"), CheckBox)
checkBoxIndex = GridView1.PageSize * GridView1.PageIndex + (i + 1)
If chk.Checked Then
If checkboxArray.IndexOf(checkBoxIndex) = -1 AndAlso Not checkAllWasChecked Then
checkboxArray.Add(checkBoxIndex)
End If
Else
If checkboxArray.IndexOf(checkBoxIndex) <> -1 OrElse checkAllWasChecked Then
checkboxArray.Remove(checkBoxIndex)
End If
End If
End If
Next
End If
ViewState("CheckBoxArray") = checkboxArray
GridView1.DataSource = LoadData()
GridView1.DataBind()
lblTotal.Text = LoadData().Rows.Count.ToString() & " members have not yet completed the report"
End Sub
Protected Sub OnPageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
If ViewState("CheckBoxArray") IsNot Nothing Then
Dim CheckBoxArray As ArrayList = CType(ViewState("CheckBoxArray"), ArrayList)
Dim checkAllIndex As String = "chkAll-" & GridView1.PageIndex
If CheckBoxArray.IndexOf(checkAllIndex) <> -1 Then
Dim chkAll As CheckBox = CType(GridView1.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
chkAll.Checked = True
End If
For i As Integer = 0 To GridView1.Rows.Count - 1
If GridView1.Rows(i).RowType = DataControlRowType.DataRow Then
If CheckBoxArray.IndexOf(checkAllIndex) <> -1 Then
Dim chk As CheckBox = CType(GridView1.Rows(i).Cells(0).FindControl("chkSelect"), CheckBox)
chk.Checked = True
Else
Dim CheckBoxIndex As Integer = GridView1.PageSize * (GridView1.PageIndex) + (i + 1)
If CheckBoxArray.IndexOf(CheckBoxIndex) <> -1 Then
Dim chk As CheckBox = CType(GridView1.Rows(i).Cells(0).FindControl("chkSelect"), CheckBox)
chk.Checked = True
End If
End If
End If
Next
End If
End Sub
Private Sub AddEmpInfo()
Dim conMyData As SqlConnection
Dim cmdAdd As SqlCommand
Try
conMyData = New SqlConnection(constring)
cmdAdd = New SqlCommand("EmailNotify", conMyData)
With cmdAdd
.CommandType = CommandType.StoredProcedure
conMyData.Open() 'open a connection
.ExecuteNonQuery() 'execute it
End With
Catch ex As Exception
Throw ex
Finally
cmdAdd = Nothing
conMyData.Close()
conMyData = Nothing
End Try
End Sub
Protected Function LoadData() As DataTable
'Now, query Email Notifications table, grab the newly inserted employee info and send email reminders to them.
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(3) {New DataColumn("mailID", GetType(Integer)),
New DataColumn("FullName", GetType(String)),
New DataColumn("EmpEmail", GetType(String)),
New DataColumn("DaysRemaining", GetType(Integer))})
' Replace the connection string, query, and field names with your actual database details
Dim query As String = "SELECT mailID, FullName, EmpEmail,DATEDIFF(DAY,GETDATE(),'05/31/2025') AS DaysRemaining FROM EmailNotifications where sent='No'"
Using sqlConnection As New SqlConnection(constring)
Using sqlCommand As New SqlCommand(query, sqlConnection)
sqlConnection.Open()
Dim reader As SqlDataReader = sqlCommand.ExecuteReader()
While reader.Read()
' Assuming your database fields are named "Id", "Name", and "Email"
Dim mailId As Integer = Convert.ToInt32(reader("mailID"))
Dim fullName As String = Convert.ToString(reader("FullName"))
Dim email As String = Convert.ToString(reader("EmpEmail"))
Dim NumDaysLeft As String = Convert.ToString(reader("DaysRemaining"))
dt.Rows.Add(mailId, fullName, email, DaysRemaining)
End While
End Using
End Using
Return dt
End Function
Protected Sub SendReminderEmail(ByVal sender As Object, ByVal e As EventArgs)
Dim dtCustomers As DataTable = New DataTable()
dtCustomers.Columns.AddRange(New DataColumn(3) {
New DataColumn("fullName", GetType(String)),
New DataColumn("email", GetType(String)),
New DataColumn("mailID", GetType(String)),
New DataColumn("DaysRemaining", GetType(Integer))})
GridView1.AllowPaging = False
GridView1.DataBind()
Dim checkboxArray As ArrayList = CType(ViewState("CheckBoxArray"), ArrayList)
Dim index As Integer = 1
For Each row As GridViewRow In GridView1.Rows
If checkboxArray.Contains(index) Then
Dim mailID As Integer = CInt(GridView1.DataKeys(row.RowIndex)("mailID"))
dtCustomers.Rows.Add(row.Cells(2).Text, (TryCast(row.FindControl("lnkEmail"), HyperLink)).Text, mailID)
End If
index += 1
Next
GridView1.AllowPaging = True
GridView1.DataBind()
Dim subject As String = "Report Completion Reminder"
Dim body As String = "Hello {0},<br /><br />There are {1} days left till end of this report submission period. <br /><br />Please remember to complete the form before deadline.<br /><br />Thanks."
Parallel.ForEach(dtCustomers.AsEnumerable(), Function(row)
'Update records after sending email
MyUpdate(row("mailID").ToString())
Return SendEmail(row("email").ToString(), subject, String.Format(body, row("FullName"), row("DaysRemaining")))
End Function)
LoadData()
'lblMsg.Text = "Reminder successfully sent"
End Sub
Private Function SendEmail(recipient As String, subject As String, body As String) As Boolean
Dim mm As New MailMessage()
mm.[To].Add(recipient)
Dim EmailSender As New MailAddress("no-reply@myemail.gov")
mm.From = EmailSender
mm.Subject = subject
mm.Body = body
mm.IsBodyHtml = True
Dim smtp As New SmtpClient()
Dim client As New SmtpClient("smtp.relay")
client.EnableSsl = False
client.Send(mm)
Return True
End Function
Private Sub MyUpdate(ByVal PK As Integer)
Dim strSQL As String = "UPDATE EmailNotifications SET sent='Yes' WHERE sent='No' and mailID = @ID"
Using conn As SqlConnection = New SqlConnection(constring)
Using cmdSQL As SqlCommand = New SqlCommand(strSQL, conn)
conn.Open()
cmdSQL.Parameters.Add("@ID", SqlDbType.Int).Value = PK
cmdSQL.ExecuteNonQuery()
End Using
End Using
End Sub
End Class
Thank you as always in advance
