Data from email to SQL 1

[jawan]

I received every day many emails whic contains data, i want to get that data from email and save it in SQL table

is any body have any VB code for that, how to connect exchange server/outlook open email and get the data and save in Table

Thanks in advance

 

[paulbent]

It's a fairly simple operation. Which version of Outlook and SQL Server? Is there anything that identifies the e-mails to be processed such as the subject or sender name?

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[x508]

Hi Paul, sorry to barge in like this.

I want to do something very similar.

I will be getting E-Mails with uniqe subject lines, but from different E-mail adresses.

I was thinking about copying the contents of the e-mail into an RichTextBox in my VB app, loop through the data and then add certain bits and pieces of it to my access DB.

Any ideas would be greatly appreciated.

Thanks is advance..

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[jawan]

Thanks for reply

Outlook is 2002
MS SQL Server 2000

 

[jawan]

Sender are different, profile name is same and emails are not same mostly are 70% same

 

[paulbent]

I can outline how to extract the message text but you'll have to work out your own parsing into fields routine as this will be specific to your data.

This example will process all items in the Inbox. In a real app, to avoid processing the same e-mail twice, you can move the item after processing or filter the items on date received (keep a last processed date/time in SQL Server) or Unread status perhaps.

Dim objApp As Outlook.Application
Dim objNS As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder
Dim objItems As Outlook.Items
Dim objItem As Outlook.MailItem
Dim strFilter As String

Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
'Get a reference to the Inbox
Set objFolder = objNS.GetDefaultFolder(olFolderInbox)
'Get the items in the Inbox
Set objItems = objFolder.Items
'Could filter at this point
'eg just unread items
'Set objItems = objFolder.Items.Restrict("[Unread] = True")
'eg a specific subject line
'Set objItems = objFolder.Items.Restrict("[Subject] = My target subject")
'eg items received after a certain date
'strLastUpd is a string passed in mm/dd/yyyy hh:mm:ss format
'where mm/dd/yyyy is the local short date format
'strFilter = "[ReceivedTime] > """# & strLastUpd & #""""
'Set objItems = objFolder.Items.Restrict(strFilter)
If Not objItems Is Nothing Then
 'Connect to the SQL server here and create an ADODB recordset (rsTarget)
 
 'Move through the items
 For Each objItem in objItems
  'Get the message body
  strBody = objItem.Body
  'Parse the data into fields, maybe pass the body and a UDT
  'into your custom parsing function and return the parsed
  'data in the udt - eg:
  If fParseData(strBody, udtData) Then
   'Data parsed successfully add a row to the recordset
   rsTarget.AddNew
   'Check for null string content
   If udtData.Field1 <> vbNullString Then
    rsTarget.Fields(&quot;Field1&quot;) = udtData.Field1
   End If
   'and so on for each field parsed from the message body
   'Save the record
   .Update
  End If
 Next
 'Close connection and release ADODB object references
End If

'Release Outlook object references

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[jawan]

Thanks paulbent

let me try this and i will be back

 

[jawan]

getting error

"Compile Error. Invalid outside procedure"

at

Set objApp = CreateObject("Outlook.Application&quot
Set objNS = objApp.GetNamespace("MAPI&quot
'Get a reference to the Inbox
Set objFolder = objNS.GetDefaultFolder(olFolderInbox)
'Get the items in the Inbox
Set objItems = objFolder.Items

 

[paulbent]

You need to put all the code in a procedure like a button click sub.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[jawan]

Thanks, if i am not wrong then i need to run this through SQL right...? Sorry i never work in VB, but its very intresting language.

 

[paulbent]

There are a number of ways to deploy the code:

1. An Outlook macro using the VBA language, very similar to VB. This can be attached to a toolbar button or run from the macros sub menu.

2. An Outlook COM Addin. This is an ActiveX DLL that's loaded by Outlook and runs in the same process space. It can be created using VB or the Addin Designer.

3. A VB exe

The MSDN CD is a good starting point for code examples and short tutorials. There are many web sites offering source code and tutorials if you do a Google search, A good site for Outlook programming examples is

http://www.slipstick.com/

The best forum for Outlook programming qusestions is the Microsoft Newsgroup, microsoft.public.outlook.program_addins



Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[jawan]

Thanks for your all quick replies, very very helpfull

can you mention how t link the MS SQL Server in then database through VB.

What is better,
1. create procedure in MS SQL, call vb code and sceduale to run procedure after every few minutes

2. one of from as you mentioned above

Thanks

 

[paulbent]

I would go for option 2. To update the SQL Server table, set a project reference to Microsoft ActiveX Data Objects 2.x. Search the MSDN CD for ADODB And AddNew for code examples.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk