Exporting incoming emails to .csv or ideally sql

[shanzek]

We are currently using, gasp, Lotus Notes... 2012 version. Apparently in the next 6 months we will be migrating to Outlook. I'm assuming 2016, as we are getting updated to Office 2016 now.

We have an Excel spreadsheet where we track correspondence with customers. That department has requested that incoming emails to a specific email account would be detected, parsed, and updated to this spreadsheet. Basic information: senders email address, date received, subject line, body of email, attachment name.

I find that Lotus Notess 8.5.3 has two features: Views (where you choose which fields to display), and Agents (which define actions to take based on a trigger event). So I can create this functionality in Lotus Notes.

But if we are converting to Outlook shortly, I'd rather wait and do it on the new system. But our IT department says that Outlook/Exchange don't have the same features as Notes. So they are looking at a long project to convert the spreadsheet to a CRM solution. That would be ideal, but won't happen for at least a year.

So I'm trying to figure out if Exchange/Outlook have some sort ability to export the data of an email automatically. Or if there is a tool that we would need to purchase.

Any feedback would be really appreciated.

Thanks
Steve

[gmayor]

Outlook certainly has this capability using macros to provide the processing - the basic principles are covered on my web site at http://www.gmayor.com/extract_data_from_email.htm. Obviously each application needs to be processed according to individual requirements, but this provides the gist.

The main issue is your requirement to include the message body in your worksheet. E-mail message body formats do not lend themselves to Excel worksheet importation.

Get rid of that requirement and you can use ADODB to write directly to the worksheet, which is clearly the best way forward. For that you'll need additional code e.g.


Private Function WriteToWorksheet(strWorkbook As String, _
strRange As String, _
strValues As String)
Dim ConnectionString As String
Dim strSQL As String
Dim CN As Object
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
strSQL = "INSERT INTO [" & strRange & "$] VALUES('" & strValues & "')"
Set CN = CreateObject("ADODB.Connection")
Call CN.Open(ConnectionString)
Call CN.Execute(strSQL, , 1 Or 128)
CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function

strRange is the worksheet name
strValues would be something like


strValues = strDate & "', '" & _
strTime & "', '" & _
strFrom & "', '" & _
strEmail & "', '" & _
strAttachmentName & "', '" & _
strSubject where the strings are the extracted values from the message

[shanzek]

Graham, thank you for your speedy response. Strangely could not find much information about this except for what you provided. Knowing that the process (export from Outlook to Excel) is actually doable will now give me ammunition to push forward with this project. I'll check out your website for further information.
You are a life-saver...
Thanks again
Steve