In Office Automation one of the cornestones, at least for me, is templates. Using templates in Excel, in Word and in Outlook creates value as they provide us with tools to customize documents and doing so gives us structural solutions that can be applied inside corporates. Personally I find it to be excellent whenever I can use templates as part of solutions.
Here I will demonstrate how we can use a template for e-mails and how we can automate MS Outlook from Excel in a VSTO solution. The VSTO solution is created with Add-in Express 2009 For Microsoft Office and VSTO which is my #1 tool as it makes deployment so easy and smooth.
Part One: Creating the Outlook Template
Creating an e-mail template is very easy as we first create a new e-mail, add the wanted formatting and finally save it as a template.
The following screen shot shows the e-mail template that will be used:
After we have created the template we save it which the below screen shot also shows:
Part Two: Automate Outlook and use the template to create an e-mail
For our solution I have created a Ribbon solution where the Report button DZ-22A is available under the tab Add-ins which also the following screen shot shows:
When automation Outlook from Excel and other programs we need to pay attention to the fact that there can only be one running instance of Outlook. In order to avoid that we create a new window for Outlook whenever the code is executed we need to hook into the instance whenever Outlook is running. In other words, we need to check if Outlook is running or not. If running then we must hook into that instance and if not running we must create a new instance. The first part of the code solution checks the status and then take the appropriated action:
Imports Microsoft.Office.Tools.Ribbon Imports Excel = Microsoft.Office.Interop.Excel Imports Outlook = Microsoft.Office.Interop.Outlook Imports System.Diagnostics Imports System.Runtime.InteropServices Imports System.Windows.Forms Public Class MyRibbon Dim m_olApp As Outlook.Application = Nothing Dim m_olNamespace As Outlook.NameSpace = Nothing Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As _ Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _ Handles Button1.Click If Is_Outlook_Running() Then If Send_Mail_Attachment() Then MessageBox.Show("The e-mail has successfully been created and sent.", _ "Sent Message", MessageBoxButtons.OK) Else MessageBox.Show("An error occured while sending the e-mail.", "Send Error", _ MessageBoxButtons.OK) End If Else MessageBox.Show("Unable to establish a contact with MS Outlook!", _ "Fatal Error", MessageBoxButtons.OK) End If End Sub Private Function Is_Outlook_Running() As Boolean Dim Result As Boolean = Nothing Dim canQuit As Boolean = Nothing Try Dim Processes As Process() = Process.GetProcessesByName("OUTLOOK") Dim CountLength As Integer = Processes.Length If CountLength <> 0 Then 'Outlook is already running so we hook into the Outlook instance. m_olApp = CType(Marshal.GetActiveObject("Outlook.Application"), _ Outlook.Application) End If If Not m_olApp Is Nothing Then canQuit = False Else 'Outlook is not running so we instantiate a new session of Outlook. m_olApp = New Outlook.Application canQuit = True End If If Not m_olApp Is Nothing Then If canQuit Then m_olNamespace = m_olApp.GetNamespace("MAPI") m_olNamespace.Logon() Else ' Outlook is running so we just hook into existing session. m_olNamespace = m_olApp.Session End If End If Result = True Catch e As Exception m_olNamespace = Nothing m_olApp = Nothing Result = False End Try Return Result End Function
In the second part of the code solution we create a new e-mail based on the template, manipulate some properties of the e-mail and then attach the active workbook. Finally we send the e-mail as the below function also shows:
Private Function Send_Mail_Attachment() As Boolean Dim xlWorkbook As Excel.Workbook = _ Globals.ThisAddIn.Application.ActiveWorkbook Dim Result As Boolean = Nothing Dim olNewMail As Outlook.MailItem = Nothing Const Template As String = _ "C:\Users\Dennis\Documents\Automation\Weekly Report.oft" Try olNewMail = CType(m_olApp.CreateItemFromTemplate(Template), _ Outlook.MailItem) With olNewMail If Not xlWorkbook.Saved Then xlWorkbook.Save() .Attachments.Add(xlWorkbook.FullName.ToString) .FlagStatus = Outlook.OlFlagStatus.olFlagMarked .Importance = Outlook.OlImportance.olImportanceHigh .Save() 'If we want to display first and then manually send it. '.Display() .Send() End With Result = True Catch ex As Exception Result = False Finally If Not m_olNamespace Is Nothing Then m_olNamespace = Nothing If Not m_olApp Is Nothing Then m_olApp = Nothing End Try Return Result End Function End Class
Of course, when I come to think about; two other advantages with templates are that we don’t need to write code to format the documents and we can easily change them/replace them.