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, - Microsoft.Office.Interop.Outlook.Application outlk = new Microsoft.Office.Interop.Outlook.ApplicationClass();
-
MailItem t;
-
string constr = "Data Source=.\\SQLEXPRESS;Initial Catalog=backstage;Integrated Security=True";
-
SqlDataAdapter da = new SqlDataAdapter("select * from mail", "Data Source=.\\SQLEXPRESS;Initial Catalog=backstage;Integrated Security=True");
-
SqlDataAdapter da2 = new SqlDataAdapter("select * from attachment", "Data Source=.\\SQLEXPRESS;Initial Catalog=backstage;Integrated Security=True");
-
DataSet ds = new DataSet();
-
DataSet ds2 = new DataSet();
then,
in the button click event code this, - private void button1_Click(object sender, EventArgs e)
-
{
-
getOutlook();
-
-
}
-
public void getOutlook()
-
{
-
SqlCommandBuilder cb = new SqlCommandBuilder(da);
-
da.UpdateCommand = cb.GetUpdateCommand();
-
SqlCommandBuilder cb2 = new SqlCommandBuilder(da2);
-
da2.UpdateCommand = cb2.GetUpdateCommand();
-
da.Fill(ds);
-
da2.Fill(ds2);
-
NameSpace NS = outlk.GetNamespace("MAPI");
-
MAPIFolder inboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderInbox);
-
getFolderMail(inboxFld, "Inbox");
-
MAPIFolder junkFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderJunk);
-
getFolderMail(junkFld, "Junk");
-
MAPIFolder sentFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderSentMail);
-
getFolderMail(sentFld, "Sent");
-
MAPIFolder outboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderOutbox);
-
getFolderMail(outboxFld, "Outbox");
-
MAPIFolder draftFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDrafts);
-
getFolderMail(draftFld, "Draft");
-
MAPIFolder deleteFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems);
-
getFolderMail(deleteFld, "Delete");
-
-
}
-
-
public void getFolderMail(MAPIFolder folder, string foldername)
-
{
-
-
int mailID = 0;
-
for (int i = 1; i <= folder.Items.Count; i++)
-
{
-
try
-
{
-
DataRow dr = ds.Tables[0].NewRow();
-
t = (MailItem)folder.Items[i];
-
-
int size = t.Size / 1000;
-
string sizeinK = size.ToString() + "K";
-
dr["folder"] = foldername;
-
dr["fromName"] = t.SenderName;
-
dr["fromID"] = t.SenderEmailAddress;
-
dr["toName"] = t.ReceivedByName;
-
dr["toID"] = t.To;
-
dr["cc"] = t.CC;
-
dr["bcc"] = t.BCC;
-
dr["subject"] = t.Subject;
-
dr["body"] = t.Body;
-
dr["date"] = t.SentOn ;
-
dr["attachment"] = t.Attachments.Count;
-
dr["size"] = sizeinK;
-
dr["readStatus"] = t.UnRead;
-
-
ds.Tables [0].Rows.Add(dr);
-
da.Update(ds);
-
if (t.Attachments.Count > 0)
-
{
-
mailID = getMailID();
-
for (int j = 1; j <= t.Attachments.Count; j++)
-
{
-
DataRow dra = ds2.Tables[0].NewRow();
-
-
dra["mailID"] = mailID;
-
-
dra["Name"] = t.Attachments[j].DisplayName;
-
string filePath =Path.GetDirectoryName(System.Windows.Forms.Application.StartupPath ) +t.Attachments[j].FileName; // @"G:/prabu"
-
t.Attachments[j].SaveAsFile(filePath);
-
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
-
int length = (int)fs.Length;
-
byte[] content = new byte[length];
-
fs.Read(content, 0, length);
-
dra["contents"] = content;
-
dra["contentSize"] = length;
-
-
fs.Close();
-
FileInfo f = new FileInfo(filePath);
-
f.Delete();
-
ds2.Tables[0].Rows.Add(dra);
-
da2.Update(ds2);
-
}
-
}
-
}
-
catch (System .Exception ex)
-
{
-
-
}
-
-
}
-
}
-
-
public int getMailID()
-
{
-
int mailID = 0;
-
SqlDataAdapter da1 = new SqlDataAdapter("select max(mailID) as newMailID from mail", constr);
-
DataSet ds1 = new DataSet();
-
da1.Fill(ds1);
-
foreach (DataRow dr in ds1.Tables[0].Rows)
-
{
-
mailID =Convert .ToInt32 ( dr["newMailID"]);
-
}
-
return mailID;
-
}
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.
4  16650 
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
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
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. -
using System;
-
using System.IO; //req for file operations
-
using System.Collections.Generic;
-
using System.ComponentModel;
-
using System.Data;
-
using System.Drawing;
-
using System.Linq;
-
using System.Text;
-
using System.Windows.Forms;
-
-
using Microsoft.Office.Interop.Outlook;
-
-
using System.Data.SqlClient;
-
-
-
namespace mailborg3
-
{
-
public partial class Form1 : Form
-
{
-
-
Microsoft.Office.Interop.Outlook.Application outlk = new Microsoft.Office.Interop.Outlook.ApplicationClass();
-
MailItem t;
-
string constr = "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True";
-
SqlDataAdapter da = new SqlDataAdapter("select * from mail", "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True");
-
SqlDataAdapter da2 = new SqlDataAdapter("select * from attachment", "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True");
-
DataSet ds = new DataSet();
-
DataSet ds2 = new DataSet();
-
-
private MAPIFolder selectedFolder = null;
-
-
public Form1()
-
{
-
InitializeComponent();
-
}
-
-
private void button1_Click(object sender, EventArgs e)
-
{
-
getOutlook();
-
}
-
public void getOutlook()
-
{
-
SqlCommandBuilder cb = new SqlCommandBuilder(da);
-
da.UpdateCommand = cb.GetUpdateCommand();
-
SqlCommandBuilder cb2 = new SqlCommandBuilder(da2);
-
da2.UpdateCommand = cb2.GetUpdateCommand();
-
da.Fill(ds);
-
da2.Fill(ds2);
-
NameSpace NS = outlk.GetNamespace("MAPI");
-
-
selectedFolder = NS.PickFolder();
-
getFolderMail(selectedFolder, selectedFolder.Name);
-
-
//MAPIFolder inboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderInbox );
-
//getFolderMail(inboxFld, "Inbox");
-
//MAPIFolder junkFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderJunk) ;
-
//getFolderMail(junkFld, "Junk");
-
//MAPIFolder sentFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderSentMail);
-
//getFolderMail(sentFld, "Sent");
-
//MAPIFolder outboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderOutbox);
-
//getFolderMail(outboxFld, "Outbox");
-
//MAPIFolder draftFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDrafts);
-
//getFolderMail(draftFld, "Draft");
-
//MAPIFolder deleteFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDeletedItems);
-
//getFolderMail(deleteFld, "Delete");
-
-
}
-
-
public void getFolderMail(MAPIFolder folder, string foldername)
-
{
-
-
-
int mailID = 0;
-
int nAttachCount = 0;
-
-
for (int i = 1; i <= folder.Items.Count; i++)
-
{
-
System.Windows.Forms.Application.DoEvents();
-
try
-
{
-
DataRow dr = ds.Tables[0].NewRow();
-
t = (MailItem)folder.Items[i];
-
-
int size = t.Size / 1000;
-
string sizeinK = size.ToString() + "K";
-
dr["folder"] = foldername;
-
dr["fromName"] = t.SenderName;
-
dr["fromID"] = t.SenderEmailAddress;
-
dr["toName"] = t.ReceivedByName;
-
dr["toID"] = t.To;
-
dr["cc"] = t.CC;
-
dr["bcc"] = t.BCC;
-
dr["subject"] = t.Subject;
-
dr["body"] = t.Body;
-
dr["date"] = t.SentOn ;
-
dr["attachment"] = t.Attachments.Count;
-
dr["size"] = sizeinK;
-
dr["readStatus"] = t.UnRead;
-
-
ds.Tables [0].Rows.Add(dr);
-
da.Update(ds);
-
-
textBox3.Text = Convert.ToString(i); //update mail count
-
//System.Windows.Forms.Application.DoEvents();
-
-
if (t.Attachments.Count > 0)
-
{
-
mailID = getMailID();
-
for (int j = 1; j <= t.Attachments.Count; j++)
-
{
-
DataRow dra = ds2.Tables[0].NewRow();
-
-
dra["mailID"] = mailID;
-
-
dra["Name"] = t.Attachments[j].DisplayName;
-
string filePath =Path.GetDirectoryName(System.Windows.Forms.Application.StartupPath ) +t.Attachments[j].FileName; // @"G:/prabu"
-
t.Attachments[j].SaveAsFile(filePath);
-
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
-
int length = (int)fs.Length;
-
byte[] content = new byte[length];
-
fs.Read(content, 0, length);
-
dra["contents"] = content;
-
dra["contentSize"] = length;
-
-
fs.Close();
-
FileInfo f = new FileInfo(filePath);
-
f.Delete();
-
ds2.Tables[0].Rows.Add(dra);
-
da2.Update(ds2);
-
-
nAttachCount += 1;
-
textBox2.Text = Convert.ToString(nAttachCount); //update attachment count
-
System.Windows.Forms.Application.DoEvents();
-
}
-
}
-
}
-
catch (System .Exception ex)
-
{
-
Console.WriteLine(ex.ToString());
-
}
-
-
}
-
}
-
-
public int getMailID()
-
{
-
int mailID = 0;
-
SqlDataAdapter da1 = new SqlDataAdapter("select max(mailID) as newMailID from mail", constr);
-
DataSet ds1 = new DataSet();
-
da1.Fill(ds1);
-
foreach (DataRow dr in ds1.Tables[0].Rows)
-
{
-
mailID = Convert.ToInt32(dr["newMailID"]);
-
}
-
return mailID;
-
}
-
-
static void initDB()
-
{
-
//SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
-
SqlConnection thisConnection = new SqlConnection("server=.\\SQLEXPRESS;Integrated Security=True");
-
SqlCommand nonqueryCommand = thisConnection.CreateCommand();
-
-
try
-
{
-
thisConnection.Open();
-
-
nonqueryCommand.CommandText = "DROP DATABASE mailborg3";
-
Console.WriteLine(nonqueryCommand.CommandText);
-
-
nonqueryCommand.ExecuteNonQuery();
-
Console.WriteLine("Existing DataBase Destroyed");
-
-
nonqueryCommand.CommandText = "CREATE DATABASE mailborg3";
-
Console.WriteLine(nonqueryCommand.CommandText);
-
-
nonqueryCommand.ExecuteNonQuery();
-
Console.WriteLine("Database created, now switching");
-
thisConnection.ChangeDatabase("mailborg3");
-
-
nonqueryCommand.CommandText = "CREATE TABLE mail("
-
//+ "pindex INT PRIMARY KEY,"
-
//+ "mailID int REFERENCES attachment(mailID) PRIMARY KEY," // foreign key to attachment DB
-
+ "mailID INT IDENTITY(1,1) PRIMARY KEY,"
-
+ "folder VARCHAR(MAX),"
-
+ "fromName VARCHAR(MAX),"
-
+ "fromID VARCHAR(MAX),"
-
+ "toName VARCHAR(MAX),"
-
+ "toID VARCHAR(MAX),"
-
+ "cc VARCHAR(MAX),"
-
+ "bcc VARCHAR(MAX),"
-
+ "subject VARCHAR(MAX),"
-
+ "body VARCHAR(MAX),"
-
+ "date DATETIME,"
-
+ "attachment INT,"
-
+ "size VARCHAR(MAX),"
-
+ "readStatus BIT"
-
+ ")";
-
Console.WriteLine(nonqueryCommand.CommandText);
-
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
-
//+ " GO"
-
nonqueryCommand.CommandText = " CREATE TRIGGER trgDateTimeUNQ"
-
+ " ON mail FOR INSERT, UPDATE"
-
+ " AS"
-
+ " IF EXISTS(SELECT I.date"
-
+ " FROM inserted AS I JOIN mail AS C"
-
+ " ON I.date = C.date"
-
+ " WHERE I.date <> ''"
-
+ " GROUP BY I.date"
-
+ " HAVING COUNT(*) > 1)"
-
+ " BEGIN"
-
+ " RAISERROR('Duplicates found. Transaction rolled back.', 10, 1)"
-
+ " ROLLBACK TRAN"
-
+ " END";
-
//+ " GO";
-
-
-
Console.WriteLine(nonqueryCommand.CommandText);
-
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
-
-
-
-
nonqueryCommand.CommandText = "CREATE TABLE attachment("
-
+ "pindex INT IDENTITY(1,1) PRIMARY KEY,"
-
+ "mailID int REFERENCES mail(mailID)," // foreign key to mail DB
-
//+ "mailID INT PRIMARY KEY,"
-
+ "Name VARCHAR(MAX),"
-
+ "contents VARCHAR(MAX),"
-
+ "contentSize VARCHAR(MAX)"
-
+ ")";
-
Console.WriteLine(nonqueryCommand.CommandText);
-
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
-
-
-
-
-
-
//nonqueryCommand.CommandText = "INSERT INTO mailID VALUES (99)";
-
//Console.WriteLine(nonqueryCommand.CommandText);
-
//Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
-
-
}
-
catch (SqlException ex)
-
{
-
-
Console.WriteLine(ex.ToString());
-
-
}
-
finally
-
{
-
-
thisConnection.Close();
-
Console.WriteLine("Connection Closed.");
-
-
}
-
}
-
-
-
-
private void Form1_Load(object sender, EventArgs e)
-
{
-
-
}
-
-
private void button2_Click(object sender, EventArgs e)
-
{
-
initDB();
-
}
-
-
private void button3_Click(object sender, EventArgs e)
-
{
-
-
SqlConnection thisConnection = new SqlConnection("server=.\\SQLEXPRESS;Integrated Security=True");
-
//create a new sql command object of type dynamic sql
-
SqlCommand cmd = thisConnection.CreateCommand();
-
cmd.CommandType = CommandType.Text;
-
//textBox1.Text = "";
-
dataGrid1.DataSource = null;
-
dataGrid1.DataMember = null;
-
-
try
-
{
-
thisConnection.Open();
-
-
//set the sql command to whatever is in the textbox
-
cmd.CommandText = textBox1.Text;
-
-
//create a new data adapter and bind it to the command obejct
-
SqlDataAdapter daQuery = new SqlDataAdapter();
-
daQuery.SelectCommand = cmd;
-
-
//create a new dataset and fill it with results based adapter
-
DataSet dsQuery = new DataSet();
-
daQuery.Fill(dsQuery, "Results");
-
-
//bind the local variable dataset to the datagrid on the form
-
dataGrid1.DataSource = dsQuery;
-
dataGrid1.DataMember = "Results";
-
}
-
catch (SqlException ex)
-
{
-
-
Console.WriteLine(ex.ToString());
-
-
}
-
catch (System.Exception ee)
-
{
-
Console.WriteLine(ee.ToString());
-
}
-
-
finally
-
{
-
-
thisConnection.Close();
-
Console.WriteLine("Connection Closed.");
-
-
}
-
}
-
-
private void textBox1_TextChanged(object sender, EventArgs e)
-
{
-
-
}
-
-
private void textBox2_TextChanged(object sender, EventArgs e)
-
{
-
-
}
-
}
-
}
-
Hi,
Can i store email in SQL- Database from Web-mail using C#
Rahul Kale
Sign in to post your reply or Sign up for a free account.
Similar topics
by: DKode |
last post by:
I will be making a windows app that will run on user's local machines.
It will not be in a networked environment or anything like that which
rules out SQL Server for the database. I am trying to...
|
by: DKode |
last post by:
I will be making a windows app that will run on user's local machines.
It will not be in a networked environment or anything like that which
rules out SQL Server for the database. I am trying to...
|
by: Paul M. |
last post by:
Hi,
Are there any examples of building a simple local database with
C# Express 2005?
TIA
|
by: Grant Merwitz |
last post by:
Hi
I am currently weighing up two options.
I have a data i need available for my website.
However, this data is only available via a web service and too slow to
retrieve on a user request...
|
by: ashutosh11 |
last post by:
Hi all, can anyone please help
I am a new to VBA and need help here. I am trying to store EMAILs depending on the senders Email Domain. I should write a VBA script that will run everytime a new...
|
by: baygan |
last post by:
can anyone plz help me to write a code to read mail from one's inbox and store the mails to the local disk??? i forgot to say it would be preferably in java..
i would be very grateful if someone...
|
by: mouac01 |
last post by:
I'm not sure if this is possible. I would like to have a PHP app on
the Internet connect and write to a local database (Intranet). For
example, users would go to a web site...
|
by: hellboss |
last post by:
hello sir/mam ,
I need to save the attatchments from multiple mails to a local directory. how can i do it , and by which control i can view those file such as .document file !
Expecting ur replies...
|
by: btreddy |
last post by:
Hii all,
I've been searching in the internet just to know abt SMTP server.
Can anybody tell me how to configure the SMTP server and how can i send the mails and how could i check whether...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
