Exporting Email Body Text to excel (using outlook 2010) VBA

corbo152 · Aug 6th 2013

Hi,

I am wanting to extract some text form the body of emails that come in daily,

The emails are always in the same format which is:

Name
Address 1
Address 2
Address 3
Postcode

These fields will need to be extracted to an Excel document under the relevant field headers,

I have done some programming before but not had any experience with VBA,

Thanks

Streub · Aug 6th 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

What email program are you using? Duh! You already mentioned that. What version of Excel are you using? Both programs will allow you to import and export data between them. That maybe easier than writing code.

corbo152 · Aug 7th 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Hi, first of all many thanks for your reply, i am using excel 2010, ideally this will need to be done by a script that is run when the email is recieved but how do i export data between them?

cytop · Aug 7th 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

If you're feeling adventurous, you can use the following in Outlook (you will need to set the security level first and then enable macros each time Outlook starts - but 2010 might have some improvements over 2007 in that respect)...

Code

Option Explicit
Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
' default local Inbox
Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
Dim oXL As Object
Dim oWS As Object
Dim lngRow As Long
Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Open FileName:="C:\temp\OLTest.xlsx", AddTOMRU:=False, UpdateLinks:=False
'// Change sheet name to suit
Set oWS = oXL.Sheets("Sheet1")
lngRow = oWS.Range("A" & oXL.Rows.Count).End(-4162).Offset(1).Row '// -4162 = xlUp. not available late bound
If TypeName(item) = "MailItem" Then
Set Msg = item
With oWS
.cells(lngRow, 1).Value = Msg.SenderName
.cells(lngRow, 2).Value = Msg.SenderEmailAddress
'// And others as needed - you will have Intellisense to help
End With
With oXL
.activeworkbook.Save
.activeworkbook.Close SaveChanges:=2 '// 2 = xlDoNotSaveChanges but not availabe late bound
.Application.Quit
End With
Set oXL = Nothing
Set oWS = Nothing
End If
ExitPoint:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ExitPoint
'// Debug only
Resume
End Sub

Display More

Paste that into the ThisOutLook session module in Outlook - you will need to restart Outlook afterwards.

Then, when a mail is received, Outlook will start an instance of Excel, load a workbook (hardcoded at the moment) and write some details from the email to the workbook. Some general details only in the sample - you'll need to code for that as you did not give precise details as to the structure in your post.

That code requires more detailed error handling to be added, especially considering the Excel workbook may be in use.

It can also be easily 'turned around' to run from Excel. There are pros and cons for each approach, I just took the simpler approach.

Streub · Aug 7th 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Thanks cytop!

corbo152 · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Hi Cytop,

First of all many thanks for your reply. i have been away on holiday so only just got chance to look at this.

The full details of what i require are:

Emails will always be moved by Outlook into a folder named British Gas

the text i require to be outputted will always be in the same format:

There will be 4 lines of text to be exported.

Name
Address 1
Address 2
Postcode

these will ideally need to be exported to a .CSV file with comma seperated fields so that our printer can run them through label matrix

Again many thanks for your help

cytop · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

If there are only those 4 lines of text, and all the text is to be exported, then fine. If there's other text, you need to upload a sample of the email body.

corbo152 · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Quote from cytop;682270

If there are only those 4 lines of text, and all the text is to be exported, then fine. If there's other text, you need to upload a sample of the email body.

Hi cytop,

Yes there are only those 4 lines of text to be exported. no other text in the emails at all.

cytop · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

That makes it simpler - No Excel at all.

Code

Option Explicit
Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
'// The BRITISH GAS folder must be a folder of the INBOX. You didn't specify, so
'// I assumed using the simplest example
Debug.Assert False
Set Items = objNS.GetDefaultFolder(olFolderInbox).Folders("British Gas").Items
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
Dim iFile As Integer
If TypeName(item) = "MailItem" Then
Debug.Assert False
Set Msg = item
iFile = FreeFile
Open "C:\Temp\OUTLOOK_EXPORT.CSV" For Output As #iFile
Print #iFile, Replace(Msg.Body, vbCrLf, ",")
Close #iFile
End If
ExitPoint:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ExitPoint
'// Debug only
Resume
End Sub

Display More

Same comments as before regarding where the code goes.

I have made an assumption that the BG folder is a sub-folder of the normal INBOX folder. If you don't specify I can only assume. Also, you need to change the export file name to suit.

Again, as it was unspecified, I'm assuming there are no commas in the address... If that can't be guaranteed then you need to edit as follows:

Code

'// ORIGINAL
Print #iFile, Replace(Msg.Body, vbCrLf, ",")
'// REPLACEMENT
Print #iFile, Replace(Replace(Msg.Body, ",", vbNullString), vbCrLf, ",")

The

Code

DEBUG.ASSERT False

lines were used to give this a quick test. Remove them when you're happy it works as expected.

corbo152 · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Many Thanks cytop,

I have added the code into the ThisOutLook session module and saved it.

I have renamed and created the appropriate CSV file in the correct directory,

so i take it this script will run when an email is recieved in the british gas subfolder?

cytop · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Should do - why don't you try it?

Create a draft message. Save it and then drag from the Drafts folder to the BG folder.

Don't forget you have to close Outlook after editing the code and restart it. You will be prompted to enable macros, same as in Excel.

corbo152 · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Quote from cytop;682280

Should do - why don't you try it?

Create a draft message. Save it and then drag from the Drafts folder to the BG folder.

Don't forget you have to close Outlook after editing the code and restart it. You will be prompted to enable macros, same as in Excel.

I can confirm that the text is outputting to a CSV file ok.

the only thing i have noticed is that every time an email is received the original data exported is overwritten and not put on a seperate line and saved to the same CSV.

Is there any way to do it so that:

Name,Address 1, Address 2, Postcode
Name,Address 1, Address 2, Postcode
Name,Address 1, Address 2, Postcode
Name,Address 1, Address 2, Postcode

Thanks again for all your help

cytop · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Mea Culpa - that's what I get for typing code freehand and only testing with one message.

Change

Code

Open "C:\Temp\OUTLOOK_EXPORT.CSV" For Output As #iFile

to

Code

Open "C:\Temp\OUTLOOK_EXPORT.CSV" For Append As #iFile

corbo152 · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Thanks,

This now works and data is all stacking up nicely

From looking through the data i have noticed that sometimes there are 5 lines of text instead of 4 so for example:

Name, Address 1, Address 2, Address 3, Postcode
Name, Address 1, Address 2, Postcode
Name, Address 1, Address 2, Address 3, Postcode
Name, Address 1, Address 2, Postcode

As the data will always sit under the headings i need to tell the code to parse address 3 if it is present otherwise print a blank space and still put postcode in the 5th box

corbo152 · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Sorry disregard my last as it will just put the postcode were address line 3 would be

All should work fine and i shall run a test with our printer tomorrow.

Thanks

cytop · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Do you want a 'formal' error handler where the user is given the option to re-try if the code cannot open the output file - for example, if it's being used by another process...

corbo152 · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Many thanks for the offer but i do not believe this is needed at this stage. Also each email has an attachment.

is there anyway to tell Outlook to auto-download the attachment when the email is received and place it in a corresponding folder?

cytop · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

You need to check the items.attachments.count property (just to make sure there is one or more) then use the item.attachments.saveas (I think) method to save it, or them.

Still on the phone so if you want to try then go ahead. If it's still an issue later, I'll post back this evening.

cytop · Sep 2nd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Still a lot of assumptions...

Code

Private Sub Items_ItemAdd(ByVal item As Object)
'// Change as required
Const cEXPORTFILE As String = "C:\Temp\OUTLOOK_EXPORT.CSV"
'// You have not specified if all attachments go in one directory
'// or a directory per 'customer'. Assuming the former
'// Change as required - the directory MUST exist before running
'// and ensure the trailing backslash. There is no sense checking in this
'// app!
Const cATTACHDir As String = "C:\Temp\Attachments\"
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
Dim oATT As Outlook.Attachment
Dim oATTs As Outlook.Attachments
Dim iFile As Integer
If TypeName(item) = "MailItem" Then
Set Msg = item
iFile = FreeFile
Open cEXPORTFILE For Append As #iFile
Print #iFile, Replace(Msg.Body, vbCrLf, ",")
Close #iFile
If item.Attachments.Count > 0 Then
For Each oATT In item.Attachments
'// Again - I have no idea what to do in this case.
'// If a file already exists, do not save, but pop
'// a MessageBox
If Dir(cATTACHDir & oATT.FileName) = vbNullString Then
oATT.SaveAsFile cATTACHDir & oATT.FileName
Else
MsgBox "The file " & _
StrConv(oATT.FileName, vbUpperCase) & vbCrLf & _
"already exists in the directory" & vbCrLf & _
cATTACHDir & vbCrLf & vbCrLf & "This attachment was not saved.", _
vbExclamation, "Error"
End If
Next
End If
End If
ExitPoint:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ExitPoint
'// Debug only
Resume
End Sub

Display More

corbo152 · Sep 3rd 2013

Re: Exporting Email Body Text to excel (using outlook 2010) VBA

Hi cytop,

Is the above code to be pasted in the same Thisoutlooksession?, above or below the existing sub for the data export?

the attachments will all go into one folder. and list by date modified so that the data exported and attachments will match up to each other in order

Thanks

Share