By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,011 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

store the mails from Outlook database to Local database

prabunewindia
100+
P: 199
Hello everybody,
here i am going to explain, how to get mails from Outlook express database and store in our own database(local)

Initially you have to add the refference Outlook library10.0 or 11.0 from add ref.

If you not find that, download the dll from here and install..click here

open and windows application (or ASP.NET)
in Form1 class code(declare) the following,

Expand|Select|Wrap|Line Numbers
  1. Microsoft.Office.Interop.Outlook.Application outlk = new Microsoft.Office.Interop.Outlook.ApplicationClass();
  2.         MailItem t;
  3.         string constr = "Data Source=.\\SQLEXPRESS;Initial Catalog=backstage;Integrated Security=True";
  4.         SqlDataAdapter da = new SqlDataAdapter("select * from mail", "Data Source=.\\SQLEXPRESS;Initial Catalog=backstage;Integrated Security=True");
  5.         SqlDataAdapter da2 = new SqlDataAdapter("select * from attachment", "Data Source=.\\SQLEXPRESS;Initial Catalog=backstage;Integrated Security=True");
  6.         DataSet ds = new DataSet();
  7.         DataSet ds2 = new DataSet();

then,

in the button click event code this,

Expand|Select|Wrap|Line Numbers
  1. private void button1_Click(object sender, EventArgs e)
  2.         {
  3.             getOutlook();
  4.  
  5.         }
  6.         public void getOutlook()
  7.         {
  8.             SqlCommandBuilder cb = new SqlCommandBuilder(da);
  9.             da.UpdateCommand = cb.GetUpdateCommand();
  10.             SqlCommandBuilder cb2 = new SqlCommandBuilder(da2);
  11.             da2.UpdateCommand = cb2.GetUpdateCommand();
  12.             da.Fill(ds);
  13.             da2.Fill(ds2);
  14.             NameSpace NS = outlk.GetNamespace("MAPI");
  15.             MAPIFolder inboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderInbox);
  16.             getFolderMail(inboxFld, "Inbox");
  17.             MAPIFolder junkFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderJunk);
  18.             getFolderMail(junkFld, "Junk");
  19.             MAPIFolder sentFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderSentMail);
  20.             getFolderMail(sentFld, "Sent");
  21.             MAPIFolder outboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderOutbox);
  22.             getFolderMail(outboxFld, "Outbox");
  23.             MAPIFolder draftFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDrafts);
  24.             getFolderMail(draftFld, "Draft");
  25.             MAPIFolder deleteFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems);
  26.             getFolderMail(deleteFld, "Delete");
  27.  
  28.         }
  29.  
  30. public void getFolderMail(MAPIFolder folder, string foldername)
  31.         {
  32.  
  33.             int mailID = 0;
  34.             for (int i = 1; i <= folder.Items.Count; i++)
  35.             {
  36.                 try
  37.                 {
  38.                     DataRow dr = ds.Tables[0].NewRow();
  39.                     t = (MailItem)folder.Items[i];
  40.  
  41.                     int size = t.Size / 1000;
  42.                     string sizeinK = size.ToString() + "K";
  43.                     dr["folder"] = foldername;
  44.                     dr["fromName"] = t.SenderName;
  45.                     dr["fromID"] = t.SenderEmailAddress;
  46.                     dr["toName"] = t.ReceivedByName;
  47.                     dr["toID"] = t.To;
  48.                     dr["cc"] = t.CC;
  49.                     dr["bcc"] = t.BCC;
  50.                     dr["subject"] = t.Subject;
  51.                     dr["body"] = t.Body;
  52.                     dr["date"] = t.SentOn ;
  53.                     dr["attachment"] = t.Attachments.Count;
  54.                     dr["size"] = sizeinK;
  55.                     dr["readStatus"] = t.UnRead;
  56.  
  57.                     ds.Tables [0].Rows.Add(dr);
  58.                     da.Update(ds);
  59.                     if (t.Attachments.Count > 0)
  60.                     {
  61.                         mailID = getMailID();
  62.                         for (int j = 1; j <= t.Attachments.Count; j++)
  63.                         {
  64.                             DataRow dra = ds2.Tables[0].NewRow();
  65.  
  66.                             dra["mailID"] = mailID;
  67.  
  68.                             dra["Name"] = t.Attachments[j].DisplayName;
  69.                             string filePath =Path.GetDirectoryName(System.Windows.Forms.Application.StartupPath ) +t.Attachments[j].FileName;  // @"G:/prabu"
  70.                             t.Attachments[j].SaveAsFile(filePath);
  71.                             FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
  72.                             int length = (int)fs.Length;
  73.                             byte[] content = new byte[length];
  74.                             fs.Read(content, 0, length);
  75.                             dra["contents"] = content;
  76.                             dra["contentSize"] = length;
  77.  
  78.                             fs.Close();
  79.                             FileInfo f = new FileInfo(filePath);
  80.                             f.Delete();
  81.                             ds2.Tables[0].Rows.Add(dra);
  82.                             da2.Update(ds2);
  83.                         }
  84.                     }
  85.                 }
  86.                 catch (System .Exception  ex)
  87.                 { 
  88.  
  89.                 }
  90.  
  91.             }
  92.         }
  93.  
  94.  public int getMailID()
  95.         {
  96.             int mailID = 0;
  97.             SqlDataAdapter da1 = new SqlDataAdapter("select max(mailID) as newMailID from mail", constr);
  98.             DataSet ds1 = new DataSet();
  99.             da1.Fill(ds1);
  100.             foreach (DataRow dr in ds1.Tables[0].Rows)
  101.             {
  102.                 mailID =Convert .ToInt32 ( dr["newMailID"]);
  103.             }
  104.             return mailID;
  105.         }

