Problem

Reminder emails were needed to be sent out to remind sales staff to follow up on projects.

Solution

I developed a simple command line executable that takes 1 parameter which is an XML file.  The contents of the XML file would look something like:

<?xml version="1.0" encoding="utf-8" ?>
<Settings>
	<ErrorEmail>
		<Email>email@gmail.com</Email>
	</ErrorEmail>
	<SummaryEmail>
		<Email>email@gmail.com</Email>
	</SummaryEmail>
	<Query>
		SELECT * FROM Projects
		WHERE Cancelled = 0 And Completed = 0 AND BookDate IS NULL
		AND FollowUpBy <= getdate()
	</Query>
	<SQLConnectionString>Server=SQLServer;Database=MainDatabase;Persist Security Info=True;Integrated Security=true</SQLConnectionString>
	<TemplateEmailFileName>EmailTemplate.txt</TemplateEmailFileName>
	<EmailSubject>Alert Email For Project#<:ProjectID:></EmailSubject>
	<EmailDomain>mycompany.com</EmailDomain>
	<ToAddressDatabaseField>Salesperson</ToAddressDatabaseField>
	<ToAddressFieldIsEmail>0</ToAddressFieldIsEmail>
	<SMTPSettings>
		<SMTPServer>smtp.gmail.com</SMTPServer>
		<EnableSSL>1</EnableSSL>
		<RequiresAuthentication>1</RequiresAuthentication>
		<Username>email@gmail.com</Username>
		<Password>*******</Password>
		<Port>587</Port>
		<FromAddress>email@gmail.com</FromAddress>
	</SMTPSettings>
	<LogFileDirectory>Daily</LogFileDirectory>
</Settings>

The command line executable would then run the query (<Query>) against the supplied database in the XML file.  For each row returned, the program will send out an email to the user.  Which user?  What email?  The recipients email address would need to be one of the fields returned from the query and noted in the XML file in the element <ToAddressDatabaseField>.  The template for the email body is supplied by the <TemplateEmailFileName> element.   To make the email custom, it is possible to insert fields returned from the database into the email template.  For example, lets say the query returned a field called “FirstName”.  In the email template text file, you could have:

Hello <:FirstName:>,

When the program ran, it would swap “<:FirstName:>” with the contents from the database.  This is similar to a mail merge for emails.  Since all of the values are configurable in the XML file, the usage possibilities are unlimited!  Let say you needed to run this once a week, no problem.  Set up a Windows Scheduled Task to run the executable at the specified interval.