Introduction
I had to create a simple mailing list with the ability to opt out for a customer and thought I would share this experience. The idea was to collect users names via a webform, then store it into a database for use later. The customer was using Microsoft Word to create the newsletter and Mail Merge would be used to send out the email to the users. Additionally, due to anti-spam regulations, the ability to opt out was a key component to solution. We will walk through the interaction and flow, then quickly review field needed in the database, creating the Linq to SQL class, the working code and then finally the mail merge.
There are three interactions here:
- User registers
- Emails are sent to the user
- User Opts Out via hyperlink
Here is an example of the interaction:
Background
The technologies used for this exercise are:
- SQL Server
- Visual Studio 2008 for code development
- LINQ to SQL
Using the Code
I started off by first creating an SQL DB or if you need to a new table in an existing SQL Database.
I then created a LINQ to SQL class file and using Visual Studio dragged and dropped the SQL table into the class.
Joinmailinglist.aspx File
This is a basic form where you enter information and things are validated to make sure the proper information is entered. This code can be entered pretty much anywhere.
<form id="form1" runat="server">
<div>
-->
<asp:Table ID="Table1" runat="server">
<asp:TableRow><asp:TableCell>First Name: <asp:TextBox ID="txtFirstName"
runat="server" Width="60" /> <asp:RequiredFieldValidator ID="RequiredFieldValidator1"
ErrorMessage="Enter First Name" Text="*" ControlToValidate="txtFirstName"
runat="server" /> </asp:TableCell>
<asp:TableCell>Last Name: <asp:TextBox ID="txtLastName" runat="server" Width="60" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="txtLastName" ErrorMessage="Enter Last Name" Text="*" />
</asp:TableCell> </asp:TableRow>
<asp:TableRow><asp:TableCell>Email: <asp:TextBox ID="txtEmail"
runat="server" Width="80" /><asp:RequiredFieldValidator ID="emailRequired"
runat="server" ControlToValidate="txtEmail" ErrorMessage="Email Needs Information"
Text="*"/> <asp:RegularExpressionValidator ID="emailexpression"
runat="server" ControlToValidate="txtEmail" ValidationExpression=".*@.*\..*"
ErrorMessage="Invalide Email Address" Text="*" /></asp:TableCell></asp:TableRow>
<asp:TableRow><asp:TableCell>
<asp:ValidationSummary ID="ValidationSummary1" runat="server" ShowSummary="true"
ShowMessageBox="true" runat="server" />
</asp:TableCell></asp:TableRow>
<asp:TableRow>
<asp:TableCell><asp:Button ID="btnSubmit" runat="server" OnClick="addMember"
CausesValidation="true" Height="30" Width="100" Text="Add Me" />
</asp:TableCell></asp:TableRow>
<asp:TableRow><asp:TableCell><asp:Label ID="lblDuplicate" runat="server" Text="">
</asp:Label></asp:TableCell></asp:TableRow>
</asp:Table>
</div>
</form>
joinmailinglist.aspx.cs File
This is where all the work happens to capture the correct information into the SQL Server. In my if
… else
… statement, I had originally tried to compare that the specific value is equal; however, I found that just counting the number of variable returned or in my case the number that was not returned I successfully solve my issue. A little view from outside the box solved this problem very efficiently and without adding any more code or converting strings or creating arrays. The important part here is to make sure you add using System.Linq
.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class joinmailinglist : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}protected void addMember(object sender, EventArgs e)
{
mailinglistClassDataContext Class = new mailinglistClassDataContext();
mailinglistMember member = new mailinglistMember();
member.memberID = new Guid();
member.fname = txtFirstName.Text;
member.lname = txtLastName.Text;
member.email = txtEmail.Text;
member.removeurl = "http://removeuser.aspx?code=" + member.memberID.ToString();
var duplicatecheck = from emails in Class.mailinglistMembers
where emails.email.Contains(txtEmail.Text)
select emails;
if (duplicatecheck.Count() == 0)
{
Class.mailinglistMembers.InsertOnSubmit(member);
Class.SubmitChanges();
Response.Redirect(“confirmation.aspx”);
}
else
{
lblDuplicate.Text = "Hey you have already entered your information.";
}
}
}
Removeme.aspx
This is the page that the user lands on that tells them their information has been removed.
<form id="form1" runat="server">
<div><h1>Request for Removal</h1>
</div>
<div><asp:Label runat="server" id="lblRemoved" Text="" /></div>
<div> If you have done this in error <a href="joinmailinglist.aspx">Click Here</a>!</div>
<div><asp:Label runat="server" ID="lblNotfound" Text=""></asp:Label></div>
</form>
Removeme.aspx.cs
In this code, we are going to go back to the database and remove the user by using the GUID that was provided in the email / opt out hyperlink.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;public partial class removeme : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["code"] != null)
{
mailinglistClassDataContext member = new mailinglistClassDataContext();
string id = Request.QueryString["code"];
var removeme = from maillist in member.mailinglistMembers
where maillist.memberID.ToString() == id
select maillist;
if (removeme.Count() > 0)
{
member.mailinglistMembers.DeleteOnSubmit(removeme.First());
member.SubmitChanges();
lblRemoved.Text = "You have been removed from our mailing list.";
}else
{
lblNotfound.Text = "This user has already been removed or does not exist";
}
}else
{
Response.Redirect("home.aspx");
}
}
}
The MailMerge
This was a point of interest that I had trouble with solving. It seems that you cannot add a datafield to a hyperlink. So I was unable to preformat the text of a URL and then just input the unique id or guid in my case. That is the reason I had to create the removeurl
field in the table. This way, I could bring the entire URL across without problems and insert it into the mail merge and then hyperlink it. Hyperlinking a datafield in Mail Merge is not so straightforward so I have created these steps to help you through the process. The version of Word I am using is 2010, but this should work with earlier versions.
- Start your mail merge as normal.
- Select your database as normal.
- Create your form letter or email.
- Select where you want to place the opt out link.
- Insert the field in our case
<<removeurl>>
- Select this field.
- Right click and select update field.
- Then you are going to select hyperlink from the list of fields.
- Click field codes.
- Click ok.
- When you come back, it will say it’s in Error! Hyperlink reference not valid.
- Select all up until the period.
- Change the text.
- You have now created a merged hyperlink.
- If you hold CTRL and click on the link, it will fire.
- If your webpage is on a working server, it will fire fine.
- If you’re still in development and working on your local host, you will have to go back to the joinmailinglist.aspx.cs file and change the
removeurl
text to reflect where you’re working, i.e. http://localhost:#####/folder/removeme.aspx?code=
Points of Interest
There were two points of interest that I had to work through:
- How to detect if an email address was already in the system to eliminate duplicates
- How to place a specific hyperlink to opt out in the message
- Because I could not merge the id into a hyperlink in mail merge, I had to create a field in the database to capture all of the information so that I could translate it to a hyperlink in the mail merge.
History
- 25th June, 2010: Initial post