Click here to Skip to main content
Click here to Skip to main content

Create a Mailing List with Auto Opt Out

, 26 Jul 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Developed a Mailing List collection with Opt Out

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:

Overview.PNG

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.

DatabaseImg.PNG

I then created a LINQ to SQL class file and using Visual Studio dragged and dropped the SQL table into the class.

Linq_to_Sql_Class.PNG

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>>

merge_step_1.PNG

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

merge_step_3.PNG

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

merge_step_4.PNG

  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

  • 25th June, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

C Rotell
Program Manager
United States United States
I have been developing web pages as a side hobby for 15+ years. Only formal training has been when I was on a computer science degree program in 2000; otherwise, I am self taught and trained.
 
I hope to provide the community with practical applications rather then theory.

Comments and Discussions

 
QuestionPossible without LINQ? Pinmembervbkid201031-Mar-11 15:11 
AnswerRe: Possible without LINQ? Pinmemberrollo10023-Jun-11 9:03 
QuestionCompiler Error Message Pinmemberlivingsingl30-Aug-10 8:29 
AnswerRe: Compiler Error Message Pinmemberrollo100230-Aug-10 22:51 
AnswerRe: Compiler Error Message Pinmemberrollo100210-Jan-11 12:09 
GeneralMailMerge PinmemberNorbert Bietsch2-Jul-10 3:49 
GeneralProblem finding sources..... PinmemberlvTH29-Jun-10 13:41 
GeneralRe: Problem finding sources..... [modified] Pinmemberrollo10021-Jul-10 9:34 
GeneralRe: Problem finding sources..... Pinmemberrollo10021-Jul-10 19:24 
GeneralRe: Problem finding sources..... PinmemberlvTH2-Jul-10 7:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 26 Jul 2010
Article Copyright 2010 by C Rotell
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid