Introduction
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.
Kind regards,
Dennis
VSTO & .NET & Excel 