This article shows you how to write an addin (plugin) for MS Office applications. First, I give you one base unit you may re-use to implement your features into the Office tools. It is not quite perfect yet, but it is a start. I will update it some day. Second, I give you a sample showing you how to work this class. This sample will help you to create add-ins for Outlook, Qord and Excel.
Office plug-ins are COM libraries that use the IDExtensibility2 Interface.

IN THE BEGINNING
================

The idea behind this unit was, that I want to give my users a simple way of loading data from my (distr.) database application into their MS Word documents, without them having to cut & paste every single piece of data.

THE MS INFORMATION FLOW
=======================

Therefore, I started looking around for information on how to create add-ins for MS Office applications. The help, you'll get from MS is very little and all for their VB/C++ wizards. This is not going to help a Delphi programmer much.

At some point I found few information on which COM interfaces to use and how to implement them for my purposes. The solution I have created you will find below. Go ahead and try it, or read some more about it details now.

The approach shown below, will work on MS Office 2000 and probably MS Office XP.

IMPORTING TYPE LIBRARIES
========================

Some of the type libraries you need to create may conflict with your current (standard) Delphi settings. To aviod this, you should unload one of your component packages (menu: Component|Install Packages). Remove the check mark for the component "Borland Sample Automation Server Components". !!! Do not remove the Component Package!!!

After having unloaded these packages, you can go ahead and import the following type libraries:

\Program Files\Common Files\Designer\MSADDNDR.DLL
\Program Files\Microsoft Office\Office\MSWORD9.OLB
\Program Files\Microsoft Office\Office\EXCEL9.OLB
\Program Files\Microsoft Office\Office\MSOUTL9.OLB
\Program Files\Microsoft Office\Office\MSO9.DLL

To import these libraries, you have to go to "Project|Import Type Library...", select "Add..", an choose the files accordingly. Choose a path for the unit, and select "Create Unit". Repeat this procedure for each file.

CORRECTION THE IMPORTED TLB
===========================

After importing the MSADDNDR.DLL type library you will have to correct a few lines.

FOnConnection(Self, Params[0] {const IDispatch}, Params[1] {ext_ConnectMode}, Params[2] {const IDispatch}, Params[3] {var {??PSafeArray} OleVariant});

must be corrected to:

