Recently I was tasked with emailing a report to several people and the report contained data that was generated from a SQL query. Since the the report needed to be sent daily I decided to automate it using an ASP.NET Winform with C#. Using a Winform to run a SQL query is straight forward enough but there was a unexpected hurdle with emailing the report to my coworkers due to network security. Fortunately one can automate communication with an Exchange mail server by leveraging Exchange Web Services (EWS) with Asp.net and C#.
What is Exchange Web Services (EWS)?
Exchange Web Services (EWS) is an exposed web service for communicating with Microsoft Exchange mail servers. If your office has an exchange server then you should be able to leverage the code below to send an SMTP message programmatically without having to open your mail client.
Using Exchange Web Services (EWS) and C#
One of the main benefits of using EWS is that it’s compatible with a wide range of programming languages and platforms. EWS supports languages like C#, Java, Python, and PHP, and works with platforms like .NET, Java, and Node.js. This makes it easy for developers to build applications that work seamlessly across multiple platforms.
The basic code to use to programmatically send an smtp message via Exchange Web Services (EWS) is below:
ExchangeService service = new Exchange Service();
service.Url = new uri("https://yourofficeserver.com/ews/Exchange.asmx");
service. UseDefaultCredentials = true; // person running winform
EmailMessage message= new EmailMessage(service);
message. Body = msgBody;
message. ToRecipients.Add(recipient);
message. Subject = msgSubject;
message. Save();
message. SendAndSaveCopy();
Send message to multiple users
In my situation I needed to send the message to multiple users and that group would periodically change so I added the email addresses to the config file for my WinForm separated by a comma. Before the message would be sent the WinForm would load those addresses as message recipients:
string msgRecip=ConfigurationManger.AppSettings[msgRecipients];
string [] recipientArr = msgRecip.Split(‘,’);
foreach (string recipient in recipientArr)
message. ToRecipients.Add(recipient);
Message body SQL query
The body of the message I needed to send was just the output of a SQL query so I used the snippet below to fill a dataset and return a string list:
private List<string> runMySQLQuery(string myQuery)
{
string connStr ConfigurationManager.ConnectionStrings ["mySQLConn"].ConnectionString;
List<string> myOutput = new List<string>();
using (SqlConnection connection = new SqlConnection(connStr))
SqlDataAdapter adapter new SqlDataAdapter();
adapter.SelectCommand
new SqlCommand(
myQuery, connection);
DataSet myDataSet new DataSet();
adapter.Fill(myDataSet);
foreach (DataTable table in myDataSet Tables)
foreach (DataRow row in table.Rows)
foreach (DataColumn column in table.Columns) myOutput.Add(row[column].ToString());
return myOutput;
}
To add the query output to the message I just update the message. Body block with:
message. Body = runMySQLQuery(mySQLQuery);
In Summary
Exchange Web Services (EWS) is a powerful set of APIs that allow developers to interact with Microsoft Exchange Server to access emails, calendars, contacts, tasks, and other data. EWS provides a platform-independent and language-independent interface that can be used to build robust and scalable applications. Thanks for reading!
