Create a Mailing List with Auto Opt Out

• Download source - 3.07 KB

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:

1. User registers
2. Emails are sent to the user
3. User Opts Out via hyperlink

Here is an example of the interaction:

Background

The technologies used for this exercise are:

1. SQL Server
2. Visual Studio 2008 for code development
3. 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>
<!-- This is the code for the form. There is a Text Box to collect the first name, 
last name and email address. All fields are required and I am validating that the 
email address is a valid format. -->
<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)
{
// here you are defining the classes for the database and the linq
mailinglistClassDataContext Class = new mailinglistClassDataContext();
mailinglistMember member = new mailinglistMember();
// Now we are going to add the data to the member
// Here we are going to let the system define a GUID for the unique user ID
member.memberID = new Guid();

// Here we are going to capture the user inputs and we are going to set 
// these to lower case especially the email so that we can do a proper comparison later.

member.fname = txtFirstName.Text;
member.lname = txtLastName.Text;
member.email = txtEmail.Text;

// Here we are going to create the URL so we can later remove the user 
// if they decide to opt out. 

member.removeurl = "http://removeuser.aspx?code=" + member.memberID.ToString();

// Here we are going to use a LINQ query to search the class of mailinglistmembers 
// for any emails that contain equal values of the text field and select it.

var duplicatecheck = from emails in Class.mailinglistMembers

                     where emails.email.Contains(txtEmail.Text)

                     select emails;

// Here we are going to check that the count of duplicate is equal to zero. 
// If so then we are going to insert the member information into the class 
// and then submit the changes to the database.

if (duplicatecheck.Count() == 0)
{
Class.mailinglistMembers.InsertOnSubmit(member);
Class.SubmitChanges();
// If you want to add a confirmation page it’s as easy as this:

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)
{
// here your are going to make sure that the url has a value for code

if (Request.QueryString["code"] != null)
{
// here we are going to define the class
mailinglistClassDataContext member = new mailinglistClassDataContext();

// here we are going to take the code provided in the URL and convert it to a string

string id = Request.QueryString["code"];

// here is our LINQ statement that is going to find the GUID of the user 
// which was provided in the email

var removeme = from maillist in member.mailinglistMembers

                where maillist.memberID.ToString() == id

                select maillist;

//Checking to see if a value was returned

if (removeme.Count() > 0)
{
member.mailinglistMembers.DeleteOnSubmit(removeme.First());
member.SubmitChanges();
lblRemoved.Text = "You have been removed from our mailing list.";
}else
{
// if no value is returned then we let them know a user was already removed.

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.

1. Start your mail merge as normal.
2. Select your database as normal.
3. Create your form letter or email.
4. Select where you want to place the opt out link.
5. Insert the field in our case <<removeurl>>

1. Select this field.
2. Right click and select update field.
3. Then you are going to select hyperlink from the list of fields.

1. Click field codes.
2. Click ok.
3. When you come back, it will say it’s in Error! Hyperlink reference not valid.

1. Select all up until the period.
2. Change the text.
3. You have now created a merged hyperlink.
4. If you hold CTRL and click on the link, it will fire.
5. If your webpage is on a working server, it will fire fine.
6. 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:

1. How to detect if an email address was already in the system to eliminate duplicates
2. How to place a specific hyperlink to opt out in the message
   1. 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

• 25^th June, 2010: Initial post