Building the Contoso Auto Sales Office Business Application Part 1 - Scheduling Customer Appointments
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.
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).
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.
Figure 3. The Manufacturers table contains this data.
Figure 4. The Models table contains this data.
Figure 5. The AvailablePackages table contains this data.
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.
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).
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.
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.
Figure 10. Use this form to enter new appointment requests.
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.
Figure 12. The Customers table contains this data.
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.
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).
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).
Figure 16. A follow-up task has been created for this customer.
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.
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.
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).
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.
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).
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 https://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.
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
// C#
using 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).
Figure 24. Click the Copy information button to populate the meeting request.
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.
Figure 26. You scheduled the meeting with the sales consultant.
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.