FOnConnection(Self, Params[0] {const IDispatch}, Params[1] {ext_ConnectMode}, Params[2] {const IDispatch}, Params[3] {var {??PSafeArray OleVariant});

(There is a closing comment } to many. A few lines (starting 445) that contain this error. You will find them, while compiling your project.

PLANING YOUR MS OFFICE ADD-IN
=============================

In order for your MS Office add-in to work you have to implement the IDTExtensibility2 interface, defined in the AddInDesignerObjects_TLB unit. This interface defines five routines, called by the Office applications.

• OnAddInsUpdate: Is called by the office application when the list of installed add-ins has changed. You might want to take notice, otherwise you can leave the procedure just empty. It must, however, be implemented.
  
• OnBeginShutdown: Is called, just before the add-in is unloaded. Your add-in should not accept any user input anymore.
  
• OnConnection: This procedure is called when the add-in is loaded.
  
• OnDisconnection: This procedure will be called, when the add-in is unloaded. Use this procedure to free up resources you have taken.
  
• OnStartupComplete: This procedure is called when your add-in is loaded with the office application, automatically.
  

These five procedures are implemented by the unit shown below. You may override them as needed.

WRAPPER COMPONENT FOR THE OFFICE BUTTONS
========================================

Further, I have created a wrapper component for the office buttons you will create. This component will handle the buttons of your add-in. Further, it maps a few of the button properties, you can access directly. You might want to map more than the ones shown.

This wrapper component is rather simple. If you have any questions, add them to comments section. I will answer them.

THE BASE UNIT FOR YOUR OFFICE ADD-IN
====================================

Note: You will find a sample of how to use this base unit below.



unit uOfficePlugin;

interface

uses
  OleServer, ActiveX, Classes, ComObj,
  Office_TLB, AddinDesignerObjects_TLB, Excel_TLB, Outlook_TLB, Word_TLB;

type
  TOfficeButtonClickEvent = procedure(
    const Control: OleVariant; var CancelDefault: OleVariant
  ) of object;

  TOfficeButton = class(TOleServer)
  private
    FCommandBarButton: _CommandBarButton;
    FOnClick: TOfficeButtonClickEvent;
    function GetCaption: WideString;
    function GetShortCutText: WideString;
    function GetStyle: MsoButtonStyle;
    function GetTag: WideString;
    function GetVisible: WordBool;
    procedure SetCaption(const Value: WideString);
    procedure SetShortCutText(const Value: WideString);
    procedure SetStyle(const Value: MsoButtonStyle);
    procedure SetTag(const Value: WideString);
    procedure SetVisible(const Value: WordBool);
    function GetTooltipText: WideString;
    procedure SetTooltipText(const Value: WideString);
    function GetState: MsoButtonState;
    procedure SetState(const Value: MsoButtonState);
    function GetHyperlinkType: MsoCommandBarButtonHyperlinkType;
    procedure SetHyperlinkType(const Value: MsoCommandBarButtonHyperlinkType);
  protected
    procedure InvokeEvent(DispID: TDispID; var Params: TVariantArray); override;
  public
    procedure Connect; override;
    procedure ConnectTo(aServerInterface: _CommandBarButton);
    procedure Disconnect; override;
    procedure Delete;

    property Caption: WideString read GetCaption write SetCaption;
    property Visible: WordBool read GetVisible write SetVisible;
    property State: MsoButtonState read GetState write SetState;
    property Style: MsoButtonStyle read GetStyle write SetStyle;
    property HyperlinkType: MsoCommandBarButtonHyperlinkType
      read GetHyperlinkType write SetHyperlinkType;
    property Tag: WideString read GetTag write SetTag;
    property ShortCutText: WideString
      read GetShortCutText write SetShortCutText;
    property TooltipText: WideString read GetTooltipText write SetTooltipText;

    property OnClick: TOfficeButtonClickEvent read FOnClick write FOnClick;
  end;
  TOfficeButtonClass = class of TOfficeButton;

  TOfficeAddIn = class(TAutoObject, IDTExtensibility2)
  private
    FOfficeButtonClass: TOfficeButtonClass;
    FExcelApp: TExcelApplication;
    FOutlookApp: TOutlookApplication;
    FWordApp: TWordApplication;
  protected
    procedure OnConnection(
      const Application: IDispatch; ConnectMode: ext_ConnectMode;
      const AddInInst: IDispatch; var custom: PSafeArray
    ); virtual; safecall;
    procedure OnDisconnection(
      RemoveMode: ext_DisconnectMode; var custom: PSafeArray
    ); virtual; safecall;
    procedure OnAddInsUpdate(var custom: PSafeArray); virtual; safecall;
    procedure OnStartupComplete(var custom: PSafeArray); virtual; safecall;
    procedure OnBeginShutdown(var custom: PSafeArray); virtual; safecall;
    function GetCommandBar(
      aCommandBars: Office_TLB._CommandBars; aName: WideString;
      aCreateOnDemand: WordBool; aCreatePos: MsoBarPosition = msoBarFloating
    ): CommandBar;
    function GetOfficeButton(
      aCommandBar: CommandBar; aName: WideString; aCreateOnDemand: WordBool;
      aOnClick: TOfficeButtonClickEvent;
      aCreateCaption: WideString = '';
      aCreateStyle: MsoButtonStyle = msoButtonCaption;
      aCreateVisible: WordBool = True;
      aCreateControlType: MsoControlType = msoControlButton
    ): TOfficeButton;

    property OfficeButtonClass: TOfficeButtonClass
      read FOfficeButtonClass write FOfficeButtonClass;
  public
    procedure Initialize; override;

    property ExcelApp: TExcelApplication read FExcelApp;
    property OutlookApp: TOutlookApplication read FOutlookApp;
    property WordApp: TWordApplication read FWordApp;
  end;

implementation

uses
  SysUtils, Dialogs
  {$IFDEF VER140}
  , OleCtrls, Variants
  {$ENDIF}
  ;

{ TOfficeButton }

procedure TOfficeButton.Connect;
var
  PUnknown: IUnknown;
begin
//  inherited Connect;
  // connect the class to the office button
  if FCommandBarButton = nil then
  begin
    PUnknown := GetServer;
    ConnectEvents(PUnknown);
    FCommandBarButton := PUnknown as _CommandBarButton;
  end;
end;

procedure TOfficeButton.ConnectTo(aServerInterface: _CommandBarButton);
begin
  // disconnect the class from the current office button
  Disconnect;
  // connect to the new office button
  FCommandBarButton := aServerInterface;
  ConnectEvents(FCommandBarButton);
end;

procedure TOfficeButton.Delete;
begin
  // delete the button from the office bar
  FCommandBarButton.Delete(EmptyParam);
end;

procedure TOfficeButton.Disconnect;
begin
//  inherited Disconnect;
  // disconnect the class from the current office button
  if FCommandBarButton <> nil then
  begin
    DisconnectEvents(FCommandBarButton);
    FCommandBarButton := nil;
  end;
end;

function TOfficeButton.GetCaption: WideString;
begin
  Result := FCommandBarButton.Caption;
end;

function TOfficeButton.GetHyperlinkType: MsoCommandBarButtonHyperlinkType;
begin
  Result := FCommandBarButton.HyperlinkType;
end;

function TOfficeButton.GetShortCutText: WideString;
begin
  Result := FCommandBarButton.ShortcutText;
end;

function TOfficeButton.GetState: MsoButtonState;
begin
  Result := FCommandBarButton.State;
end;

function TOfficeButton.GetStyle: MsoButtonStyle;
begin
  Result := FCommandBarButton.Style;
end;

function TOfficeButton.GetTag: WideString;
begin
  Result := FCommandBarButton.Tag;
end;

function TOfficeButton.GetTooltipText: WideString;
begin
  Result := FCommandBarButton.TooltipText;
end;

function TOfficeButton.GetVisible: WordBool;
begin
  Result := FCommandBarButton.Visible;
end;

procedure TOfficeButton.InvokeEvent(DispID: TDispID;
  var Params: TVariantArray);
begin
  inherited InvokeEvent(DispID, Params);
  // react to the standard office button events
  case DispID of
    -1: Exit;
     1: if Assigned(FOnClick) then
       FOnClick(Params[0], Params[1]);
  end;
end;

procedure TOfficeButton.SetCaption(const Value: WideString);
begin
  FCommandBarButton.Set_Caption(Value);
end;

procedure TOfficeButton.SetHyperlinkType(
  const Value: MsoCommandBarButtonHyperlinkType);
begin
  FCommandBarButton.Set_HyperlinkType(Value);
end;

procedure TOfficeButton.SetShortCutText(const Value: WideString);
begin
  FCommandBarButton.Set_ShortcutText(Value);
end;

procedure TOfficeButton.SetState(const Value: MsoButtonState);
begin
  FCommandBarButton.Set_State(Value);
end;

procedure TOfficeButton.SetStyle(const Value: MsoButtonStyle);
begin
  FCommandBarButton.Set_Style(Value);
end;

procedure TOfficeButton.SetTag(const Value: WideString);
begin
  FCommandBarButton.Set_Tag(Value);
end;

procedure TOfficeButton.SetTooltipText(const Value: WideString);
begin
  FCommandBarButton.Set_TooltipText(Value);
end;

procedure TOfficeButton.SetVisible(const Value: WordBool);
begin
  FCommandBarButton.Set_Visible(Value);
end;

{ TOfficeAddIn }

function TOfficeAddIn.GetCommandBar(
  aCommandBars: _CommandBars; aName: WideString; aCreateOnDemand: WordBool;
  aCreatePos: MsoBarPosition = msoBarFloating
): CommandBar;
begin
  Result := nil;
  if aCommandBars = nil then
    Exit;
  try Result := aCommandBars.Item[aName]; except end;
  if (Result = nil) and aCreateOnDemand then
    Result := aCommandBars.Add(aName, aCreatePos, EmptyParam, EmptyParam);
end;

function TOfficeAddIn.GetOfficeButton(
  aCommandBar: CommandBar; aName: WideString; aCreateOnDemand: WordBool;
  aOnClick: TOfficeButtonClickEvent; aCreateCaption: WideString;
  aCreateStyle: MsoButtonStyle; aCreateVisible: WordBool;
  aCreateControlType: MsoControlType
): TOfficeButton;
var
  OfficeButtonIntf: CommandBarControl;
begin
  Result := nil;
  if aCommandBar = nil then
    Exit;
  OfficeButtonIntf := aCommandBar.FindControl(
    EmptyParam, EmptyParam, aName, EmptyParam, EmptyParam
  );
  if OfficeButtonIntf = nil then
  begin
    if aCreateOnDemand then
    begin
      OfficeButtonIntf := aCommandBar.Controls.Add(
        aCreateControlType, EmptyParam, EmptyParam, EmptyParam, EmptyParam
      );
      Result := FOfficeButtonClass.Create(nil);
      Result.ConnectTo(OfficeButtonIntf as _CommandBarButton);
      Result.Tag := aName;
      Result.Caption := aCreateCaption;
      Result.Style := aCreateStyle;
      Result.Visible := aCreateVisible;
      Result.OnClick := aOnClick;
    end;
  end else begin
    Result := FOfficeButtonClass.Create(nil);
    Result.ConnectTo(OfficeButtonIntf as _CommandBarButton);
    Result.OnClick := aOnClick;
  end;
end;

procedure TOfficeAddIn.Initialize;
begin
  inherited Initialize;
  FOfficeButtonClass := TOfficeButton;
  FExcelApp := nil;
  FOutlookApp := nil;
  FWordApp := nil;
end;

procedure TOfficeAddIn.OnAddInsUpdate(var custom: PSafeArray);
begin
  // nothing to be done in the base class
  // will be called if the list of installed add-ins has changed
end;

procedure TOfficeAddIn.OnBeginShutdown(var custom: PSafeArray);
begin
  // nothing to be done in the base class
  // descending classes should free any memory
end;

procedure TOfficeAddIn.OnConnection(
  const Application: IDispatch; ConnectMode: ext_ConnectMode;
  const AddInInst: IDispatch; var custom: PSafeArray
);
var
  App: OleVariant;
  AppName: String;
begin
  App := Application;
  // find the type off application running that is loading the server
  AppName := LowerCase(String(App.Name));
  if Pos('outlook', AppName) > 0 then
  begin
    // MS Outlook
    FOutlookApp := TOutlookApplication.Create(nil);
    FOutlookApp.ConnectTo(Application as Outlook_TLB._Application);
  end else if Pos('word', AppName) > 0 then begin
    // MS Word
    FWordApp := TWordApplication.Create(nil);
    FWordApp.ConnectTo(Application as Word_TLB._Application);
  end else if Pos('excel', AppName) > 0 then begin
    // MS Excel
    FExcelApp := TExcelApplication.Create(nil);
    FExcelApp.ConnectTo(Application as Excel_TLB._Application);
  end;
end;

procedure TOfficeAddIn.OnDisconnection(RemoveMode: ext_DisconnectMode;
  var custom: PSafeArray);
begin
  if Assigned(FExcelApp) then
    FreeAndNil(FExcelApp);
  if Assigned(FOutlookApp) then
    FreeAndNil(FOutlookApp);
  if Assigned(FWordApp) then
    FreeAndNil(FWordApp);
end;

procedure TOfficeAddIn.OnStartupComplete(var custom: PSafeArray);
begin
  // nothing to be done in the base class
  // descending classes should initialize itself here
end;

end.


A SAMPLE USING THIS BASE UNIT
=============================

This unit overrides both classes. The first class is TSimpleOfficeButton. This class implements one procedure only. As far as I know, you will have to do this for every button class you want to create.

Following you see the most important part:

  cServerData: TServerData = (
    ClassID: '{374BC1D3-4C87-4F46-82DC-623C1B74BCE5}';
    IntfIID: '{000C030E-0000-0000-C000-000000000046}';
    EventIID: '{000C0351-0000-0000-C000-000000000046}';
    LicenseKey: nil;
    Version: 100
  );

Note: You will have to create a unique GUID for every Button Class implementation you create for the ClassID, only. You can do this by pressing Ctrl+Shift+G within your Delphi editor. Remove the square braquets!

The GUIDs for the IntfIID and EventIID are the MS GUIDs for the CommandBarButton and the CommandBarButtonEvents interfaces. Do not change those.

The second class overrides the Add-In class and implements the business logic of our add-in. In our case, it will load (create if running for the first time) a command bar and load a simple button within it.

When the user presses the button, a pop up message appears returning the name of the currently active document.

CREATING THE EXAMPLE
====================

Start Delphi and close all open documents. From the "File|New..." menu point go to the ActiveX tab and select ActiveX Library. Save the project under SimpleWordAddin. Next create an Active Server Object, from the ActiveX Library tab, too. A dialog will show. Set the CoClass name to TestIt, emove the check mark from the "Generate ..." check box and set the Active Server Type to "Object Context".

Save the unit as uTestIt.

Paste the code from below into the newly created unit.


unit uTestIt;

interface

uses
  ComObj, ActiveX, AspTlb, SimpleWordAddin_TLB, StdVcl, uOfficePlugin,
  oleServer,
  AddInDesignerObjects_TLB, Office_TLB;

type
  TSimpleOfficeButton = class(TOfficeButton)
  private
  protected
    procedure InitServerData; override;
  public
  end;

  TTestIt = class(TOfficeAddIn, IDTExtensibility2, ITestIt)
  private
    FCommandBar: CommandBar;
    FSimpleButton: TOfficeButton;
    procedure SimpleButtonClick(
      const Control: OleVariant; var CancelDefault: OleVariant
    );
  protected
    procedure OnStartupComplete(var custom: PSafeArray); override; safecall;
    procedure OnBeginShutdown(var custom: PSafeArray); override; safecall;
  public
    procedure Initialize; override;
  end;

implementation

uses
  ComServ, Dialogs, SysUtils
  {$IFDEF VER140}
  , OleCtrls, Variants
  {$ENDIF}
  ;

{ TSimpleOfficeButton }

procedure TSimpleOfficeButton.InitServerData;
const
  cServerData: TServerData = (
    ClassID: '{374BC1D3-4C87-4F46-82DC-623C1B74BCE5}';
    IntfIID: '{000C030E-0000-0000-C000-000000000046}';
    EventIID: '{000C0351-0000-0000-C000-000000000046}';
    LicenseKey: nil;
    Version: 100
  );
begin
  ServerData := @cServerData;
end;

{ TTestIt }

procedure TTestIt.Initialize;
begin
  inherited Initialize;
  OfficeButtonClass := TSimpleOfficeButton;
end;

procedure TTestIt.OnBeginShutdown(var custom: PSafeArray);
begin
  inherited OnBeginShutdown(custom);
  // free the taken resources
  if FCommandBar <> nil then
  begin
    FCommandBar.Delete;
    FCommandBar := nil;
  end;
  if FSimpleButton <> nil then
    FreeAndNil(FSimpleButton);
end;

procedure TTestIt.OnStartupComplete(var custom: PSafeArray);
begin
  inherited OnStartupComplete(custom);
  // create the command bar for word
  FCommandBar := GetCommandBar(WordApp.CommandBars, 'DelphiTestBar', True);
  if FCommandBar <> nil then
  begin
    // create the command bar button
    FSimpleButton := GetOfficeButton(
      FCommandBar, 'SimpleButton', True, SimpleButtonClick, 'S&imple Button'
    );
  end;
end;

procedure TTestIt.SimpleButtonClick(const Control: OleVariant;
  var CancelDefault: OleVariant);
begin
  // response to the command bar button click event
  ShowMessage('Current Document: ' + WordApp.ActiveDocument.Name);
end;

initialization
  TAutoObjectFactory.Create(ComServer, TTestIt, Class_TestIt,
    ciMultiInstance, tmApartment);
end.



REGISTERING YOUR ADD-IN WITH WORD
=================================

After compiling your sample, register your add-in with the "Run|Register ActiveX Server" from the Delphi menu.

Next you have to go into the Registry, using the RegEdit.exe application, installed with Windows.

Go to the key:
HKEY_CURRENT_USER\Software\Microsoft\Office\Word\Addins

Create a new sub-key  (ServerName.InterfaceName):
SimpleWordAddin.TestIt

Within the new sub-key create a dword-value named:
LoadBehavior
and set its value to 3

The value for the load behavior is a combination defined as follows:
$00 - Do not load (disconnected)
$01 - Load (Connected)
$02 - Load with Office Application
$08 - Load on Demand
$16 - Load only next time the Office Application starts

Good luck,
Daniel