Headers


EASendMail SMTP Service can pick up data/recipients from database automatically and insert result back to database or file with the following customized headers.

email queue with database
Header Value
X-Data-Connection Specify the database connection string to connect the database.
X-Sql-Select Specify the SQL statement to pick records list from database.
X-Sql-OnSentSuccess Specify the SQL statement to insert the successful results to database.
X-Sql-OnSentError Specify the SQL statement to insert the failure results to database.
X-EAS-JobID Specify Job-ID for batch emails.
X-Sql-OnJobCompleted Specify the SQL statement to insert the Job results to database.
X-File-OnJobCompleted Specify the file full path to insert the Job results to file.
X-File-OnSentSuccess Specify the file full path to insert the successful results to file.
X-File-OnSentError Specify the file full path to insert the failure results to file.
X-Data-Connection
If you want to use X-Sql-Select, X-Sql-OnSentSuccess or X-Sql-OnSentError header, you MUST specify this header at first.
Data Connection String - MS SQL Server:
Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword;
Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Data Connection String - MySQL Server:
Driver={MySQL ODBC 3.51 Driver};Server=myServerAddress;Database=myDatabase;User=myUsername;Password=myPassword;Option=3;
Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Database=myDatabase;User=myUsername;Password=myPassword;Option=3;

Data Connection String - MS Access:
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;Uid=;Pwd=;"

Database Server Driver

In X-Data-Connection header, you should specify a database driver to connect database. You can open "Control Panel" -> "Administrative Tools" - > "ODBC Data Sources" - "Drivers" to check current installed database drivers.

odbc drivers

Common SQL Driver Download

If SQL Server is installed on a remote server, and you don't have SQL driver installed on local machine, then you need to download and install corresponding driver on local machine.

MS Access Database Driver Download

Microsoft Access Database Engine 2010 Redistributable

