Microsoft Outlook Add-in With DialogBox and Database Connectivity

• Download source code - 142 Kb

Pre-Requisites

Please do read my previous tutorials on add-ins, to proceed. ADODB concepts should also be known before proceeding.

Introduction

Previously, I had published two tutorials on add-ins, and now it is time to move further deep, that is using other techniques. In this tutorial, we are going to have some details added in an Access database, view and add details in that database through add-in. We use ADODB connectivity.

Since this tutorial shows how to perform reading and finding a specific value in the database, the same can be applied for deletion and modification. Maybe it will be added soon. This tutorial also tells you how to invoke Inspector Events.

ADODB

The ADODB library is a small, lightweight library that contains core objects, and offers the basics for making connections, issuing commands, and retrieving recordsets, and it also enables recordset navigation. You can use it to perform basic maintenance tasks, such as modifying, adding, and deleting records. The nonhierarchical design of this library makes it easy for beginners to use.

Using The Code

Database

The database is implemented in CMyDatabase class.

1. To use ADODB Connection, first we have to import ADODB into our program, and so the line responsible for this is:

   #import "C:\Program Files\Common Files\System\ADO\msado15.dll " 
   no_namespace name ("EOF","EndOfFile")

   The path may vary from system to system. Search for msado15.dll and put the path there if there's an error.

2. Two basic things needed in ADODB is the Connection Pointer and the RecordSet Pointer.

   _ConnectionPtr m_pCon;
   _RecordsetPtr m_pRs;

3. Now we have to establish a connection to our database. The GetDefaultConnect() does this.

   void CMyDatabase::GetDefaultConnect()
   {
       //create an instance for ADODB connection
       HRESULT hr=m_pCon.CreateInstance("ADODB.Connection");
   
       if( SUCCEEDED(hr) )
       {
           //open the Database
           hr=m_pCon->Open("Provider=Microsoft.Jet.OLEDB.4.0;
                    Data Source=C:\\Address.mdb;", "","",adModeUnknown);
       }
   }

4. So now after opening, we can read the values as follows:

   void CMyDatabase::Read(CStringArray* values)
   {
       //connect the database
       GetDefaultConnect();
   
       //variables
       _bstr_t bstrQuery;
       _variant_t isAffected;
       _variant_t vEmail;
       _variant_t vName;
   
       values->RemoveAll();
   
       //the query we want to execute
       bstrQuery="SELECT * FROM Addr";
   
       //Execute the query and it will return 
       //a RecordSet so that we can scroll
       //through the result one by one
       m_pRs=m_pCon->Execute(_bstr_t(bstrQuery), &isAffected, 
                                           adOptionUnspecified);
   
       //traverse the recordset till the end of file
       while( !m_pRs->GetEndOfFile() )
       {
           //collect the values by the order 
           //of the field names (i.e)Coulmn names
           vEmail=m_pRs->GetCollect(L"Email");
           vName=m_pRs->GetCollect(L"Name");
   
           //store them in stringarray
           values->Add( (char*) ((_bstr_t)vEmail) );
           values->Add( (char*) ((_bstr_t)vName) );
   
           //move to next record
            m_pRs->MoveNext();
       }
   
       //close the recordset
       m_pRs->Close();
   }

5. Adding a new data to the database is as follows:

   void CMyDatabase::Add(PSTR pMail, PSTR pName)
   {
       //Connect to the database
       GetDefaultConnect();
   
       //variables
       _variant_t vNull;
       vNull.vt=VT_ERROR;
       vNull.scode=DISP_E_PARAMNOTFOUND;
       _bstr_t bstrQuery("SELECT * FROM Addr");
       long rIndices[2];
   
       //create the recordset
       m_pRs.CreateInstance(__uuidof(Recordset));
   
       //put our Active Database Connection to our Connection Pointer
       m_pRs->PutRefActiveConnection(m_pCon);
   
       //open the connection successfully with the query
       m_pRs->Open(_variant_t(bstrQuery),vNull,adOpenForwardOnly, 
                                        adLockOptimistic,adCmdText);
   
       //first add the field names which is in our database
       COleSafeArray vaFields;
       vaFields.CreateOneDim(VT_VARIANT,2);
       rIndices[0]=0;
       vaFields.PutElement(rIndices,&(_variant_t("Email")));
       rIndices[0]=1;
       vaFields.PutElement(rIndices,&(_variant_t("Name")));
   
       //next add the values for the above added field names respectively
       COleSafeArray vaValues;
       vaValues.CreateOneDim(VT_VARIANT,2);
       rIndices[0]=0;
       vaValues.PutElement(rIndices,&(_variant_t((CString)pMail)));
       rIndices[0]=1;
       vaValues.PutElement(rIndices,&(_variant_t((CString)pName)));
   
       //now add the field name and it's respective value to the database
       m_pRs->AddNew(vaFields,vaValues);
   
       //close the connection
       m_pCon->Close();
   
       AfxMessageBox("Details Added");
   }

6. If you change the query with "WHERE" option, you can perform the search operation.

Inspector Event

There are two windows in Microsoft Outlook:

1. Explorer window
2. Inspector window

Both have their separate callbacks and events. Once a mail is clicked in Inbox or Outbox, the window which opens is called as the Inspector window.

Just think how nice it would be to track this New Inspector Event??

The procedure is as follows:

1. Our add-in class should implement the InspectorEvents to track them, so add this line in the Implements part:

   CAddin :
       public IDispEventSimpleImpl<1,CAddin,&__uuidof(Outlook::InspectorsEvents)>

2. The job is made simpler by typedef:

   typedef IDispEventSimpleImpl</*nID =*/ 1,CAddin, 
       &__uuidof(Outlook::InspectorsEvents)> MyInspectorsEvents;

3. Add it in the Sink Entry:

   BEGIN_SINK_MAP(CAddin)
     SINK_ENTRY_INFO(1, __uuidof(Outlook::InspectorsEvents), 
         /*dispid*/ 0xf001, NewInspector, &OnInspectorInfo)

   The OnInspector function is an external function which is going to hold the information about our Inspector Function, and is written as:

   extern _ATL_FUNC_INFO OnInspectorInfo;

   NewInspector is our actual function which will be invoked when a New Inspector event occurs.

4. From our Application Object, we get the Inspectors object:

   spApp->get_Inspectors(&spInspectors);

5. Finally, advise the Inspector event:

   HRESULT hr=MyInspectorsEvents::DispEventAdvise((IDispatch*)spInspectors);

6. The event function info is as follows:

   _ATL_FUNC_INFO OnInspectorInfo ={CC_STDCALL,VT_EMPTY,1,{VT_DISPATCH}};

7. The event function is as follows:

   void __stdcall CAddin::NewInspector(IDispatch* pdispInspector)
   {
     AfxMessageBox("New Inspector Event Occured");
   }

That's it!...we have completed another tutorial with database connectivity!

Reference

Microsoft Developer Network (MSDN)

My Contact

chakkaradeepcc@yahoo.com

Note

This tutorial was checked in Office XP, and it works in Office 2000 if the import files are changed as given in previous tutorials. I can't guarantee whether it works in Office 2003.

If somebody has an add-in written for Office 2003, please do send it to me. I tried a tutorial given in CodeProject but it is not working.!!!!