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.
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.
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.
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
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=;"