X-Sql-Select
You should specify a SQL statement in this header. EASendMail Service queries database with this SQL statement at first, then it will generate each email for each record.
X-Sql-OnSentSuccess
You should specify a SQL statement in this header. EASendMail Service executes this SQL statement when an email was sent to recipient SMTP server successfully.
X-Sql-OnSentError
You should specify a SQL statement in this header. EASendMail Service executes this SQL statement when an email couldn't be delivered to recipient SMTP server.
[C# - Example]

SmtpMail oMail = new SmtpMail("TryIt");
oMail.From = "test@adminsytem.com";
oMail.Subject = "test subject";

oMail.Headers.ReplaceHeader( "X-Data-Connection", "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\easendmail\\easendmail_demo.mdb;Uid=;Pwd=;" );

//EASendMail will select the fields by the following sql statement
//Before sending email, //then pick the recipient address from specified field.

oMail.Headers.ReplaceHeader( "X-Sql-Select", "SELECT id, name, address FROM Recipients" );

//pick "name" field as the recipient name and "address" field as
// the recipient address. // you can also use {$var_srecord:fieldname} to pick any field
// in X-Sql-Select statement and put it to subject, bodytext, then EASendMail
// will replace it automatially.
oMail.Headers.ReplaceHeader( "To", "\"{$var_srecord:name}\" <{$var_srecord:address}>" );
oMail.Headers.ReplaceHeader( "X-Rcpt-To", "{$var_srecord:address}");
//EASendMail service will execute the following sql statement on
// every email was sent successfully.

oMail.Headers.ReplaceHeader( "X-Sql-OnSentSuccess", "INSERT INTO sentlog ( server, email ) VALUES( '{$var_server}', '{$var_rcptaddr}' )" );

//EASendMail service will execute the following sql statement on
// every email was unable to sent.
oMail.Headers.ReplaceHeader( "X-Sql-OnSentError", "INSERT INTO errorlog( email, server, errorcode, errordescription ) VALUES( '{$var_rcptaddr}', '{$var_server}', '{$var_errcode}', '{$var_errdesc}' )" );

string s = "Hi {$var_srecord:name}, \r\nthis sample demonstrates how to send email in asp.net with EASendMail service.\r\n\r\n";
s += "To:{$var_srecord:address}\r\n\r\n";

//{$var_srecord:address} will be replaced by EASendMail automatically.

s += "recipient email address and name variable will be replaced by EASendMail service automatically\r\n\r\n";
s += "If no server address was specified, the email will be delivered to the recipient's server by the setting in ";
s += "EASendMail Service.\r\n\r\n";
s += "Your id in database is {$var_srecord:id}.\r\n\r\n";
s += "No matter how many recipients there are, EASendMail ";
s += "service will send the email in background.\r\n\r\n";

//{$var_srecord:id} {$var_srecord:address} {$var_srecord:name} in
// body text will //be replaced by EASendMail automatically.

oMail.TextBody = s;
SmtpClient oSmtp = new SmtpClient();
string err = "";

try
{
oSmtp.SendMailToQueue( null, oMail );
}
catch( System.Exception exp )
{
err = String.Format( "Exception: Common: {0}", exp.Message );
err += "Please make sure you installed EASendMail Service on the server!";
}
X-EAS-JobID(*optional)
You can specify a string as a Job-ID in this header, then you can manage the Job in EASendMail SMTP Service Manager->Queue Monitor->Jobs.
X-Sql-OnJobCompleted(*optional)
You can specify a SQL statement in this header. EASendMail Service executes this SQL statement when a Job is completed.
[C# - Example]

SmtpMail oMail = new SmtpMail("TryIt");
oMail.From = "test@adminsytem.com";
oMail.Subject = "test subject";

oMail.Headers.ReplaceHeader( "X-Data-Connection", "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\easendmail\\easendmail_demo.mdb;Uid=;Pwd=;" );

//EASendMail will select the fields by the following sql statement
//Before sending email, //then pick the recipient address from specified field.

oMail.Headers.ReplaceHeader( "X-Sql-Select", "SELECT id, name, address FROM Recipients" );

//pick "name" field as the recipient name and "address" field as
// the recipient address. // you can also use {$var_srecord:fieldname} to pick any field
// in X-Sql-Select statement and put it to subject, bodytext, then EASendMail
// will replace it automatially.
oMail.Headers.ReplaceHeader( "To", "\"{$var_srecord:name}\" <{$var_srecord:address}>" );
oMail.Headers.ReplaceHeader( "X-Rcpt-To", "{$var_srecord:address}");
//EASendMail service will execute the following sql statement on
// every email was sent successfully.

oMail.Headers.ReplaceHeader( "X-Sql-OnSentSuccess", "INSERT INTO sentlog ( server, email ) VALUES( '{$var_server}', '{$var_rcptaddr}' )" );

//EASendMail service will execute the following sql statement on
// every email was unable to sent.
oMail.Headers.ReplaceHeader( "X-Sql-OnSentError", "INSERT INTO errorlog( email, server, errorcode, errordescription ) VALUES( '{$var_rcptaddr}', '{$var_server}', '{$var_errcode}', '{$var_errdesc}' )" );

string s = "Hi {$var_srecord:name}, \r\nthis sample demonstrates how to send email in asp.net with EASendMail service.\r\n\r\n";
s += "To:{$var_srecord:address}\r\n\r\n";

//{$var_srecord:address} will be replaced by EASendMail automatically.

s += "recipient email address and name variable will be replaced by EASendMail service automatically\r\n\r\n";
s += "If no server address was specified, the email will be delivered to the recipient's server by the setting in ";
s += "EASendMail Service.\r\n\r\n";
s += "Your id in database is {$var_srecord:id}.\r\n\r\n";
s += "No matter how many recipients there are, EASendMail ";
s += "service will send the email in background.\r\n\r\n";

//{$var_srecord:id} {$var_srecord:address} {$var_srecord:name} in
// body text will //be replaced by EASendMail automatically.

oMail.TextBody = s;

// Define a Job, you can monitor the Job in EASendMail SMTP Service Manager->Queue Monitor->Jobs
oMail.Headers.ReplaceHeader( "X-EAS-JobID", "MyJob1");

// After all emails were sent in this job, the following SQL statement will be executed.
oMail.Headers.ReplaceHeader( "X-Sql-OnJobCompleted", "INSERT INTO joblog ( jobid ) VALUES( 'MyJob1')" );


SmtpClient oSmtp = new SmtpClient();
string err = "";

try
{
oSmtp.SendMailToQueue( null, oMail );
}
catch( System.Exception exp )
{
err = String.Format( "Exception: Common: {0}", exp.Message );
err += "Please make sure you installed EASendMail Service on the server!";
}
X-File-OnSentSuccess
You should specify a full file path in this header. EASendMail Service appends a result to the file when an email was sent to recipient SMTP server successfully.
X-File-OnSentError
You should specify a full file path in this header. EASendMail Service appends a result to the file when an email couldn't be delivered to recipient SMTP server.
X-File-OnJobCompleted
You should specify a full file path in this header. EASendMail Service appends a result to the file when a Job is completed.
[Visual Basic - Example]
Imports EASendMail

Public Sub SendMail( sFrom As String, _
sTo As String, _
sSubject As String )

Dim oMail As SmtpMail = New SmtpMail("TryIt")
Dim oSmtp As SmtpClient = New SmtpClient

Dim errStr As String = ""

Try
oMail.From = New MailAddress( sFrom )
'Please separate multiple addresses by comma(,)
oMail.To = New AddressCollection(sTo)

'To avoid too many email addresses appear in To header, using the
' following code only display the current recipient

oMail.Headers.ReplaceHeader( "To", """{$var_rcptname}"" <{$var_rcptaddr}>" )
oMail.Headers.ReplaceHeader( "X-Rcpt-To", new AddressCollection( txtTo.Text ).ToEncodedString( HeaderEncodingType.EncodingAuto, charset ))

Write successfull email to success.txt and write failed email to error.txt
oMail.Headers.ReplaceHeader( "X-File-OnSentSuccess", "d:\\success.txt" )
oMail.Headers.ReplaceHeader( "X-File-OnSentError", "d:\\error.txt" )

oMail.Subject = sSubject
oMail.TextBody = "Dear {$var_rcptname}, your email address is {$var_rcptaddr}"


oSmtp.SendMailToQueue( null, oMail)
MessageBox.Show( "The message was sent to EASendMail Service successfully!" )
Catch exp As System.Exception
errStr = String.Format("Exception: Common: {0}", exp.Message)
errStr += "Please make sure you installed EASendMail Service on the server!"
End Try
End Sub

Variables


"Replacing variables" in email body or header is the most important feature of EASendMail service.

Common Variables

{$var_rcpt}, {$var_rcptname} and {$var_rcptaddr} are the most common variables used in EASendMail services. For example: The component (ANSMTP or EASendMail) sends an email to "Tester <test@adminsystem.com>". The recipient name is "Tester" and address is <test@adminsystem.com>.

If the email body text is: "Dear {$var_rcptname}, your email is {$var_rcptaddr}, full address is {$var_rcpt}".
Then the recipient will recive the following body text:
Dear Tester, your email is test@adminsystem.com, full address is Tester <test@adminsystem.com>

This is very useful to newsletter application. The component can send one email to EASendMail service with hundreds of recipients, EASendMail service will replace those variables automatically and delivery the email to every recipient.

Database Variables

If the component sends the email by database select. The following variables can be used.

{$var_srecord:[fieldname]} fetches a value by the table field name. For example, the component specifies the "X-Sql-Select: SELECT userid, name, email from usertable". And recipient email address is stored in email field, then the component should set the To as "{$var_srecord:email}", EASendMail service will delivery the email to every recipient in usertable. The {$var_srecord:[fieldname]} can be used in body text and subject as well. For example: the body text can be: Dear {$var_srecord:name}, your id is {$var_srecord:userid}.

{$var_irecord:[fieldindex]} fetches a value by the table field sequence index. For example, the component specifies the "X-Sql-Select: SELECT userid, name, email from usertable". And recipient email address is stored in email field, then the component should set the To as "{$var_irecord:2}", EASendMail service will delivery the email to every recipient in usertable. The {$var_irecord:[fieldname]} can be used in body text and subject as well. For example: the body text can be: Dear {$var_irecord:1}, your id is {$var_irecord:0}.

Result Variables

If the component specifies X-Sql-OnSuccess or X-Sql-OnError, the following variables are aviable. {$var_errcode}: Error code returned by SMTP server or System. {$var_errdesc}: Error description {$var_server}: Current SMTP server address.

To learn more about usage of the variables, please refer to EASendMail samples.

Online Tutorials

Send Email in MS SQL Server - Tutorial
Send Email with Queue in ASP.NET + C#
Send Bulk Emails with Database Queue in ASP.NET + C#
Send Email with Queue in ASP.NET + VB
Send Bulk Emails with Database Queue in ASP.NET + VB
Send Email with Queue in ASP + VBScript
Send Bulk Emails with Database Queue in ASP.NET + VBScript

See Also

Disclaimer  Configuration  Headers and Variables  SMTP Instances  Journal and troubleshooting