Robert
Green
MCW Technologies
Download the code for this
tutorial on Code Gallery
Introduction
Contoso Auto Sales is a fictitious automobile dealer specializing
in high-end automobiles. Contoso has computer systems to talk to suppliers, to
generate an invoice when a customer purchases an automobile, to bill for
service visits and to pay employees. It wants a system to manage the day-to-day
interaction with customers and improve its level of pre-sales customer service.
Contoso Auto would like a solution based on Office 2007 that
addresses the following scenarios:
• The
sales manager needs to contact customers who have requested an appointment by
calling Contoso or visiting the Web site. She wants to send a mail
acknowledging the appointment request and then schedule appointments using
Outlook.
• Sales
consultants want to use Word to create and print price quotes for automobiles,
reflecting available options selected by a customer.
• The
financing manager wants to use Excel to generate financing information for
customers with pending quotes. He wants the ability to generate this
information interactively, but also wants it created automatically when the
sales consultant generates a quote.
• The
sales manager wants to use Excel to run reports showing quotes by manufacturer,
model, customer and employee. She wants to view reports offline on her laptop
and wants the reports automatically updated with up to date information.
In this tutorial series, you will use Visual Studio 2008 to
create an Office Business Application that address each of the scenarios listed
above, using Outlook 2007, Word 2007 and Excel 2007. The tutorials contain both
Visual Basic and C# code.
Exercise 1: Create the Outlook Add-in Project
Potential customers can request an appointment by visiting the
Contoso Web site or by calling Contoso directly. The onus is now on Contoso to
contact that customer and schedule a day and time to meet with a sales
consultant. Not responding means lost opportunities for sales.
Contoso wants to use Outlook to respond to and schedule customer
requests for appointments. The sales manager wants to respond via email to let
the customer know she has received the appointment request and will contact the
customer. She will then call the customer to arrange for an appointment with a
sales consultant.
In this tutorial, you will create an Outlook 2007 customer
appointment management solution. You will add the following capabilities to
Outlook:
• The
ability to send an email to all of the customers who have requested
appointments.
• The
automatic creation of follow-up tasks for each customer who requested an
appointment.
• The
ability to auto-fill meeting requests with customer and sales rep information
and send the customer a confirmation email.
To get started, in Visual Studio 2008 select File | New | Project to display the New
Project dialog box. In the list of project types, expand the Office node.
Select Version2007, displaying the
list of templates shown in Figure 1.
.jpg)
Figure 1. Visual Studio 2008 provides these Office
2007 templates.
In the Templates pane, select Outlook 2007 Add-in. Name your project CustomerAppointments and select an appropriate folder for the
project. Click OK to create the
project.
Your add-in will work with data that resides in the
ContosoAuto SQL Server database. You first need to create the database. Open
SQL Server Management Studio and connect to SQL Server (or SQL Server Express).
Select File | Open | File to display
the Open File dialog box. Navigate to the folder where you downloaded this
tutorial’s sample project. Navigate to the Data folder. Select InstallContosoAuto.sql and click Open. Click Execute to run the script.
If you do not have SQL Server Management Studio, open a
command prompt. To do this, locate the Command Prompt item in the Start menu.
Right-click on it and select Run as
administrator. Enter the following to install the database on your local
sqlexpress instance:
sqlcmd -S .\sqlexpress -i "C:\SampleLocation\Data\InstallContosoAuto.sql"
Replace C:\SampleLocation with the path to this tutorial’s
sample project. The command above assumes you are using SQL Server Express. If
you are using SQL Server, replace .\sqlexpress with computer name\instance name.
For more information on sqlcmd, please see the MSDN Library. Close
the command prompt.
Return to Visual Studio. In the Solution Explorer,
right-click on Data Connections and select Add
Connection to display the Add Connection dialog box. If the Data source
does not display Microsoft SQL Server (SqlClient), click Change. This displays the Change Data Source dialog box. Select Microsoft SQL Server (SqlClient) and
click OK.
In the Add Connection dialog box, enter your computer name
(if you are using SQL Server) or .\SQLEXPRESS
(if you are using SQL Server Express) in the Server name text box. Select ContosoAuto from the database drop-down
list. Click OK to close the Add
Connection dialog box.
In the Server Explorer, expand the ContosoAuto node.
Expand the Tables node to see the tables in the database (see Figure 2).
.jpg)
Figure 2. The ContosoAuto database contains these tables.
To see the data in the Manufacturers table, right-click on
Manufacturers and select Show Table Data.
You should see the data shown in Figure 3.
Use the same technique to view the sample data in the Models table (see Figure 4),
AvailablePackages (see Figure 5)
and Employees (see Figure 6)
tables.
The Manufacturers table contains the automobile makers.
The Models table contains the automobiles. The AvailablePackages table contains
options available for each automobile. The Employees table contains Contoso
Auto’s sales representatives and sales manager.
.jpg)
Figure 3. The Manufacturers table contains this data.
.jpg)
Figure 4. The Models table contains this data.
.jpg)
Figure 5. The AvailablePackages table contains this data.
.jpg)
Figure 6. The Employees table contains this data.
The Outlook solution you are building in this tutorial
also needs a connection to the ContosoAuto database. Select Project | CustomerAppointments Properties. Select the Settings tab. To add a new setting, enter ContosoAutoConnectionString in the Name column. Select (Connection string) from the Type
drop-down list.
Click the ellipsis in the Value text box. This displays
the Connection Properties dialog box. Create a connection to the ContosoAuto
database. Click OK to close the Connection
Properties dialog box. Close the Project Designer, saving your changes.
You will use a DataSet to communicate with the ContosoAuto
database. Rather than create the DataSet from scratch, you will use an existing
version of it. Select Project | Add Existing Item to display the Open
File dialog box. Navigate to the folder where you downloaded this tutorial’s
sample project. Navigate to the DataSets\CustomerAppointments folder. Then
navigate to the VB or CS folder depending on the language you are using. Select
ContosoAutoDataSet.xsd and click OK to add the DataSet to the project.
In the Solution Explorer, double-click ContosoAutoDataSet.xsd to open the DataSet Designer (see Figure 7).
Take a few minutes to familiarize yourself with the tables and TableAdapters.
.jpg)
Figure 7. The add-in uses this DataSet to work with the
data in the ContosoAuto database.
Close the DataSet Designer. Save your changes. Select Build | Build Solution and verify that
the project compiles correctly.
Exercise 2: Build a Custom Outlook Menu
In this exercise, you will add a Customers menu to Outlook. The
sales manager will use this menu to respond to customer appointment requests
and to schedule appointments.
Add the Menu to Outlook
The ThisAddIn class currently has two methods.
ThisAddIn_Startup runs when the add-in loads, shortly after Outlook starts.
ThisAddIn_Shutdown runs when the add-in unloads, shortly before Outlook stops.
Your first thought might be to put code like the following to add
the Customers menu in the Startup event handler.
' Visual Basic
' Get a reference to the Outlook main menu bar
mainMenuBar = thisExplorer.CommandBars.ActiveMenuBar
' Locate the Help menu
Try
helpMenuIndex = mainMenuBar.Controls("Help").Index
Catch
helpMenuIndex = mainMenuBar.Controls.Count
End Try
' Create a Customers menu to the left of the Help menu
mainMenu = CType(mainMenuBar.Controls.Add( _
Office.MsoControlType.msoControlPopup, Type.Missing, _
Type.Missing, helpMenuIndex, True), Office.CommandBarPopup)
mainMenu.Caption = "Customers"
mainMenu.Visible = True
// C#
// Get a reference to the Outlook main menu bar
mainMenuBar = thisExplorer.CommandBars.ActiveMenuBar;
// Locate the Help menu
try
{
helpMenuIndex = mainMenuBar.Controls["Help"].Index;
}
catch
{
helpMenuIndex = mainMenuBar.Controls.Count;
}
// Create a Customers menu to the left of the Help menu
mainMenu = ((Office.CommandBarPopup)
(mainMenuBar.Controls.Add(Office.MsoControlType.msoControlPopup,
Type.Missing, Type.Missing, helpMenuIndex, true)));
mainMenu.Caption = "Customers";
mainMenu.Visible = true;
This code will add a Customers menu. However, it will only appear
in one window. Outlook will add the menu to whatever window it opened first
when it started. This will typically be the Inbox, but the user can change
Outlook’s startup folder in the Options dialog. If you then open another
window, such as the Calendar or Contacts, the Customers menu will not appear in
that window.
Contrast this with Word or Excel, where adding a menu item adds
that menu to the application, not to whatever document is currently open. The CommandBars
collection object represents the menu bar and toolbars in Office applications. CommandBars
is a property of the Application object in Word and Excel and therefore, adding
a menu item occurs at the application level.
In Outlook, however, CommandBars is a property of the Explorer
object, not the application. An Explorer is a window that displays the contents
of Outlook folders. The Inbox is a folder of mail items and the window that
displays the Inbox is an Explorer. The Calendar is a folder of appointment
items and the window that displays meetings and appointments is an Explorer.
Similarly, the Contacts and Tasks windows are Explorers.
Adding a menu item in Outlook adds that item only to one Explorer,
not to all Explorers. In the tutorial’s application, the Customers menu should
appear in all Explorers, not just the Inbox. The sales manager should not have
to switch over to the Inbox just to have Outlook automatically send emails.
When Outlook starts, it opens all Explorer windows that were open when you
exited. You want to add the Customers menu to all of those windows on startup.
You also want to add it to any new Explorer window.
The most reliable way to attach a menu to more than one Explorer
is to create a class that represents an Explorer window. You then create a new
instance of this class each time an Explorer window opens. You associate the
menu with the class, rather than the actual Explorer window.
Select Project | Add Class
to display the Add New Item dialog box. Name the class ExplorerWindow and click Add
to add the class.
If you are using
C #, add the following at the top of the file.
// C#
using Office = Microsoft.Office.Core;
using Outlook = Microsoft.Office.Interop.Outlook;
Add the following declarations to the ExplorerWindow
class.
' Visual Basic
Private mainMenuBar As Office.CommandBar = Nothing
Private helpMenuIndex As Integer = 0
Private customersMenuIndex As Integer = 0
Private mainMenu As Office.CommandBarPopup = Nothing
Private WithEvents respondMenuItem As _
Office.CommandBarButton = Nothing
Private WithEvents scheduleMenuItem As _
Office.CommandBarButton = Nothing
Private thisExplorer As Outlook.Explorer = Nothing
// C#
private Office.CommandBar mainMenuBar = null;
private int helpMenuIndex = 0;
private int customersMenuIndex = 0;
private Office.CommandBarPopup mainMenu = null;
private Office.CommandBarButton respondMenuItem = null;
private Office.CommandBarButton scheduleMenuItem = null;
private Outlook.Explorer thisExplorer = null;
Add the following method to add the Customers menu to an
Explorer window:
' Visual Basic
Private Sub AddCustomersMenu()
' Get a reference to the Outlook main menu bar
mainMenuBar = thisExplorer.CommandBars.ActiveMenuBar
' Check if there is a Customers menu
Try
customersMenuIndex = mainMenuBar.Controls("Customers").Index
Catch
customersMenuIndex = 0
End Try
If customersMenuIndex = 0 Then ' Add the Customers menu
' Locate the Help menu
Try
helpMenuIndex = mainMenuBar.Controls("Help").Index
Catch
helpMenuIndex = mainMenuBar.Controls.Count
End Try
' Create a Customers menu to the left of the Help menu
mainMenu = CType(mainMenuBar.Controls.Add( _
Office.MsoControlType.msoControlPopup, _
Before:=helpMenuIndex, Temporary:=True), _
Office.CommandBarPopup)
mainMenu.Caption = "Customers"
mainMenu.Visible = True
' Create menu items in the Customers menu
respondMenuItem = CType(mainMenu.Controls.Add( _
Office.MsoControlType.msoControlButton, _
Before:=1, Temporary:=True), Office.CommandBarButton)
respondMenuItem.Style = _
Microsoft.Office.Core.MsoButtonStyle.msoButtonCaption
respondMenuItem.Caption = "Respond to appt requests"
scheduleMenuItem = CType(mainMenu.Controls.Add( _
Office.MsoControlType.msoControlButton, _
Before:=2, Temporary:=True), Office.CommandBarButton)
scheduleMenuItem.Style = _
Microsoft.Office.Core.MsoButtonStyle.msoButtonCaption
scheduleMenuItem.Caption = "Schedule appointments"
End If
End Sub
// C#
private void AddCustomersMenu()
{
// Get a reference to the Outlook main menu bar
mainMenuBar = thisExplorer.CommandBars.ActiveMenuBar;
// Check if there is a Customers menu
try
{
customersMenuIndex = mainMenuBar.Controls["Customers"].Index;
}
catch
{
customersMenuIndex = 0;
}
if (customersMenuIndex == 0) // Add the Customers menu
{
// Locate the Help menu
try
{
helpMenuIndex = mainMenuBar.Controls["Help"].Index;
}
catch
{
helpMenuIndex = mainMenuBar.Controls.Count;
}
// Create a Customers menu to the left of the Help menu
mainMenu = ((Office.CommandBarPopup)
(mainMenuBar.Controls.Add(
Office.MsoControlType.msoControlPopup,
Type.Missing, Type.Missing, helpMenuIndex, true)));
mainMenu.Caption = "Customers";
mainMenu.Visible = true;
// Create menu items in the Customers menu
respondMenuItem = ((Office.CommandBarButton)
(mainMenu.Controls.Add(
Office.MsoControlType.msoControlButton,
Type.Missing, Type.Missing, 1, 1)));
respondMenuItem.Style =
Microsoft.Office.Core.MsoButtonStyle.msoButtonCaption;
respondMenuItem.Caption = "Respond to appt requests";
respondMenuItem.Click += new
Office._CommandBarButtonEvents_ClickEventHandler(
respondMenu_Click);
scheduleMenuItem = ((Office.CommandBarButton)
(mainMenu.Controls.Add(
Office.MsoControlType.msoControlButton,
Type.Missing, Type.Missing, 2, 1)));
scheduleMenuItem.Style =
Microsoft.Office.Core.MsoButtonStyle.msoButtonCaption;
scheduleMenuItem.Caption = "Schedule appointments";
scheduleMenuItem.Click += new
Office._CommandBarButtonEvents_ClickEventHandler(
scheduleMenu_Click);
}
}
Add the following event handlers for the two menu items. You
will add code to these methods later.
' Visual Basic
Private Sub respondMenu_Click(ByVal ctrl As _
Office.CommandBarButton, ByRef CancelDefault As Boolean) _
Handles respondMenuItem.Click
End Sub
Private Sub scheduleMenu_Click(ByVal ctrl As _
Office.CommandBarButton, ByRef CancelDefault As Boolean) _
Handles scheduleMenuItem.Click
End Sub
// C#
private void respondMenu_Click(Office.CommandBarButton ctrl,
ref bool CancelDefault)
{
}
private void scheduleMenu_Click(Office.CommandBarButton ctrl,
ref bool CancelDefault)
{
}
Return to the ThisAddIn file. Add the following declarations
to the ThisAddIn class.
' Visual Basic
Private WithEvents explorers As Outlook.Explorers = Nothing
Friend explorerWindowList As List(Of ExplorerWindow) = Nothing
// C#
private Outlook.Explorers explorers = null;
internal List<ExplorerWindow> explorerWindowList = null;
Add the following code to the ThisAddIn_Startup method:
' Visual Basic
explorerWindowList = New List(Of ExplorerWindow)
explorerWindowList.Add(New ExplorerWindow( _
Me.Application.ActiveExplorer))
explorers = Me.Application.Explorers
// C#
explorerWindowList = new List<ExplorerWindow>();
explorerWindowList.Add(new ExplorerWindow(
this.Application.ActiveExplorer()));
explorers=this.Application.Explorers;
explorers.NewExplorer += new
Outlook.ExplorersEvents_NewExplorerEventHandler(
Explorers_NewExplorer);
Note that your code will not compile at this point. You will
add the ExplorerWindow constructor in the next step.
This code creates a new generic list of instances of the
ExplorerWindow class. The code adds the current Explorer to that list. When
Outlook starts, it opens all previously opened Explorers. However, only the
first Explorer is open when the Startup event occurs. Outlook opens the rest
later. You can see this at startup. The first Explorer appears and then there
is a delay until the other Explorer windows open.
Each time Outlook or the user opens an Explorer, the
NewExplorer event occurs. Add the following method to handle this event and add
the new Explorer window to the list.
' Visual Basic
Private Sub Explorers_NewExplorer(ByVal _explorerWindow As _
Outlook.Explorer) Handles explorers.NewExplorer
explorerWindowList.Add(New ExplorerWindow(_explorerWindow))
End Sub
// C#
private void Explorers_NewExplorer(Outlook.Explorer explorerWindow)
{
explorerWindowList.Add(new ExplorerWindow(explorerWindow));
}
Return to the ExplorerWindow code file. Add the following
constructor:
' Visual Basic
Public Sub New(ByVal _explorer As Outlook.Explorer)
thisExplorer = _explorer
AddCustomersMenu()
Dim explorerEvents As Outlook.ExplorerEvents_10_Event = _
CType(_explorer, Outlook.ExplorerEvents_10_Event)
AddHandler explorerEvents.Close, AddressOf Explorers_Close
End Sub
// C#
public ExplorerWindow(Outlook.Explorer explorer)
{
thisExplorer = explorer;
AddCustomersMenu();
Outlook.ExplorerEvents_10_Event explorerEvents =
((Outlook.ExplorerEvents_10_Event)(thisExplorer));
explorerEvents.Close += new
Outlook.ExplorerEvents_10_CloseEventHandler(Explorers_Close);
}
This code runs each time Outlook or the user opens an
Explorer window. The code adds the Customers menu to the Explorer and handles
the Close event for that Explorer.
Note that Close is an event of Explorers. However, it is also
a method. Visual Studio does not support using the same name for both an event
and a method, so to refer to the Close event in your code you can cast an Explorer
to the ExplorerEvents_10_Event interface that contains the Close event.
Next, add the following code to handle the Close event. When
the user closes an Explorer window, this code removes it from the list.
' Visual Basic
Private Sub Explorers_Close()
Globals.ThisAddIn.explorerWindowList.Remove(Me)
End Sub
// C#
private void Explorers_Close()
{
Globals.ThisAddIn.explorerWindowList.Remove(this);
}
This method is in the ExplorerWindow class but the
explorerWindowList is in the ThisAddIn class. Ordinarily you would create an
instance of a class to access its members. However, you cannot create a new
instance of ThisAddIn. The Visual Studio Tools for Office runtime creates the
instance when Outlook starts. You can use Globals.ThisAddIn to refer to the
existing instance and access the explorerWindowList field.
Save your changes. Select Build | Build Solution and verify that the project compiles
correctly.
Press F5 to run the application. You
should see Outlook start. All open Explorers should include the Customers menu
(see Figure 8).
.jpg)
Figure 8. The add-in adds the Customers menu to each open
Explorer window.
Open a new explorer, such as Contacts or Tasks. You should
see the Customers menu attached to that explorer. Exit Outlook and return to
Visual Studio.
Exercise 3: Respond to Customer Requests for Appointments
Customers can request an appointment either by visiting the
Contoso Web site or by phoning and talking to the receptionist. The Customers
table contains customers and the AppointmentRequests table (see Figure 9)
contains appointment requests.
.jpg)
Figure 9. The AppointmentRequests table contains customer
requests for appointments.
The ResponseSent column identifies
whether the customer has received an acknowledgement of the appointment
request. When the sales manager selects Customers
| Respond to appt requests, the code in the RespondToApptRequests method
runs. You will write code to take the following actions:
• Retrieve
all rows from AppointmentRequests where ResponseSent is not true
• Retrieve
customer information such as name, phone numbers and email address
• Send
an email to the customer acknowledging the request for an appointment
• Update
the ResponseSent column to true
• Add
a follow-up task for the sales manager
Send Mail to Customers Who Have Requested Appointments
You will next add the ability to respond to customer requests for
appointments. Select Project | Add Class
to display the Add New Item dialog box. Name the class ThisAddIn_Code and click OK
to add the class.
Make the following changes in bold to the class declaration:
' Visual Basic
Partial Public Class ThisAddIn
// C#
public partial class ThisAddIn
Partial classes enable you to
split the code for a class across multiple files. In the sample project, the ThisAddIn
file contains the Startup and Shutdown event handlers. Code that works with
data and performs actions is in the ThisAddIn_Code file.
Add the following to the top of the file:
' Visual Basic
Imports System.Windows.Forms
// C#
using System.Windows.Forms;
using Outlook = Microsoft.Office.Interop.Outlook;
using Office = Microsoft.Office.Core;
Add the following declarations:
' Visual Basic
Private buildString As StringBuilder = Nothing
Private mailItem As Outlook.MailItem = Nothing
Private taskItem As Outlook.TaskItem = Nothing
Private counter As Integer= 0
Private custApptReqTableAdapter As _
ContosoAutoDataSetTableAdapters. _
CustomerApptRequestsTableAdapter = Nothing
Private qryTableAdapter As _
ContosoAutoDataSetTableAdapters.QueriesTableAdapter = Nothing
Private requests As _
ContosoAutoDataSet.CustomerApptRequestsDataTable = Nothing
// C#
private StringBuilder buildString = null;
private Outlook.MailItem mailItem= null;
private Outlook.TaskItem taskItem= null;
private int counter = 0;
private
ContosoAutoDataSetTableAdapters.CustomerApptRequestsTableAdapter
custApptReqTableAdapter = null;
private ContosoAutoDataSetTableAdapters.QueriesTableAdapter
qryTableAdapter = null;
private ContosoAutoDataSet.CustomerApptRequestsDataTable
requests = null;
Add the following method to handle the Respond to appt
requests menu item.
' Visual Basic
Public Sub RespondToApptRequests()
End Sub
// C#
public void RespondToApptRequests()
{
}
Add the following code to the method to bring the Inbox to
the foreground.
' Visual Basic
For Each openWindow As Outlook.Explorer In Me.Application.Explorers
If openWindow.CurrentFolder.Name = "Inbox" Then
openWindow.Activate()
Exit For
End If
Next
// C#
foreach (Outlook.Explorer openWindow in this.Application.Explorers)
{
if (openWindow.CurrentFolder.Name == "Inbox")
{
((Outlook._Explorer)openWindow).Activate();
break;
}
}
This code locates the Inbox by
determining the folder each Explorer is displaying. When the code finds the Explorer
displaying the Inbox folder, it activates that Explorer. Note that this is not
required to send the mails. It is merely a UI convenience.
Add the following code to retrieve a list of customers who
requested an appointment but have yet to be contacted:
' Visual Basic
Try
custApptReqTableAdapter = New _
ContosoAutoDataSetTableAdapters. _
CustomerApptRequestsTableAdapter
requests = New ContosoAutoDataSet.CustomerApptRequestsDataTable
custApptReqTableAdapter.FillByNotResponded(requests)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
// C#
try
{
custApptReqTableAdapter = new
ContosoAutoDataSetTableAdapters.
CustomerApptRequestsTableAdapter();
requests = new
ContosoAutoDataSet.CustomerApptRequestsDataTable();
custApptReqTableAdapter.FillByNotResponded(requests);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Add the following code to gets a reference to QueriesTableAdapter.
This contains a method that sets the ResponseSent column to true for each sent
mail.
' Visual Basic
qryTableAdapter = New _
ContosoAutoDataSetTableAdapters.QueriesTableAdapter
// C#
qryTableAdapter = new
ContosoAutoDataSetTableAdapters.QueriesTableAdapter();
Add the following code to loop through each row in the requests
table. The code passes the row to the SendMailToInterestedCustomer and AddTaskForInterestedCustomer
methods. You will create these shortly. The MarkApptRequestAsResponseSent
method calls the stored procedure of the same name, passing in the id of the
current request. The stored procedure locates the appropriate row in the AppointmentRequests
table in the database and changes the ResponseSent column to true. The code
then displays the number of mail messages sent.
' Visual Basic
counter = 0
For Each request As ContosoAutoDataSet.CustomerApptRequestsRow _
In requests.Rows
SendMailToInterestedCustomer(request)
AddTaskForInterestedCustomer(request)
counter += 1
qryTableAdapter.MarkApptRequestAsResponseSent(request.RequestID)
Next
MessageBox.Show(String.Format("{0} messages were sent", _
counter), "Contoso Auto Sales", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
// C#
counter = 0;
foreach (ContosoAutoDataSet.CustomerApptRequestsRow request in
requests.Rows)
{
SendMailToInterestedCustomer(request);
AddTaskForInterestedCustomer(request);
counter += 1;
qryTableAdapter.MarkApptRequestAsResponseSent(request.RequestID);
}
MessageBox.Show(string.Format("{0} messages were sent", counter),
"Contoso Auto Sales", MessageBoxButtons.OK,
MessageBoxIcon.Information);
Add the following method to send confirmation mail to
customers who have requested appointments.
' Visual Basic
Private Sub SendMailToInterestedCustomer( _
ByVal request As ContosoAutoDataSet.CustomerApptRequestsRow)
' Create a new email item and address it to the customer
mailItem = CType(Me.Application.CreateItem( _
Outlook.OlItemType.olMailItem), Outlook.MailItem)
mailItem.Subject = "Thanks for your interest"
mailItem.To = request.Email
' Build a string that will be the body of the email
buildString = New StringBuilder
buildString.Append(String.Format("Dear {0} {1}", _
request.FirstName, request.LastName) & vbCrLf & vbCrLf)
buildString.Append( _
"Thank you for contacting the Automotive Sales " & _
"group at Contoso Ltd. We are looking forward " & _
"to meeting with you and discussing your high " & _
"end auto needs." & vbCrLf)
buildString.Append(String.Format( _
"I will be calling you at {0} within 24 hours " & _
"to set up an appointment.", request.PrimaryPhone) & _
vbCrLf & vbCrLf)
buildString.Append( _
"Thank you" & vbCrLf & vbCrLf & _
"Robert Green" & vbCrLf & _
"Automotive Sales Manager" & vbCrLf & _
"Contoso Ltd." & vbCrLf)
mailItem.Body = buildString.ToString
' Send the email to the customer
mailItem.Send()
End Sub
// C#
private void SendMailToInterestedCustomer(
ContosoAutoDataSet.CustomerApptRequestsRow request)
{
// Create a new email item and address it to the customer
mailItem = ((Outlook.MailItem)(this.Application.CreateItem(
Outlook.OlItemType.olMailItem)));
mailItem.Subject = "Thanks for your interest";
mailItem.To = request.Email;
// Build a string that will be the body of the email
buildString = new StringBuilder();
buildString.Append(string.Format("Dear {0} {1}\r\n\r\n",
request.FirstName, request.LastName));
buildString.Append(
"Thank you for contacting the Automotive Sales " +
"group at Contoso Ltd. We are looking forward " +
"to meeting with you and discussing your high " +
"end auto needs.\r\n");
buildString.Append(string.Format(
"I will be calling you at {0} within 24 hours " +
"to set up an appointment.\r\n\r\n", request.PrimaryPhone));
buildString.Append(
"Thank you\r\n\r\n" +
"Robert Green\r\n" +
"Automotive Sales Manager\r\n" +
"Contoso Ltd.\r\n");
mailItem.Body = buildString.ToString();
// Send the email to the customer
((Outlook._MailItem)mailItem).Send();
}
Most of the text in the email is boilerplate. The code reads
information specific to the customer from the CustomerApptRequestsRow instance.
The last line of C# code casts mailItem to Outlook._MailItem.
If you do not perform the cast, the code will work and send the message, but
you will receive the following warning:
Ambiguity between method
'Microsoft.Office.Interop.Outlook._MailItem.Send()' and non-method
Microsoft.Office.Interop.Outlook.ItemEvents_10_Event.Send'. Using method group.
Add a Task to Follow Up With Customers Who Have Requested Appointments
Add the following method to add a task to follow up with
these customers.
' Visual Basic
Private Sub AddTaskForInterestedCustomer( _
ByVal request As ContosoAutoDataSet.CustomerApptRequestsRow)
' Create a new task.
' The Subject includes the name of the customer.
taskItem = CType(Me.Application.CreateItem( _
Outlook.OlItemType.olTaskItem), Outlook.TaskItem)
taskItem.Subject = String.Format( _
"Interest followup with {0} {1}", _
request.FirstName, request.LastName)
' Build a string that will be the body of the task.
buildString = New StringBuilder
buildString.Append(String.Format("Call {0} {1} at {2}", _
request.FirstName, request.LastName, request.PrimaryPhone) & _
vbCrLf & vbCrLf)
buildString.Append(request.Purpose)
If Not request.IsNotesNull Then
buildString.Append(": " & request.Notes)
End If
taskItem.Body = buildString.ToString
' Set task properties and save the task.
' The code groups these tasks in the Customers category by
' setting the Categories property to Customers
taskItem.Status = Outlook.OlTaskStatus.olTaskNotStarted
taskItem.DueDate = DateTime.Today.AddDays(1)
taskItem.StartDate = DateTime.Today
taskItem.Categories = "Customers"
taskItem.ReminderSet = True
taskItem.Save()
End Sub
// C#
private void AddTaskForInterestedCustomer(
ContosoAutoDataSet.CustomerApptRequestsRow request)
{
// Create a new task.
// The Subject includes the name of the customer.
taskItem = ((Outlook.TaskItem)
(this.Application.CreateItem(Outlook.OlItemType.olTaskItem)));
taskItem.Subject = string.Format(
"Interest followup with {0} {1}",
request.FirstName, request.LastName);
// Build a string that will be the body of the task.
buildString = new StringBuilder();
buildString.Append(string.Format("Call {0} {1} at {2}\r\n\r\n",
request.FirstName, request.LastName, request.PrimaryPhone));
buildString.Append(request.Purpose);
if (!(request.IsNotesNull()))
{
buildString.Append(": " + request.Notes);
}
taskItem.Body = buildString.ToString();
// Set task properties and save the task.
// The code groups these tasks in the Customers category by
// setting the Categories property to Customers
taskItem.Status = Outlook.OlTaskStatus.olTaskNotStarted;
taskItem.DueDate = DateTime.Today.AddDays(1);
taskItem.StartDate = DateTime.Today;
taskItem.Categories = "Customers";
taskItem.ReminderSet = true;
taskItem.Save();
}
Return to the ExplorerWindow file. Add the following code to
the respondMenu Click event code so it calls the RespondToApptRequests method.
' Visual Basic
Globals.ThisAddIn.RespondToApptRequests()
// C#
Globals.ThisAddIn.RespondToApptRequests();
Save your changes. Select Build | Build Solution and verify that the project compiles
correctly.
Add Customers and Appointment Requests
Before you run the code you just entered, you will first
add some customer and appointment request data to the database. Select File | Open | Project/Solution to
display the Open Project dialog box. Navigate to the folder when you downloaded
this tutorial’s sample project. Navigate to the Appointment Requests
Application folder. Then navigate to the VB or CS folder depending on the
language you are using. Select AppointmentRequests.sln
and click OK to open the project.
Note that this application assumes you are using SQL
Server Express. If you are using SQL Server, you will need to change the
connection string.
Press F5 to run
the application. In the form, enter the customer and appointment request
information shown in Figure 10.
Click Save. You should see the
message shown in Figure 11.
Click OK to dismiss the message.
.jpg)
Figure 10. Use this form to enter new appointment requests.
.jpg)
Figure 11. You have saved a new appointment request.
Enter another appointment request using the following:
• Name: Simon Rapier
• City: Bellevue
• Region: WA
• Email: simon@example.com
• Primary phone: 425-555-0134
• Purpose: Research
• Notes: Looking for something fast
Click Save.
Click OK to dismiss the confirmation
message.
Enter another appointment request using the following:
• Name: Arlene Kent
• City: Seattle
• Region: WA
• Email: arlene@example.com
• Primary phone: 206-555-4567
• Secondary phone: 206-555-4568
• Purpose: Purchase
• Notes: I want to drive home in a Fabrikam Cat
Click Save.
Click OK to dismiss the confirmation
message.
Enter one more appointment request using the following:
• Name: Jeff Price
• City: Yakima
• Region: WA
• Email: jeff@example.com
• Primary phone: 509-555-4545
• Purpose: Purchase
• Notes: Birthday present for my wife
Click Save.
Click OK to dismiss the confirmation
message. Close the form.
To confirm you added the data, open the Server Explorer.
Expand the ContosoAuto node. Then expand the Tables node. To see the data in
the Customers table, right-click on Customers and select Show Table Data. You should see the data shown in Figure 12.
Use the same technique to view the sample data in the AppointmentRequests
table. You should see the data shown in Figure 13.
.jpg)
Figure 12. The Customers table contains this data.
.jpg)
Figure 13. The AppointmentRequests table contains this
data.
Run the Application
Return to the CustomerAppointments solution. Press F5
to run the application. After Outlook starts, select Customers | Respond to appt requests. You should see the message
shown in Figure 14.
Click OK to dismiss the message.
.jpg)
Figure 14. You have sent mail to the customers who
requested appointments.
In your Sent Mail folder you should see a mail for each
customer waiting for an appointment (see Figure 15).
.jpg)
Figure 15. An acknowledgement mail sent to a customer
requesting an appointment.
Open the Tasks window. You should see a follow-up task for
each customer (see Figure 16
and Figure 17).
.jpg)
Figure 16. A follow-up task has been created for this
customer.
.jpg)
Figure 17. Follow-up tasks have been created for all
customers who were sent mail.
Exit Outlook and return to Visual Studio.
Exercise 4: Schedule Customer Appointments
After sending mail, the sales manager will call customers and
arrange for an appointment. When the sales manager selects the appropriate menu
item, the Calendar
should be active, and open if necessary. The sales manager should then see
customers who are waiting for appointments to be scheduled. In this exercise,
you will add the ability to schedule appointments with customers.
Display Customers Waiting for Appointments
Return to the ThisAddIn_Code file. Add the following
declarations.
' Visual Basic
Friend ctpApptRequests As _
Microsoft.Office.Tools.CustomTaskPane = Nothing
Private foundCalendar As Boolean = False
Private calendarExplorer As Outlook.Explorer = Nothing
Private calendar as Outlook.MAPIFolder = Nothing
Private calendarItems As Outlook.Items = Nothing
// C#
internal Microsoft.Office.Tools.CustomTaskPane
ctpApptRequests = null;
private bool foundCalendar = false;
private Outlook.Explorer calendarExplorer = null;
private Outlook.MAPIFolder calendar = null;
private Outlook.Items calendarItems = null;
Add the following method to display customers waiting for
appointments to be scheduled:
' Visual Basic
Public Sub ShowCustomerRequests()
End Sub
// C#
public void ShowCustomerRequests()
{
}
Add the following code to the method to loop through each
open Explorer to see if the Calendar is currently open and, if it is, bring it
forward.
' Visual Basic
foundCalendar = False
For Each openWindow As Outlook.Explorer In Me.Application.Explorers
If openWindow.CurrentFolder.Name = "Calendar" Then
foundCalendar = True
openWindow.Activate()
calendar = Me.Application.Session.GetDefaultFolder( _
Outlook.OlDefaultFolders.olFolderCalendar)
Exit For
End If
Next
// C#
foundCalendar = false;
foreach (Outlook.Explorer openWindow in this.Application.Explorers)
{
if (openWindow.CurrentFolder.Name == "Calendar")
{
foundCalendar = true;
((Outlook._Explorer)openWindow).Activate();
calendar = this.Application.Session.GetDefaultFolder(
Outlook.OlDefaultFolders.olFolderCalendar);
break;
}
}
Next, add the following code to open the Calendar if it is
not currently open.
' Visual Basic
If Not foundCalendar Then
calendar = Me.Application.Session.GetDefaultFolder( _
Outlook.OlDefaultFolders.olFolderCalendar)
calendarExplorer = Me.Application.Explorers.Add(calendar, _
Outlook.OlFolderDisplayMode.olFolderDisplayNormal)
calendarExplorer.Display()
End If
// C#
if (!(foundCalendar))
{
calendar = this.Application.Session.GetDefaultFolder(
Outlook.OlDefaultFolders.olFolderCalendar);
calendarExplorer = this.Application.Explorers.Add(calendar,
Outlook.OlFolderDisplayMode.olFolderDisplayNormal);
calendarExplorer.Display();
}
The Add method of the Explorers collection creates a new Explorer.
The first parameter passed to Add identifies the folder this Explorer should
display. The second parameter specifies how the folder should display.
GetDefaultFolder returns a MAPIFolder object that represents an Outlook folder.
You can use GetDefaultFolder to find a particular folder, in this case the
Calendar. The calendarExplorer field therefore represents the Calendar Explorer.
Calling Display brings this Explorer forward.
Next, store a reference to the items in the Calendar folder.
You will use this in code later on.
' Visual Basic
calendarItems = calendar.Items
// C#
calendarItems = calendar.Items;
The Outlook add-in will use a custom task pane that contains
a user control. The user control will display customers waiting for an
appointment. Rather than create the user control from scratch, you will use an
existing version of it (see Figure 18).
Select Project | Add Existing Item to display the Open
File dialog box. Navigate to the folder where you downloaded this tutorial’s
sample project. Navigate to the UI Components\VB or UI Components\CS folder.
Select AppointmentRequests.vb or AppointmentRequests.cs and click OK to add the user control to the
project.
.jpg)
Figure 18. This user control displays customers who have
requested appointments.
Return to the ThisAddIn_Code file. Add the following code to
the ShowCustomerRequests method.
' Visual Basic
ctpApptRequests = Me.CustomTaskPanes.Add( _
New AppointmentRequests, "Appointment Requests")
ctpApptRequests.Width = 375
ctpApptRequests.Visible = True
// C#
ctpApptRequests = this.CustomTaskPanes.Add(
new AppointmentRequests(), "Appointment Requests");
ctpApptRequests.Width = 375;
ctpApptRequests.Visible = true;
When the user selects Schedule appointments from the
Customers menu, this code runs. The code creates a new custom task pane based
on the user control, adds it to Outlook’s collection of custom task panes and
displays the custom task pane. If the user selected that menu item a second
time, this code would run again and display a second task pane next to the
first.
To address this, add the following code in bold:
' Visual Basic
If ctpApptRequests Is Nothing Then
ctpApptRequests = Me.CustomTaskPanes.Add( _
New AppointmentRequests, "Appointment Requests")
ctpApptRequests.Width = 375
End If
ctpApptRequests.Visible = True
// C#
if (ctpApptRequests == null)
{
ctpApptRequests = this.CustomTaskPanes.Add(
new AppointmentRequests(), "Appointment Requests");
ctpApptRequests.Width = 375;
}
ctpApptRequests.Visible = true;
Now, you will only have one instance of the custom task pane,
regardless of how many times the user selects the Schedule appointments menu
item.
You also need to address a more subtle issue. Outlook
recycles windows. If the user closes a window, Outlook does not necessarily
dispose of it. It may simply hide it so it can use it again. Outlook also
associates a custom task pane with a window, in this case the Calendar window.
Suppose the user displays the custom task pane and then closes the Calendar
window. If Outlook only hides the Calendar window, it will also only hide the
custom task pane, not remove it from the collection of task panes.
To address this, return to the ExplorerWindow file. Add the
following code to the Explorers_Close method:
' Visual Basic
' If the Explorer that closed is the Calendar,
' get rid of the custom task pane
If Globals.ThisAddIn.ctpApptRequests IsNot Nothing Then
Globals.ThisAddIn.CustomTaskPanes.Remove( _
Globals.ThisAddIn.ctpApptRequests)
Globals.ThisAddIn.ctpApptRequests = Nothing
End If
// C#
// If the Explorer that closed is the Calendar,
// get rid of the custom task pane
if (!(Globals.ThisAddIn.ctpApptRequests == null))
{
Globals.ThisAddIn.CustomTaskPanes.Remove(
Globals.ThisAddIn.ctpApptRequests);
Globals.ThisAddIn.ctpApptRequests = null;
}
Add the following code to the scheduleMenu Click event code
so it calls the ShowCustomerRequestsmethod.
' Visual Basic
Globals.ThisAddIn.ShowCustomerRequests()
// C#
Globals.ThisAddIn.ShowCustomerRequests();
Save your changes. Select Build | Build Solution and verify that the project compiles
correctly.
Press F5 to run the application. After
Outlook starts, select Customers | Schedule appointments. You should see
the custom task pane appear and display the customers who requested
appointments (see Figure 19).
As you scroll from customer to customer in the grid, you should see the phone numbers,
purpose and notes for each customer.
.jpg)
Figure 19. The custom task pane displays customers who have
requested appointments.
Close the Appointment Requests window. Select Customers | Schedule appointments. You should see only one custom task pane.
Close the Calendar window. Select Customers
| Schedule appointments. You
should see the Calendar window open. You should also see the custom task pane
appear.
Exit Outlook and return to Visual Studio.
Add a Ribbon to Meeting Requests
The sales manager can now call customers who have requested
appointments. While the customer is on the phone, the sales manager will find a
mutually agreeable day and time and schedule an appointment for the customer.
This involves creating a meeting request with a sales consultant. The meeting
request should contain the same customer information displayed in the custom
task pane. There should be a way for the sales manager to copy that information
into the meeting request rather than type it.
To accomplish this, you will add a Ribbon button that
appears when the user creates a new meeting request. You have two options for
customizing the Ribbon in Office applications. You can write Ribbon XML or you
can use the Ribbon Designer. You’ll take the second approach here. Select Project | Add New Item. In the Add New
Item dialog box, select the Ribbon
(Visual Designer) item template and click Add. Visual Studio opens the Ribbon Designer (see Figure 20).
.jpg)
Figure 20. Use the Ribbon Designer to customize the Ribbon.
In Word and Excel, there is one Ribbon and it is
associated with the application. Outlook 2007 does not have an
application-level Ribbon. Rather, each Inspector window has a Ribbon. An Inspector
is a window that displays an Outlook item, whether it is an email, meeting,
task, etc. You now need to associate the Ribbon you just created with an
Inspector window. To select the Ribbon in the Designer, click on the top or
bottom border. Click the RibbonType property in the Properties window and
display the drop-down list of Inspector window types. Uncheck Microsoft.Outlook.Mail.Read and check Microsoft.Outlook.Appointment (see Figure 21).
The Ribbon is now associated with Calendar items.
.jpg)
Figure 21. Associate the Ribbon with Calendar items.
In the Ribbon Designer, select Group1 and change the Label
property to Appointments. From the
Office Ribbon Controls tab of the Toolbox, drag a Button control into the
Appointments group. Name the button copyInfoButton.
Set the Label property to Copy
information and the SuperTip property to Copy the customer’s information to this meeting.
You have two choices for button size: regular or large.
You will use a large button here, so set the ControlSize property to RibbonControlSizeLarge. You also have
the option of including an image with your button in addition to the label. In
general, Ribbon buttons have an image, so you will typically specify one. To
use a custom image, click the ellipsis associated with the Image property. This
displays the Select Resource dialog box. You can then select a local or a
project image resource. This is the same technique you would use to add an
image to the button on a Windows Form.
You can also use one of the built-in Office images. To do
that, set the OfficeImageId property to the id of the image you want. Here, set
the property for the button to CopyToPersonalCalendar.
How do you know what id to use? One easy way to find the
id is to open an Outlook item such as a mail message or calendar appointment,
right-click on the Ribbon or the Quick Access Toolbar and select Customize Quick Access Toolbar. This displays
the Editor Options dialog box. Find the command that includes the icon you want
and display the command’s tooltip. You will find the id in parentheses (see Figure 22).
.jpg)
Figure 22. You can use the Options dialog to find the ids
for built-in Office images.
Another easy way is to use the VSTO Ribbon IDs Power Tool.
You can download and install this from http://www.microsoft.com/downloads/details.aspx?FamilyId=46B6BF86-E35D-4870-B214-4D7B72B02BF9&displaylang=en.
This tool adds an ImageMso Window item to the Tools menu in Visual Studio.
Select that menu item to display the ImageMso Values dialog box. Type CopyTo in the search box and click the
magnifying glass. The buttons with CopyTo in the id name appear (see Figure 23).
When you click the image you want, the tool copies the id to the clipboard.
.jpg)
Figure 23. You can use the VSTO Ribbon IDs Power Tool to
find the ids for built-in Office images.
Populate Meeting Requests
In the Ribbon Designer, double-click the Copy information
button. Add the following code to the copyInfoButton_Click event handler:
' Visual Basic
Globals.ThisAddIn.CopyInfoToMeetingRequest()
// C#
Globals.ThisAddIn.CopyInfoToMeetingRequest();
Return to the ThisAddIn_Code file. Add the following to the
top of the file:
' Visual Basic
Imports System.Data
Add the following method to copy customer information from
the custom task pane to the meeting request:
' Visual Basic
Public Sub CopyInfoToMeetingRequest()
End Sub
// C#
public void CopyInfoToMeetingRequest()
{
}
Add the following code to get a reference to the
appointment item:
' Visual Basic
Dim thisAppointment As Outlook.AppointmentItem = CType( _
Me.Application.ActiveInspector.CurrentItem, _
Outlook.AppointmentItem)
// C#
Outlook.AppointmentItem thisAppointment =
((Outlook.AppointmentItem)
(this.Application.ActiveInspector().CurrentItem));
ActiveInspector references the currently active Inspector.
The CurrentItem property of ActiveInspector references the Outlook item
displayed in the Inspector. thisAppointment therefore references the meeting
request.
Add the following code to get a reference to the user
control in the custom task pane.
' Visual Basic
Dim apptReqUserControl As AppointmentRequests = _
CType(ctpApptRequests.Control, AppointmentRequests)
// C#
AppointmentRequests apptReqUserControl =
(AppointmentRequests)ctpApptRequests.Control;
Add the following code to retrieve the information for the
customer currently selected in the grid in the custom task pane. This code
reads the Current property of the form’s BindingSource component, which returns
an object. The code casts this to an instance of the CustomerApptRequestsRow
class.
' Visual Basic
Dim custApptReqRow As ContosoAutoDataSet.CustomerApptRequestsRow
custApptReqRow = CType(CType( _
apptReqUserControl.CustomerApptRequestsBindingSource.Current, _
DataRowView).Row, ContosoAutoDataSet.CustomerApptRequestsRow)
// C#
ContosoAutoDataSet.CustomerApptRequestsRow custApptReqRow;
custApptReqRow = ((ContosoAutoDataSet.CustomerApptRequestsRow)
(((System.Data.DataRowView)
(apptReqUserControl.customerApptRequestsBindingSource.Current))
.Row));
Add the following code to read information from the custom
task pane and populate the meeting request. You will schedule the meeting with
the sales consultant currently selected in the Schedule with combo box on the
user control.
' Visual Basic
thisAppointment.Subject = String.Format("{0} - {1} {2}", _
apptReqUserControl.employeesComboBox.SelectedValue.ToString, _
custApptReqRow.FirstName, custApptReqRow.LastName)
thisAppointment.Location = "Showroom"
thisAppointment.Recipients.Add( _
apptReqUserControl.employeesComboBox.SelectedValue.ToString)
// C#
thisAppointment.Subject = string.Format("{0} - {1} {2}",
apptReqUserControl.employeesComboBox.SelectedValue.ToString(),
custApptReqRow.FirstName, custApptReqRow.LastName);
thisAppointment.Location = "Showroom";
thisAppointment.Recipients.Add(
apptReqUserControl.employeesComboBox.SelectedValue.ToString());
Add the following code to populate the main body of the
meeting request with notes to the sales consultant.
' Visual Basic
Dim buildString As New StringBuilder
If custApptReqRow.IsCityNull Then
buildString.Append(String.Format( _
"You are meeting with {0} {1}", _
custApptReqRow.FirstName, custApptReqRow.LastName) & _
vbCrLf & vbCrLf)
Else
buildString.Append(String.Format( _
"You are meeting with {0} {1} of {2}", _
custApptReqRow.FirstName, custApptReqRow.LastName, _
custApptReqRow.City) & vbCrLf & vbCrLf)
End If
If custApptReqRow.IsPurposeNull Then
buildString.Append(String.Format( _
"{0} did not list a purpose for the visit", _
custApptReqRow.FirstName) & vbCrLf & vbCrLf)
Else
buildString.Append(String.Format( _
"{0} listed {1} as the purpose of the visit", _
custApptReqRow.FirstName, custApptReqRow.Purpose) & _
vbCrLf & vbCrLf)
End If
Dim primaryPhone As Boolean = _
Not custApptReqRow.IsPrimaryPhoneNull
Dim secondaryPhone As Boolean = _
Not custApptReqRow.IsSecondaryPhoneNull
If primaryPhone AndAlso secondaryPhone Then
buildString.Append(String.Format( _
"You can reach {0} at {1} or {2})", _
custApptReqRow.FirstName, custApptReqRow.PrimaryPhone, _
custApptReqRow.SecondaryPhone) & vbCrLf & vbCrLf)
End If
If primaryPhone AndAlso Not secondaryPhone Then
buildString.Append(String.Format( _
"You can reach {0} at {1}", _
custApptReqRow.FirstName, custApptReqRow.PrimaryPhone) & _
vbCrLf & vbCrLf)
End If
If Not primaryPhone AndAlso secondaryPhone Then
buildString.Append(String.Format( _
"You can reach {0} at {1}", _
custApptReqRow.FirstName, custApptReqRow.SecondaryPhone) & _
vbCrLf & vbCrLf)
End If
If Not primaryPhone AndAlso Not secondaryPhone Then
buildString.Append(String.Format( _
"{0} did not leave a phone number", _
custApptReqRow.FirstName) & vbCrLf & vbCrLf)
End If
buildString.Append(String.Format( _
"{0}'s notes:", custApptReqRow.FirstName) & vbCrLf)
If Not custApptReqRow.IsNotesNull Then
buildString.Append(custApptReqRow.Notes)
End If
thisAppointment.Body = buildString.ToString
// C#
buildString = new StringBuilder();
if (custApptReqRow.IsCityNull())
{
buildString.Append(string.Format(
"You are meeting with {0} {1}\r\n\r\n",
custApptReqRow.FirstName, custApptReqRow.LastName));
}
else
{
buildString.Append(string.Format(
"You are meeting with {0} {1} of {2}\r\n\r\n",
custApptReqRow.FirstName, custApptReqRow.LastName,
custApptReqRow.City));
}
if (custApptReqRow.IsPurposeNull())
{
buildString.Append(string.Format(
"{0} did not list a purpose for the visit\r\n\r\n",
custApptReqRow.FirstName));
}
else
{
buildString.Append(string.Format(
"{0} listed {1} as the purpose of the visit\r\n\r\n",
custApptReqRow.FirstName, custApptReqRow.Purpose));
}
bool primaryPhone = !(custApptReqRow.IsPrimaryPhoneNull());
bool secondaryPhone = !(custApptReqRow.IsSecondaryPhoneNull());
if (primaryPhone && secondaryPhone)
{
buildString.Append(string.Format(
"You can reach {0} at {1} or {2}\r\n\r\n",
custApptReqRow.FirstName, custApptReqRow.PrimaryPhone,
custApptReqRow.SecondaryPhone));
}
if (primaryPhone && !secondaryPhone)
{
buildString.Append(string.Format(
"You can reach {0} at {1}\r\n\r\n",
custApptReqRow.FirstName, custApptReqRow.PrimaryPhone));
}
if (secondaryPhone && !primaryPhone)
{
buildString.Append(string.Format(
"{0} did not leave a phone number\r\n\r\n",
custApptReqRow.FirstName));
}
buildString.Append(string.Format("{0}'s notes:\r\n",
custApptReqRow.FirstName));
if (!(custApptReqRow.IsNotesNull()))
{
buildString.Append(custApptReqRow.Notes);
}
thisAppointment.Body = buildString.ToString();
Add the following code to assign this meeting to the
Customers category.
' Visual Basic
thisAppointment.Categories = "Appointment Request"
// C#
thisAppointment.Categories = " Appointment Request";
Save your changes. Select Build | Build Solution and verify that the project compiles
correctly.
Press F5 to run the application. After
Outlook starts, select Customers | Schedule appointments. You should see
the custom task pane appear and display the customers who requested
appointments.
Add a new meeting request to the Calendar. Click the Add-Ins
tab in the Ribbon. Click the Copy information button (see Figure 24).
The add-in should invite the sales consultant and add the customer’s
information to the meeting request (see Figure 25).
.jpg)
Figure 24. Click the Copy information button to populate
the meeting request.
.jpg)
Figure 25. You populated the meeting request with the
customer’s information.
Close the meeting request without saving it. Exit Outlook
and return to Visual Studio.
Update the Database and Send a Confirmation Mail to the Customer
After creating the meeting request, you want to mark the
appointment request as scheduled and send a confirmation mail to the customer.
You will also update the appropriate row in the AppointmentRequests table and
set the Scheduled and ResponseSent columns to True.
These actions should occur only when the sales manager
saves the meeting request. When this occurs, the ItemAdd event of the Items
object occurs. Items represents all of the items in a folder.
Return to the ThisAddIn_Code file. Add the following code
in bold to the ShowCustomerRequests method:
' Visual Basic
calendarItems = calendar.Items
AddHandler calendarItems.ItemAdd, AddressOf NewCalendarItem
// C#
calendarItems = calendar.Items;
calendarItems.ItemAdd +=new
Outlook.ItemsEvents_ItemAddEventHandler(NewCalendarItem);
In the ThisAddIn_Code file, add the following method to
handle the ItemAdd event:
' Visual Basic
Public Sub NewCalendarItem(ByVal calendarItem As Object)
If Not TypeOf calendarItem Is Outlook.AppointmentItem _
Then Exit Sub
Dim thisAppointment As Outlook.AppointmentItem = _
CType(calendarItem, Outlook.AppointmentItem)
If Not thisAppointment.Categories = "Appointment Request" _
Then Exit Sub
End Sub
// C#
public void NewCalendarItem(object calendarItem)
{
if (!(calendarItem is Outlook.AppointmentItem))
{
return;
}
Outlook.AppointmentItem thisAppointment =
(Outlook.AppointmentItem)calendarItem;
if (!(thisAppointment.Categories == "Appointment Request"))
{
return;
}
}
The ItemAdd event occurs when the user creates a new item in
the Calendar. This will typically be an AppointmentItem, but in the event it is
not, there is no need for any code to run. In addition, if the new item is not
the result of the sales manager responding to a customer request, there is no
need for any code to run. The code above checks for these conditions.
Add the following code to the NewCalendarItem method to get a
reference to the custom task pane’s user control and a reference to the
selected customer:
' Visual Basic
' Get a reference to the custom task pane's user control
Dim apptReqUserControl As AppointmentRequests = _
CType(Globals.ThisAddIn.ctpApptRequests.Control, _
AppointmentRequests)
' Get a reference to the selected customer
Dim custApptReqRow As ContosoAutoDataSet.CustomerApptRequestsRow
custApptReqRow = CType(CType( _
apptReqUserControl.CustomerApptRequestsBindingSource.Current, _
DataRowView).Row, ContosoAutoDataSet.CustomerApptRequestsRow)
// C#
// Get a reference to the custom task pane's user control
AppointmentRequests apptReqUserControl =
(AppointmentRequests)ctpApptRequests.Control;
// Get a reference to the selected customer
ContosoAutoDataSet.CustomerApptRequestsRow custApptReqRow;
custApptReqRow = ((ContosoAutoDataSet.CustomerApptRequestsRow)
(((System.Data.DataRowView)
(apptReqUserControl.customerApptRequestsBindingSource.Current))
.Row));
Add the following code to mark the request as scheduled in
the database. This code calls the MarkApptRequestAsScheduled method of the QueriesTableAdapter.
This method calls the stored procedure of the same name, passing in the id of
the current request. The stored procedure locates the appropriate row in the AppointmentRequests
table in the database and changes the Scheduled column to true.
' Visual Basic
qryTableAdapter = New _
ContosoAutoDataSetTableAdapters.QueriesTableAdapter
qryTableAdapter.MarkApptRequestAsScheduled( _
custApptReqRow.RequestID)
// C#
qryTableAdapter = new
ContosoAutoDataSetTableAdapters.QueriesTableAdapter();
qryTableAdapter.MarkApptRequestAsScheduled(
custApptReqRow.RequestID);
Add the following code to send a confirmation email to the
customer. The code retrieves the customer’s email address and name from the CustomerApptRequests
table. The code retrieves the meeting day and time from the meeting item.
' Visual Basic
mailItem = CType(Me.Application.CreateItem( _
Outlook.OlItemType.olMailItem), Outlook.MailItem)
mailItem.Subject = "Appointment at Contoso Auto Sales"
mailItem.To = custApptReqRow.Email
buildString = New StringBuilder
buildString.Append(String.Format("Dear {0} {1}", _
custApptReqRow.FirstName, custApptReqRow.LastName) & _
vbCrLf & vbCrLf)
buildString.Append(String.Format("This is to confirm " & _
"your appointment with Contoso Auto Sales. " & _
"You will be meeting with {0} on {1:d} at {2:t}.", _
apptReqUserControl.employeesComboBox.SelectedValue.ToString, _
thisAppointment.Start, thisAppointment.Start) & _
vbCrLf & vbCrLf)
buildString.Append( _
"We are looking forward to meeting with you and " & _
"discussing your high end auto needs." & vbCrLf & _
"Thank you" & vbCrLf & vbCrLf & _
"Robert Green" & vbCrLf & _
"Automotive Sales Manager" & vbCrLf & _
"Contoso Ltd." & vbCrLf)
mailItem.Body = buildString.ToString
mailItem.Send()
// C#
mailItem = ((Outlook.MailItem)
(this.Application.CreateItem(Outlook.OlItemType.olMailItem)));
mailItem.Subject = "Appointment at Contoso Auto Sales";
mailItem.To = custApptReqRow.Email;
buildString = new StringBuilder();
buildString.Append(string.Format("Dear {0} {1}\r\n",
custApptReqRow.FirstName, custApptReqRow.LastName));
buildString.Append(string.Format(
"This is to confirm your appointment with Contoso Auto Sales. " +
"You will be meeting with {0} on {1:d} at {2:t}.\r\n",
apptReqUserControl.employeesComboBox.SelectedValue.ToString(),
thisAppointment.Start, thisAppointment.Start));
buildString.Append(
"We are looking forward to meeting with you " +
"and discussing your high end auto needs.\r\n" +
"Thank you\r\n\r\n" +
"Robert Green\r\n" +
"Automotive Sales Manager\r\n" +
"Contoso Ltd.\r\n");
mailItem.Body = buildString.ToString();
((Outlook._MailItem)mailItem).Send();
You will now delete the task reminding the sales manager to
call the customer. Add the following to the top of the ThisAddIn_Code file:
' Visual Basic
Private tasks As Outlook.MAPIFolder = Nothing
Private taskItems As Outlook.Items = Nothing
// C#
private Outlook.MAPIFolder tasks = null;
private Outlook.Items taskItems = null;
In the NewCalendarItem method, add the following code:
' Visual Basic
' Get a reference to the Tasks folder
tasks = Me.Application.Session.GetDefaultFolder( _
Outlook.OlDefaultFolders.olFolderTasks)
taskItems = tasks.Items
' Find the task for this customer
Dim filter As String = _
"[Subject] = 'Interest followup with " & _
String.Format("{0} {1}", custApptReqRow.FirstName, _
custApptReqRow.LastName) & " '"
taskItem = taskItems.Find(filter)
If taskItem IsNot Nothing Then
taskItem.Delete()
End If
// C#
// Get a reference to the Tasks folder
tasks = this.Application.Session.GetDefaultFolder(
Outlook.OlDefaultFolders.olFolderTasks);
taskItems = tasks.Items;
// Find the task for this customer
string filter =
@"[Subject] = 'Interest followup with " +
String.Format("{0} {1}", custApptReqRow.FirstName,
custApptReqRow.LastName)+ " '";
taskItem = (Outlook.TaskItem)taskItems.Find(filter);
if (taskItem != null)
{
taskItem.Delete();
}
You can use the Find method of the Items collection to find
an Outlook item by querying one or more properties of the item. The code above
queries based on the Subject and locates the task you added when you sent the
first email to the customer. If the code locates the task, it deletes the task.
Finally, add the following code to call the RefreshData
method of the AppointmentRequests user control:
' Visual Basic
apptReqUserControl.RefreshData()
// C#
apptReqUserControl.RefreshData();
The RefreshData method refreshes the list of customers
waiting for an appointment. This removes the just scheduled customer from the
grid in the custom task pane.
Save your changes. Select Build | Build Solution and verify that the project compiles correctly.
Press F5 to run the application. After
Outlook starts, select Customers | Schedule appointments. You should see
the custom task pane appear and display the customers who requested
appointments.
Add a new meeting request to the Calendar. Click the Add-Ins
tab in the Ribbon. Click the Copy information button. The add-in should invite
the sales consultant and add the customer’s information to the meeting request.
If you sent the meeting request now, Outlook would likely not
recognize the name in the To box and would display the Check Names dialog box.
For the purpose of this tutorial, replace the name of the sales consultant with
his or her email address in the To box. Email addresses are the first name of
the consultant followed by @contoso.com. As an example, you would replace Jose
Lugo with jose@contoso.com.
Click Send to save
the meeting request and invite the sales consultant. The customer should no
longer appear in the grid in the custom task pane and the meeting request
should appear in the Calendar (see Figure 26).
In your Sent Mail folder, you should see the email sent to the customer (see Figure 27).
You should also see that you deleted the follow-up task.
.jpg)
Figure 26. You scheduled the meeting with the sales
consultant.
.jpg)
Figure 27. You sent a confirmation mail to the customer.
Conclusion
In this tutorial, you saw how to build an Outlook 2007 solution
that makes it easy for the sales manager to respond to customer appointment
requests and schedule appointments in a timely manner.
The sample application has quite a bit of code to work with data.
This code is not at all unique to a solution where Outlook, or Word and Excel
for that matter, are the user interface. In fact, were you to create a Windows
Forms based application to retrieve and manager this customer information, you
would use the same data code.
The sample application also has quite a bit of code to handle
Outlook activities such as sending mail and creating tasks, as well as
scheduling meetings. That code is straightforward and requires only familiarity
with the properties and methods of various Outlook objects.
The hardest part of writing an Outlook solution lies in the
generic nature of the Explorer and Inspector windows. When you start Outlook,
you will typically see “the Inbox”. You can open “the Calendar”. In fact, there
is no Explorer called Inbox or Calendar. An Explorer happens to be displaying
items in the Calendar folder or the Inbox folder. However, both windows are
Explorers. Similarly, windows that display items, regardless of the folder they
are in, are Inspectors.
If you have your Inbox, Calendar and Contacts open, you have
three open Explorers. If you then have ten emails and five contacts open, you
have fifteen Inspectors open. You cannot refer to a specific Explorer or
Inspector in code. To refer to a specific Explorer or Inspector in code, you
have to look for it by name.
About the Author
Robert Green (rgreen@mcwtech.com) is a Senior Consultant with MCW
Technologies. He is a Microsoft MVP for Visual Studio Tools for Office. Robert
has written or co-authored AppDev’s Visual Studio, LINQ, Windows Communication
Foundation and Windows Workflow Foundation courseware, and appears in the video
training for these courses, as well. Robert is a member of the INETA Speaker
Bureau and has been a frequent speaker at technology conferences. Before
joining MCW, Robert worked at Microsoft for 8 years, as a Program Manager on
the Visual Basic product team and as a Product Manager for Visual Studio,
Visual Basic, Visual Studio Tools for Office and Visual FoxPro.