ok lets i explain you why i coded the above,
ok.. everyone know what i coded in the Form1 class. Thats i am created instance for the classes, i am going to use.

Oh.. i forgot to tell about my table design. I created 2 tables. One for Mail and other for Attachment
Mail table have the field mailID as Identity(AutoIncrement) field, but not as primary key. The primary key is combination of (fromID and Date). So we can avoid the mail storing when we fetching second time.

Ok, Now to the code
see the getOutlook() method, I am creating instance for each folder and fetching the mail from that by calling the method "getFolderMail(MAPIFolder folder, string foldername)"
I passed 2 parameters
1. MAPIFolder to indicate the folder
2. Folder name string. I stored this in mail table as one field to filter the mail while display

I am getting all Datas for each mail and storing it in database. After that i am gettting max(mailID), thats nothing but the current one's mailID to store the attachment(mailID is used as foreign key to get the attachment)
Attachments having a property "SaveAsFile(String Filepath)". So i am saving it in one location and converting into byte array and storing into database
Now the mails in Outlook database will be in our table.
So we can use it to Display in our web page or window page.

Rajendra Prabu E,
Programmer,
Grove Ltd,
Kochi,Kerala,
India.
May 25 '07 #1
Share this Article
Share on Google+
4 Comments


P: 1
Hi,
I am working on the similar requirement. We are using outlook 2000 and Sql server 2000. Let me know that whether the same code will work for my requirement. What are dll we need refer for the project. And explain how to add those dll to the project in detailed manner.

Thanks in advance

Regards,
Prasanna
Jun 8 '07 #2

prabunewindia
100+
P: 199
hi friend,
better u can install office 2003(outlook 2003)
because when i install the dll required for this project it shown a messege to install office 2003.
so u can install office 2003
Then sql version is not a problem.
u can use the same code(modify some if need)
to add ref. i explained it in article itself.
also i gave one link to download the dll(setup)

Prabu
Jun 12 '07 #3

P: 2
Hi,
Thanks for the clues ... I developed a more complete solution as below

- generates database programmatically.
- trigger procedure in SQL to prevent duplicate mail importing
- updates mail count and attachment count.
- reference key between mail and attachment table
- the database is called mailborg3 ... ;-)
- there is also a simple sql query facility.
eg
USE mailborg3;
SELECT * FROM mail;

- you need a form with an import button, query button, dbinitialise button,
text box ( for sql entries ), datagrid ( for query results ), mailcount textbox,
attachmentcount textbox.

- developed on SQLExpress and C# Express 2008. Thanks to Microsoft corp.
- tested on outlook 2003.

