My journey of Office Outlook plug-in development (1)
Purpose
Develop a plug-in that can synchronize Outlook email address book information.
Plan
- VSTO add-in
- COM add-ins
The VSTO add-in supports Outlook starting from the 2010 version.
VSTO 4.0 supports Outlook 2010 and later versions, so you can write the code once and run it on different versions.
COM add-ins are very dependent on the .NET Framework framework and Office version, as you will understand when I talk about it later.
VSTO add-in
VSTO, the full name is Visual Studio Tools for Office, conducts professional development of Office in Microsoft’s Visual Studio platform. VSTO is a replacement for VBA. Using this toolkit makes it easier to develop Office applications. VSTO can also use many functions in the Visual Studio development environment.
VSTO relies on the .NET Framework and cannot run on .net core or .net 5+ platforms.
Create VSTO program
Use Visual Studio 2013 to create a new project. If you use a newer version, you will most likely not find it. Because it was removed. For example, the minimum Outlook 2013 add-in created by Visual Studio 2019
Office/SharePoint -> .Net Framework 4 -> Outlook 2010 Add-in
Afterwards we will get a project structure like this
Open ThisAddIn.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Outlook = Microsoft.Office.Interop.Outlook;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Interop.Outlook;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Collections;
namespace ContactsSynchronization
{
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Execute when Outlook starts
MessageBox.Show("Hello VSTO!");
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
//Executed when Outlook is closed
}
#region VSTO generated code
///
/// Designer supports required methods - don't
/// Use the code editor to modify the contents of this method.
///
private void InternalStartup()
{
//Bind lifecycle function
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
}
Start it and give it a try
At this point we have set up the project, but before writing the code, why not get to know each of the objects in Outlook.
Understand common objects in VSTO
Microsoft Documentation
https://learn.microsoft.com/zh-cn/dotnet/api/microsoft.office.interop.outlook.application?view=outlook-pia
Common types
- MAPIFolder represents a folder in Outlook
- ContactItem represents a contact
- DistListItem represents a group in a contact folder
- OlDefaultFolders gets the enumeration of default file types
- OlItemType gets the enumeration of folder sub-item types
Most of the functions and properties we use are mounted on the global instance Application.
Application.Session;// Session instance
Application.Version;//DLL dynamic link library version
Application.Name;//Application name
Application.Session Session instance can obtain most of the status and data of Outlook. Such as folders, contacts, emails, etc.
Outlook folder structure
Outlook distinguishes user data according to email accounts, that is, each email account has an independent inbox, contacts, etc.
Outlook default folder structure
Get the default contact folder of the first email account
Application.Session.Stores.Cast.First().GetDefaultFolder(OlDefaultFolders.olFolderContacts);
Get Outlook status information
Get contact information
MAPIFolder folder = Application.Session.GetDefaultFolder(OlDefaultFolders.olFolderContacts);//Get the default address book folder
IEnumerable contactItems = folder.Items.OfType(); // Get the sub-items under the folder, OfType only gets the contacts
foreach (ContactItem it in contactItems)
{
// Get various information of the contact
string fullName = it.FullName;
// Note that if you modify the contact information here, Save() will not take effect.
}
Add contact
MAPIFolder folder = Application.Session.GetDefaultFolder(OlDefaultFolders.olFolderContacts);//Get the default contact folder
ContactItem contact = folder.Items.Add(OlItemType.olContactItem);//Add contact item
//Set various information
contact.FirstName = "三";
contact.LastName = "Zhang";
contact.Email1Address = "[email protected]";
// store contacts
contact.Save();
Delete Contact
Microsoft.Office.Interop.Outlook.MAPIFolder deletedFolder = application.Session.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems);//Default contact folder
int count = deletedFolder.Items.Count; // Get the number of sub-items, including contacts and groups
for (int i = count; i > 0; i--)// Traverse and delete
{
deletedFolder.Items.Remove(i);
}
finished product code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Outlook = Microsoft.Office.Interop.Outlook;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Interop.Outlook;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Collections;
namespace ContactsSynchronization
{
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
OperatorContact operatorInstance = new OperatorContact(this.Application);
operatorInstance.Task();
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
#region VSTO generated code
///
/// Designer supports required methods - don't
/// Use the code editor to modify the contents of this method.
///
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
class OperatorContact
{
public OperatorContact(Microsoft.Office.Interop.Outlook.Application application)
{
this.application = application;
}
Microsoft.Office.Interop.Outlook.Application application = null; // outlook program instance
private static string addressBookName = "Tangshi Group Address Book"; // Address book name
private Microsoft.Office.Interop.Outlook.MAPIFolder addressBookFolder = null; // Address book folder instance
public void Task()
{
new Thread(Run).Start();
}
///
/// Open a new thread to perform the task, do not block the original thread
///
private void Run()
{
try
{
if (NeedUpdate())
{
addressBookFolder = getAddressBookFolder();//Create an address book with overwriting
List remoteContacts = readRemoteContacts();//Read the remote mailbox address book
if (remoteContacts == null) return;
Adjust(remoteContacts);//Adjust contacts and groups
updateClientVersion();//Update local address book version number
}
}
catch (System.Exception ex)
{
const string path = @"C:\TONS\email-plugin-error.log";
FileInfo fileInfo = new FileInfo(path);
long length = 0;
if (fileInfo.Exists && fileInfo.Length != 0) length = fileInfo.Length / 1024 / 1024;
if (length <= 3) File.AppendAllText(path, ex.Message + "\r\n");
else File.WriteAllText(path, ex.Message + "\r\n");
}
}
///
/// Create address book by overwriting
///
/// Address book folder instance
private Microsoft.Office.Interop.Outlook.MAPIFolder getAddressBookFolder()
{
// Get the enumerator of the address book folder of the user's first PST file
IEnumerator en = application.Session.Stores.Cast().First()
.GetDefaultFolder(OlDefaultFolders.olFolderContacts)
.Folders.GetEnumerator();
bool exits = false;
Microsoft.Office.Interop.Outlook.MAPIFolder folder = null;
// Traverse the folder
while (en.MoveNext()) {
Microsoft.Office.Interop.Outlook.MAPIFolder current = (Microsoft.Office.Interop.Outlook.MAPIFolder)en.Current;
if (current.Name == addressBookName) {
exits = true;
folder = current;
}
}
if (!exits)
{
//Create Tangshi Group address book and map it into address book format
Microsoft.Office.Interop.Outlook.MAPIFolder newFolder = application.Session.Stores.Cast().First()
.GetDefaultFolder(OlDefaultFolders.olFolderContacts)
.Folders.Add(addressBookName);
newFolder.ShowAsOutlookAB = true;//Set to "Contacts" folder
return newFolder;
}
else {
// Return to the existing simultaneous group address book folder and delete the contents inside
int count = folder.Items.Count;
for (int i = count; i > 0; i--)
{
folder.Items.Remove(i);
}
Microsoft.Office.Interop.Outlook.MAPIFolder deletedFolder = application.Session.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems);
count = deletedFolder.Items.Count;
for (int i = count; i > 0; i--)
{
deletedFolder.Items.Remove(i);
}
return folder;
}
}
///
/// Update the local version of Bronze Beard Record
///
private void updateClientVersion()
{
String path = @"C:\TONS\email-plugin-version.conf";
string version = getRemoteVersion();
if (!File.Exists(path))
{
File.WriteAllText(path,version);
}
else {
File.WriteAllText(path, version);
}
}
///
/// Determine whether update is needed
///
/// boolean value
private bool NeedUpdate()
{
string remoteVersion = getRemoteVersion();
if (remoteVersion == null) return false;
string clientVersion = getClientVersion();
return !(clientVersion == remoteVersion);
}
///
/// Read the server's address book version
///
/// Address book version
private string getRemoteVersion()
{
List<Dictionary> items = SelectList(
"SELECT TOP(1) [version] FROM TonsOfficeA..VersionControl WHERE applicationID = N'EmailContact'"
, "Server=192.168.2.1;Database=TonsOfficeA;uid=sa;pwd=dsc");
if (items == null) return null;
return items[0]["version"].ToString();
}
///
/// Get the local address book version
///
/// Address book version
private string getClientVersion()
{
String path = @"C:\TONS\email-plugin-version.conf";
if (!File.Exists(path)) return null;
return File.ReadAllText(path);
}
///
/// Read the remote address book
///
/// Contact instance collection
private List readRemoteContacts()
{
List remoteContacts = new List();
List<Dictionary> items =
SelectList(
"select [emailAddress],[firstName],[lastName],[companyName],[department],[_group] as 'group',[jobTitle] from [TonsOfficeA].[dbo].[EmailContacts]",
"Server=192.168.2.1;Database=TonsOfficeA;uid=sa;pwd=dsc");
items.ForEach(it =>
{
Contact contact = new Contact();
contact.email1Address = it["emailAddress"].ToString();
contact.firstName = it["firstName"].ToString();
contact.lastName = it["lastName"].ToString();
contact.companyName = it["companyName"].ToString();
contact.department = it["department"].ToString();
if (it["jobTitle"] != null) contact.jobTitle = it["jobTitle"].ToString();
contact.groups = it["group"].ToString().Split(',').ToList();
remoteContacts.Add(contact);
});
return remoteContacts;
}
///
/// Execute select statement
///
/// select statementMicrosoft.Office.Interop.Outlook.MAPIFolder folder = null;
// Traverse the folder
while (en.MoveNext()) {
Microsoft.Office.Interop.Outlook.MAPIFolder current = (Microsoft.Office.Interop.Outlook.MAPIFolder)en.Current;
if (current.Name == addressBookName) {
exits = true;
folder = current;
}
}
if (!exits)
{
//Create Tangshi Group address book and map it into address book format
Microsoft.Office.Interop.Outlook.MAPIFolder newFolder = application.Session.Stores.Cast().First()
.GetDefaultFolder(OlDefaultFolders.olFolderContacts)
.Folders.Add(addressBookName);
newFolder.ShowAsOutlookAB = true;//Set to "Contacts" folder
return newFolder;
}
else {
// Return to the existing simultaneous group address book folder and delete the contents inside
int count = folder.Items.Count;
for (int i = count; i > 0; i--)
{
folder.Items.Remove(i);
}
Microsoft.Office.Interop.Outlook.MAPIFolder deletedFolder = application.Session.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems);
count = deletedFolder.Items.Count;
for (int i = count; i > 0; i--)
{
deletedFolder.Items.Remove(i);
}
return folder;
}
}
///
/// Update the local version of Bronze Beard Record
///
private void updateClientVersion()
{
String path = @"C:\TONS\email-plugin-version.conf";
string version = getRemoteVersion();
if (!File.Exists(path))
{
File.WriteAllText(path,version);
}
else {
File.WriteAllText(path, version);
}
}
///
/// Determine whether update is needed
///
/// boolean value
private bool NeedUpdate()
{
string remoteVersion = getRemoteVersion();
if (remoteVersion == null) return false;
string clientVersion = getClientVersion();
return !(clientVersion == remoteVersion);
}
///
/// Read the server's address book version
///
/// Address book version
private string getRemoteVersion()
{
List<Dictionary> items = SelectList(
"SELECT TOP(1) [version] FROM TonsOfficeA..VersionControl WHERE applicationID = N'EmailContact'"
, "Server=192.168.2.1;Database=TonsOfficeA;uid=sa;pwd=dsc");
if (items == null) return null;
return items[0]["version"].ToString();
}
///
/// Get the local address book version
///
/// Address book version
private string getClientVersion()
{
String path = @"C:\TONS\email-plugin-version.conf";
if (!File.Exists(path)) return null;
return File.ReadAllText(path);
}
///
/// Read the remote address book
///
/// Contact instance collection
private List readRemoteContacts()
{
List remoteContacts = new List();
List<Dictionary> items =
SelectList(
"select [emailAddress],[firstName],[lastName],[companyName],[department],[_group] as 'group',[jobTitle] from [TonsOfficeA].[dbo].[EmailContacts]",
"Server=192.168.2.1;Database=TonsOfficeA;uid=sa;pwd=dsc");
items.ForEach(it =>
{
Contact contact = new Contact();
contact.email1Address = it["emailAddress"].ToString();
contact.firstName = it["firstName"].ToString();
contact.lastName = it["lastName"].ToString();
contact.companyName = it["companyName"].ToString();
contact.department = it["department"].ToString();
if (it["jobTitle"] != null) contact.jobTitle = it["jobTitle"].ToString();
contact.groups = it["group"].ToString().Split(',').ToList();
remoteContacts.Add(contact);
});
return remoteContacts;
}
///
/// Execute select statement
///
/// select statement
/// Database link statement
/// List<Dictionary>results
///
public List<Dictionary> SelectList(string sql, string connection)
{
if (sql == null || connection == null || sql == "" || connection == "")
throw new System.Exception("No SQL statement or Connection link statement was passed in");
List<Dictionary> list = new List<Dictionary>();
SqlConnection conn = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
SqlDataReader sqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (sqlDataReader == null) return null;
while (sqlDataReader.Read())
{
int count = sqlDataReader.FieldCount;
if (count <= 0) continue;
Dictionary map = new Dictionary();
for (int i = 0; i < count; i++)
{
string name = sqlDataReader.GetName(i);
object value = sqlDataReader.GetValue(i);
map.Add(name, value);
}
list.Add(map);
}
conn.Close();
return list;
}
catch(System.Exception)
{
conn.Close();
return null;
}
}
///
/// Adjust address book contacts
///
/// Source of contact information imported from the database
private void Adjust(List remoteContacts)
{
// Make a copy to create a group
List distListItems = new List();
Contact[] tempItems = new Contact[remoteContacts.Count];
remoteContacts.CopyTo(tempItems);
tempItems.ToList().ForEach(it =>
{
it.groups.ForEach(g =>
{
Contact con = new Contact
{
firstName = it.firstName,
lastName = it.lastName,
email1Address = it.email1Address,
companyName = it.companyName,
department = it.department,
group=g
};
distListItems.Add(con);
});
});
// Add contacts
remoteContacts.ForEach(it =>
{
ContactItem contact = addressBookFolder.Items.Add();
contact.FirstName = it.firstName;
contact.LastName = it.lastName;
contact.Email1Address = it.email1Address;
contact.CompanyName = it.companyName;
contact.Department = it.department;
if (it.jobTitle != null) contact.JobTitle = it.jobTitle;
contact.Save();
});
//Group by group and create a group to save
List contactStores = distListItems
.GroupBy(it => it.group)
.Select(it => new ContactStore { group = it.Key, contacts = it.ToList() })
.ToList();
contactStores.ForEach(it =>
{
DistListItem myItem = addressBookFolder.Items.Add(OlItemType.olDistributionListItem);
it.contacts.ForEach(contact =>
{
string id = String.Format("{0}{1}({2})", contact.lastName, contact.firstName,
contact.email1Address);
Recipient recipient = application.Session.CreateRecipient(id);
recipient.Resolve();
myItem.AddMember(recipient);
});
myItem.DLName = it.group;
myItem.Save();
});
}
struct Contact
{
public string email1Address; // Email
public string firstName; // Last name
public string lastName; // name
public string companyName; // company name
public string department; //Department name
public List groups; // Grouped collection
public string group; // group
public string jobTitle; // Job title
}
struct ContactStore
{
public string group;
public List contacts;
}
}
}
Packaging, installation and uninstallation
Right click on the project -> Publish
After publishing you will see a structure like this
Click setup.exe to install
Uninstallation requires VSTOInstaller.exe
"C:\Program Files (x86)\Common Files\microsoft shared\VSTO\10.0\VSTOInstaller.exe" /u "Your .vsto file directory"
/// Database link statement
/// List<Dictionary>results
///
public List<Dictionary> SelectList(string sql, string connection)
{
if (sql == null || connection == null || sql == “” || connection == “”)
throw new System.Exception(“No SQL statement or Connection link statement was passed in”);
List<Dictionary> list = new List<Dictionary>();
SqlConnection conn = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
SqlDataReader sqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (sqlDataReader == null) return null;
while (sqlDataReader.Read())
{
int count = sqlDataReader.FieldCount;
if (count <= 0) continue;
Dictionary map = new Dictionary();
for (int i = 0; i < count; i++)
{
string name = sqlDataReader.GetName(i);
object value = sqlDataReader.GetValue(i);
map.Add(name, value);
}
list.Add(map);
}
conn.Close();
return list;
}
catch(System.Exception)
{
conn.Close();
return null;
}
}
///
///
/// Source of contact information imported from the database
private void Adjust(List remoteContacts)
{
// Make a copy to create a group
List distListItems = new List();
Contact[] tempItems = new Contact[remoteContacts.Count];
remoteContacts.CopyTo(tempItems);
tempItems.ToList().ForEach(it =>
{
it.groups.ForEach(g =>
{
Contact con = new Contact
{
firstName = it.firstName,
lastName = it.lastName,
email1Address = it.email1Address,
companyName = it.companyName,
department = it.department,
group=g
};
distListItems.Add(con);
});
});
// Add contacts
remoteContacts.ForEach(it =>
{
ContactItem contact = addressBookFolder.Items.Add();
contact.FirstName = it.firstName;
contact.LastName = it.lastName;
contact.Email1Address = it.email1Address;
contact.CompanyName = it.companyName;
contact.Department = it.department;
if (it.jobTitle != null) contact.JobTitle = it.jobTitle;
contact.Save();
});
//Group by group and create a group to save
List contactStores = distListItems
.GroupBy(it => it.group)
.Select(it => new ContactStore { group = it.Key, contacts = it.ToList() })
.ToList();
contactStores.ForEach(it =>
{
DistListItem myItem = addressBookFolder.Items.Add(OlItemType.olDistributionListItem);
it.contacts.ForEach(contact =>
{
string id = String.Format(“{0}{1}({2})”, contact.lastName, contact.firstName,
contact.email1Address);
Recipient recipient = application.Session.CreateRecipient(id);
recipient.Resolve();
myItem.AddMember(recipient);
});
myItem.DLName = it.group;
myItem.Save();
});
}
struct Contact
{
public string email1Address; // Email
public string firstName; // Last name
public string lastName; // name
public string companyName; // company name
public string department; //Department name
public List groups; // Grouped collection
public string group; // group
public string jobTitle; // Job title
}
struct ContactStore
{
public string group;
public List contacts;
}
}
}
Packaging, installation and uninstallation
Right click on the project -> Publish
After publishing you will see a structure like this
Click setup.exe to install
Uninstallation requires VSTOInstaller.exe
"C:\Program Files (x86)\Common Files\microsoft shared\VSTO\10.0\VSTOInstaller.exe" /u "Your .vsto file directory"
=”Packaging, installation and uninstallation”>Packaging, installation and uninstallation
Right click on the project -> Publish
After publishing you will see a structure like this
Click setup.exe to install
Uninstallation requires VSTOInstaller.exe
"C:\Program Files (x86)\Common Files\microsoft shared\VSTO\10.0\VSTOInstaller.exe" /u "Your .vsto file directory"
微信扫一扫打赏
支付宝扫一扫打赏