Enjoy.


Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.IO;  //req for file operations
  3. using System.Collections.Generic;
  4. using System.ComponentModel;
  5. using System.Data;
  6. using System.Drawing;
  7. using System.Linq;
  8. using System.Text;
  9. using System.Windows.Forms;
  10.  
  11. using Microsoft.Office.Interop.Outlook;
  12.  
  13. using System.Data.SqlClient;
  14.  
  15.  
  16. namespace mailborg3
  17. {
  18.     public partial class Form1 : Form
  19.     {
  20.  
  21.         Microsoft.Office.Interop.Outlook.Application outlk = new Microsoft.Office.Interop.Outlook.ApplicationClass();
  22.         MailItem t;
  23.         string constr = "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True";
  24.         SqlDataAdapter da = new SqlDataAdapter("select * from mail", "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True");
  25.         SqlDataAdapter da2 = new SqlDataAdapter("select * from attachment", "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True");
  26.         DataSet ds = new DataSet();
  27.         DataSet ds2 = new DataSet();
  28.  
  29.         private MAPIFolder selectedFolder = null;
  30.  
  31.         public Form1()
  32.         {
  33.             InitializeComponent();
  34.         }
  35.  
  36.         private void button1_Click(object sender, EventArgs e)
  37.         {
  38.             getOutlook();
  39.         }
  40.         public void getOutlook()
  41.         {
  42.             SqlCommandBuilder cb = new SqlCommandBuilder(da);
  43.             da.UpdateCommand = cb.GetUpdateCommand();
  44.             SqlCommandBuilder cb2 = new SqlCommandBuilder(da2);
  45.             da2.UpdateCommand = cb2.GetUpdateCommand();
  46.             da.Fill(ds);
  47.             da2.Fill(ds2);
  48.             NameSpace NS = outlk.GetNamespace("MAPI");
  49.  
  50.             selectedFolder = NS.PickFolder();
  51.             getFolderMail(selectedFolder, selectedFolder.Name);
  52.  
  53.             //MAPIFolder inboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderInbox  );
  54.             //getFolderMail(inboxFld, "Inbox");
  55.             //MAPIFolder junkFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderJunk)  ;
  56.             //getFolderMail(junkFld, "Junk");
  57.             //MAPIFolder sentFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderSentMail);
  58.             //getFolderMail(sentFld, "Sent");
  59.             //MAPIFolder outboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderOutbox);
  60.             //getFolderMail(outboxFld, "Outbox");
  61.             //MAPIFolder draftFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDrafts);
  62.             //getFolderMail(draftFld, "Draft");
  63.             //MAPIFolder deleteFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems);
  64.             //getFolderMail(deleteFld, "Delete");
  65.  
  66.         }
  67.  
  68.         public void getFolderMail(MAPIFolder folder, string foldername)
  69.         {
  70.  
  71.  
  72.             int mailID = 0;
  73.             int nAttachCount = 0;
  74.  
  75.             for (int i = 1; i <= folder.Items.Count; i++)
  76.             {
  77.                 System.Windows.Forms.Application.DoEvents();
  78.                 try
  79.                 {
  80.                     DataRow dr = ds.Tables[0].NewRow();
  81.                     t = (MailItem)folder.Items[i];
  82.  
  83.                     int size = t.Size / 1000;
  84.                     string sizeinK = size.ToString() + "K";
  85.                     dr["folder"] = foldername;
  86.                     dr["fromName"] = t.SenderName;
  87.                     dr["fromID"] = t.SenderEmailAddress;
  88.                     dr["toName"] = t.ReceivedByName;
  89.                     dr["toID"] = t.To;
  90.                     dr["cc"] = t.CC;
  91.                     dr["bcc"] = t.BCC;
  92.                     dr["subject"] = t.Subject;
  93.                     dr["body"] = t.Body;
  94.                     dr["date"] = t.SentOn ;
  95.                     dr["attachment"] = t.Attachments.Count;
  96.                     dr["size"] = sizeinK;
  97.                     dr["readStatus"] = t.UnRead;
  98.  
  99.                     ds.Tables [0].Rows.Add(dr);
  100.                     da.Update(ds);
  101.  
  102.                     textBox3.Text = Convert.ToString(i); //update mail count
  103.                     //System.Windows.Forms.Application.DoEvents();
  104.  
  105.                     if (t.Attachments.Count > 0)
  106.                     {
  107.                         mailID = getMailID();
  108.                         for (int j = 1; j <= t.Attachments.Count; j++)
  109.                         {
  110.                             DataRow dra = ds2.Tables[0].NewRow();
  111.  
  112.                             dra["mailID"] = mailID;
  113.  
  114.                             dra["Name"] = t.Attachments[j].DisplayName;
  115.                             string filePath =Path.GetDirectoryName(System.Windows.Forms.Application.StartupPath ) +t.Attachments[j].FileName;  // @"G:/prabu"
  116.                             t.Attachments[j].SaveAsFile(filePath);
  117.                             FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
  118.                             int length = (int)fs.Length;
  119.                             byte[] content = new byte[length];
  120.                             fs.Read(content, 0, length);
  121.                             dra["contents"] = content;
  122.                             dra["contentSize"] = length;
  123.  
  124.                             fs.Close();
  125.                             FileInfo f = new FileInfo(filePath);
  126.                             f.Delete();
  127.                             ds2.Tables[0].Rows.Add(dra);
  128.                             da2.Update(ds2);
  129.  
  130.                             nAttachCount += 1;
  131.                             textBox2.Text = Convert.ToString(nAttachCount); //update attachment count
  132.                             System.Windows.Forms.Application.DoEvents();
  133.                         }
  134.                     }
  135.                 }
  136.                 catch (System .Exception  ex)
  137.                 {
  138.                     Console.WriteLine(ex.ToString());
  139.                 }
  140.  
  141.             }
  142.         }
  143.  
  144.         public int getMailID()
  145.         {
  146.             int mailID = 0;
  147.             SqlDataAdapter da1 = new SqlDataAdapter("select max(mailID) as newMailID from mail", constr);
  148.             DataSet ds1 = new DataSet();
  149.             da1.Fill(ds1);
  150.             foreach (DataRow dr in ds1.Tables[0].Rows)
  151.             {
  152.                 mailID = Convert.ToInt32(dr["newMailID"]);
  153.             }
  154.             return mailID;
  155.         }
  156.  
  157.         static void initDB()
  158.         {
  159.             //SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
  160.             SqlConnection thisConnection = new SqlConnection("server=.\\SQLEXPRESS;Integrated Security=True");
  161.             SqlCommand nonqueryCommand = thisConnection.CreateCommand();
  162.  
  163.             try
  164.             {
  165.                 thisConnection.Open();
  166.  
  167.                 nonqueryCommand.CommandText = "DROP DATABASE mailborg3";
  168.                 Console.WriteLine(nonqueryCommand.CommandText);
  169.  
  170.                 nonqueryCommand.ExecuteNonQuery();
  171.                 Console.WriteLine("Existing DataBase Destroyed");
  172.  
  173.                 nonqueryCommand.CommandText = "CREATE DATABASE mailborg3";
  174.                 Console.WriteLine(nonqueryCommand.CommandText);
  175.  
  176.                 nonqueryCommand.ExecuteNonQuery();
  177.                 Console.WriteLine("Database created, now switching");
  178.                 thisConnection.ChangeDatabase("mailborg3");
  179.  
  180.                 nonqueryCommand.CommandText = "CREATE TABLE mail("
  181.                     //+ "pindex INT PRIMARY KEY,"
  182.                     //+ "mailID int REFERENCES attachment(mailID) PRIMARY KEY," // foreign key to attachment DB
  183.                                             + "mailID INT IDENTITY(1,1) PRIMARY KEY,"
  184.                                             + "folder VARCHAR(MAX),"
  185.                                             + "fromName VARCHAR(MAX),"
  186.                                             + "fromID VARCHAR(MAX),"
  187.                                             + "toName VARCHAR(MAX),"
  188.                                             + "toID VARCHAR(MAX),"
  189.                                             + "cc VARCHAR(MAX),"
  190.                                             + "bcc VARCHAR(MAX),"
  191.                                             + "subject VARCHAR(MAX),"
  192.                                             + "body VARCHAR(MAX),"
  193.                                             + "date DATETIME,"
  194.                                             + "attachment INT,"
  195.                                             + "size VARCHAR(MAX),"
  196.                                             + "readStatus BIT"
  197.                                             + ")";
  198.                 Console.WriteLine(nonqueryCommand.CommandText);
  199.                 Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
  200.                     //+ " GO"
  201.                 nonqueryCommand.CommandText = " CREATE TRIGGER trgDateTimeUNQ"
  202.                                             + " ON mail FOR INSERT, UPDATE"
  203.                                             + " AS"
  204.                                             + " IF EXISTS(SELECT I.date"
  205.                                             + " FROM inserted AS I JOIN mail AS C"
  206.                                             + " ON I.date = C.date"
  207.                                             + " WHERE I.date <> ''"
  208.                                             + " GROUP BY I.date"
  209.                                             + " HAVING COUNT(*) > 1)"
  210.                                             + " BEGIN"
  211.                                             + "  RAISERROR('Duplicates found. Transaction rolled back.', 10, 1)"
  212.                                             + "  ROLLBACK TRAN"
  213.                                             + " END";
  214.                                             //+ " GO";
  215.  
  216.  
  217.                 Console.WriteLine(nonqueryCommand.CommandText);
  218.                 Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
  219.  
  220.  
  221.  
  222.                 nonqueryCommand.CommandText = "CREATE TABLE attachment("
  223.                                             + "pindex INT IDENTITY(1,1) PRIMARY KEY,"
  224.                                             + "mailID int REFERENCES mail(mailID)," // foreign key to mail DB
  225.                                             //+ "mailID INT PRIMARY KEY,"
  226.                                             + "Name VARCHAR(MAX),"
  227.                                             + "contents VARCHAR(MAX),"
  228.                                             + "contentSize VARCHAR(MAX)"
  229.                                             + ")";
  230.                 Console.WriteLine(nonqueryCommand.CommandText);
  231.                 Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
  232.  
  233.  
  234.  
  235.  
  236.  
  237.                 //nonqueryCommand.CommandText = "INSERT INTO mailID VALUES (99)";
  238.                 //Console.WriteLine(nonqueryCommand.CommandText);
  239.                 //Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
  240.  
  241.                 }
  242.                 catch (SqlException ex)
  243.                 {
  244.  
  245.                     Console.WriteLine(ex.ToString());
  246.  
  247.                 }
  248.                 finally
  249.                 {
  250.  
  251.                     thisConnection.Close();
  252.                     Console.WriteLine("Connection Closed.");
  253.  
  254.                 }
  255.             }
  256.  
  257.  
  258.  
  259.         private void Form1_Load(object sender, EventArgs e)
  260.         {
  261.  
  262.         }
  263.  
  264.         private void button2_Click(object sender, EventArgs e)
  265.         {
  266.             initDB();
  267.         }
  268.  
  269.         private void button3_Click(object sender, EventArgs e)
  270.         {
  271.  
  272.             SqlConnection thisConnection = new SqlConnection("server=.\\SQLEXPRESS;Integrated Security=True");
  273.             //create a new sql command object of type dynamic sql 
  274.             SqlCommand cmd = thisConnection.CreateCommand(); 
  275.             cmd.CommandType = CommandType.Text;
  276.             //textBox1.Text = "";
  277.             dataGrid1.DataSource = null;
  278.             dataGrid1.DataMember = null;
  279.  
  280.             try
  281.             {
  282.                 thisConnection.Open();
  283.  
  284.                 //set the sql command to whatever is in the textbox 
  285.                 cmd.CommandText = textBox1.Text;
  286.  
  287.                 //create a new data adapter and bind it to the command obejct 
  288.                 SqlDataAdapter daQuery = new SqlDataAdapter();
  289.                 daQuery.SelectCommand = cmd;
  290.  
  291.                 //create a new dataset and fill it with results based adapter 
  292.                 DataSet dsQuery = new DataSet();
  293.                 daQuery.Fill(dsQuery, "Results");
  294.  
  295.                 //bind the local variable dataset to the datagrid on the form 
  296.                 dataGrid1.DataSource = dsQuery;
  297.                 dataGrid1.DataMember = "Results";
  298.             }
  299.             catch (SqlException ex)
  300.             {
  301.  
  302.                 Console.WriteLine(ex.ToString());
  303.  
  304.             }
  305.             catch (System.Exception ee)
  306.             {
  307.                 Console.WriteLine(ee.ToString());
  308.             }
  309.  
  310.                 finally
  311.                 {
  312.  
  313.                     thisConnection.Close();
  314.                     Console.WriteLine("Connection Closed.");
  315.  
  316.                 }
  317.         }
  318.  
  319.         private void textBox1_TextChanged(object sender, EventArgs e)
  320.         {
  321.  
  322.         }
  323.  
  324.         private void textBox2_TextChanged(object sender, EventArgs e)
  325.         {
  326.  
  327.         }
  328.     }
  329. }
  330.  
Nov 27 '07 #4

P: 2
Hi,
Can i store email in SQL- Database from Web-mail using C#



Rahul Kale
Mar 24 '08 #5