Need a bit of Help: Exporting Information from Outlook to Excel

[Rys4K]

Hey there,

First, I wanted to say that these boards are really an invaluable resource, especially for someone like myself who is mostly guessing and piecing things together.

As to the matter at hand, I was looking for a bit of help here:

I need to create a script that can be executed in Outlook by a rule when I recieve a new addition to my mailing list.

I believe it to be possible, simply because the information always comes through the email in a static manner, from a mail form on my website. Here's an example, with someone elses personal information *'d out.

Below is the result of your feedback form. It was submitted by
(********@hotmail.com) on Sunday, November 6, 2005 at 17:51:07
---------------------------------------------------------------------------
email: ********@hotmail.com
subject: Mailing List Addition - MAGB.com
name: ***** ****
Address: *** ***** ***** **** **
City: *****
State: **
Zip: *****
---------------------------------------------------------------------------

I need to extract the information from each of the various lines, and add them to an excel file that already exists.

I understand conceptually how I want this to work, and have had some experience in the past with VBA scripting, but in Word, and with information from a userform. I have just been staring at the VB Editor's blank screen, wondering why it must mock me so...

Basically, Outlook would recognize the email as a mailing list addition, from it's subject line, and then execute the script. The script would then copy the information from the email, to an exsisting excel file, by adding a new line, and inserting the various variable's information into the new cells.

Was wondering if someone could perhaps help me with this?

I thank you for whatever input you may be able to offer,

-Rys4K

[Rys4K]

Wow, so I didn't recieve the outcry of support I was hoping for.

I guess my question would be, is it because I was vague? Or, is this literally an impossible task?

Just wondering,

-Rys4K

[Ken Puls]

Hi Rys4k,

I may not be able to help you with the Outlook part, but I'm intrigued. I'll take a look at it later tonight if I can.

As far as the Excel piece, have you had any experience in Excel coding? This code will create a new Excel instance from Word (or Outlook). Just a part of the puzzle for you.

[vba]Sub CreateExcelInstance()
'Macro purpose: Create an Excel instance via code
' using late binding. (No references required)

Dim xlApp As Object
Dim wbNew As Object

'Create a new instance of Word
Set xlApp = CreateObject("Excel.Application")

'Create a new document
Set wbNew = xlApp.workbooks.Add

'Anything you want to do to the document should be done here
wbNew.sheets(1).Range("A1").Value = "I was generated by a macro from Word!"

'Set the instance of Word visible. (It's been hiding until now)
xlApp.Visible = True

'Release the document and application objects to free up memory
Set wbNew = Nothing
Set xlApp = Nothing

End Sub[/vba]

[Killian]

Hi Rys4K

I'll try to take a look at this tomorrow...
Ken's given you the start on getting Excel running from Outlook. I guess you'll want to use:
Workbooks.Open "filepath"
to get to your XL file, then...

how do you want to add the data? to a new sheet, find and empty row on an existing sheet?

On the Outlook side, you need associate your incoming mail to a MailItem object variable, then you can work with the properties of that (like, .From, .Subject, .Body) and pass those though to the XL sheet.
Or is that sample the "Body" of the message? if so, we'll have to work out how you want to parse the text into the various components to assign to the XL sheet

[Rys4K]

First, I really wanted to thank both of you for reaching out a hand on this.

To cover some of the questions posed in the second response.

"how do you want to add the data? to a new sheet, find and empty row on an existing sheet?" (too lazy to actually quote the reply)

Wanted to use the exsisting sheet on the Excel file opened, finding the next empty row to enter the various data fields into.

"Or is that sample the "Body" of the message?" (lazy lazy me)

That is correct, that is the body of the message. Accordingly, from the subject of the message, I can trigger a rule, which then inturn can trigger the scripting in question here. This information is submitted by the user in a mailform, and therefore parts of the body are always static, fortunately so, those static parts occur directly before the dynamic information I hope to capture and transmit into Excel.

It always amazes me, when I read back over what i've written is how poorly even I can understand it. I do really appreciate the help, and if you've any more questions, please do air them here.

Best Regards,

-Rys4K

[Ken Puls]

Hi Rys4k,

Unfortunately, I'm not going to have time to work through the Outlook part of this, so hopefully Killian will be able to get back to it tomorrow.

Just so Killian won't have to do that part of it, here's the revised code to call Excel from outlook. You'll have to change the filename and path (don't forget the last \ in the path), and also you'll need to change the values I've set for the data.

[vba]Sub CreateExcelInstance()
'Macro purpose: Create an Excel instance via code
' using late binding. (No references required)

Dim xlApp As Object
Dim wbTarget As Object
Dim sFilename As String
Dim sFilepath As String

'Assign the filename and path
sFilename = "filename.xls"
sFilepath = "C:\Temp\"

'Create a new instance of Excel
Set xlApp = CreateObject("Excel.Application")

'Open the workbook
xlApp.Workbooks.Open sFilepath & sFilename
Set wbTarget = xlApp.Workbooks(sFilename)

'Place the email values into the next available row of the sheet
With wbTarget.sheets(1).Range("A" & wbTarget.sheets(1).Rows.Count).End(-4162).offset(1, 0)
.Value = "email"
.offset(0, 1).Value = "subject"
.offset(0, 2).Value = "name"
.offset(0, 3).Value = "address"
.offset(0, 4).Value = "city"
.offset(0, 5).Value = "state"
.offset(0, 6).Value = "zip"
End With

'Save and close the file
wbTarget.Close savechanges:=True

'Release the workbook and application objects to free up memory
Set wbTarget = Nothing
Set xlApp = Nothing

End Sub[/vba]

Hope it helps!

[Killian]

Ok, my turn

Although you can trigger a script with a rule, I'm not sure how you can pass a reference to that mail item to the script
However, it is possible to set up a "watched folder" where the folder has events enabled (specifically, the event that's fired when an item comes in - that way you can process each mail as an object it enters the folder.

Here's how I think it can work:

1) create a rule that indtifies the mails and MOVES them to a specific folder.
This will be the "Watched folder"
(In the code example, I've use a folder "Temp" in my Personal Folders - if you change this, don't forget to adjust the code)

2) In the Outlook VBE, select the ThisOutlookSession module and paste in the following code[VBA]'expose the items in the target folder to events
Dim WithEvents TargetFolderItems As Items

Private Sub Application_Startup()
'some startup code to set our "event-sensitive" items collection
Dim ns As Outlook.NameSpace
'and set the target folder
Set ns = Application.GetNamespace("MAPI")
Set TargetFolderItems = ns.Folders.Item( _
"Personal Folders").Folders.Item("Temp").Items
End Sub

Private Sub TargetFolderItems_ItemAdd(ByVal Item As Object)
'when a new item is added to our "watched folder" we can process it
'by calling the routine and passing a ref to the incoming item
Call ProcessMail2Excel(Item)

End Sub
Private Sub Application_Quit()

Dim ns As Outlook.NameSpace
Set TargetFolderItems = Nothing
Set ns = Nothing

End Sub[/VBA]Hopefully, the event names and comments explain whats going on here sufficiently (?)

3) In the Outlook VBE, add a new standard module, and paste in this code[VBA]Sub ProcessMail2Excel(myMailItem As MailItem)
'Macro purpose: Create an Excel instance via code
' using late binding. (No references required)

Dim xlApp As Object
Dim wbTarget As Object
Dim sFilename As String
Dim sFilepath As String
Dim lngTargetRow As Long
Dim strBody As String

'Assign the filename and path
sFilename = "filename.xls"
sFilepath = "C:\Temp\"
'initialize the strin variable with the body text
'of the incoming mail item
strBody = myMailItem.Body

'Create a new instance of Excel
Set xlApp = CreateObject("Excel.Application")
'Open the workbook
xlApp.Workbooks.Open sFilepath & sFilename
Set wbTarget = xlApp.Workbooks(sFilename)

'get the next free row
lngTargetRow = wbTarget.Sheets(1).Cells( _
wbTarget.Sheets(1).Rows.Count, 1).End(-4162).Row
If IsEmpty(wbTarget.Sheets(1).Cells(lngTargetRow, 1)) Then
lngTargetRow = 1
Else
lngTargetRow = lngTargetRow + 1
End If
'input the data by calling the text parsing function
With wbTarget.Sheets(1).Cells(lngTargetRow, 1)
.Value = GetText(strBody, "email:", "subject:")
.offset(0, 1).Value = GetText(strBody, "subject:", "name:")
.offset(0, 2).Value = GetText(strBody, "name:", "Address:")
.offset(0, 3).Value = GetText(strBody, "Address:", "City:")
.offset(0, 4).Value = GetText(strBody, "City:", "State:")
.offset(0, 5).Value = GetText(strBody, "State:", "Zip:")
.offset(0, 6).Value = GetText(strBody, "Zip:", _
"---------------------------------------------------------------------------")
End With

'Save and close the file
wbTarget.Close savechanges:=True

'Release the workbook and application objects to free up memory
Set wbTarget = Nothing
Set xlApp = Nothing

End Sub

Function GetText(strBody As String, strStart As String, _
strEnd As String) As String
'returns a string of the text between two strings
'in a string to search (BAD explanation!)
Dim lngPos As Long
Dim tempString As String

lngPos = InStr(1, strBody, strStart)
tempString = Right(strBody, Len(strBody) - lngPos - Len(strStart) + 1)
lngPos = InStrRev(tempString, strEnd)
tempString = Left(tempString, lngPos - 3)
GetText = Trim(tempString)
End Function[/VBA]This is Ken's Excel wrapper, with a couple of additions and a function that parses the text in the body of the mail item depending on what you pass it.

I gave it a quick test and it seems OK - so it's over to you...

[Ken Puls]

Although I haven't tested it myself, Nice work, Killian! That looks pretty cool!

[Rys4K]

Again, I wanted to thank you all for your help and input here. This is a great script, and after getting it in and configured appropriately, and fussing about for a good 45 minutes, I believe i've found to be the last little kink holding me back here.

Accordingly, the way the script captures the text from the emails incoming, it also captures the character return, or line return... Which also includes it in the excel file, which in essence corrupts the data for it's intended purpose.

I've been trying to conceive for some time now, how to possibly remove the character return from the data, before it's entered into the excel file?

Some sort of filter or scrubber type function?

Again, my apologies if this is some how unclear, but it's pretty obvious by now that i've got little concept of what i'm trying to convey.

I do hope my ignorance isn't only entertaining to me, so someone else enjoys it.

Thank you again,

-Rys4K

[Ken Puls]

Hi Rys4k,

I'm not 100% certain that this will work, but we could try applying Excel's CLEAN function to the data before pasting. This is supposed to clean out all non-printable garbage when pulling data from other apps. Change the big With block in the middle of the code to the following:

[vba] With wbTarget.Sheets(1).Cells(lngTargetRow, 1)
.Value = GetText(strBody, "email:", "subject:")
.Offset(0, 1).Value = WorksheetFunction.Clean(GetText(strBody, "subject:", "name:"))
.Offset(0, 2).Value = WorksheetFunction.Clean(GetText(strBody, "name:", "Address:"))
.Offset(0, 3).Value = WorksheetFunction.Clean(GetText(strBody, "Address:", "City:"))
.Offset(0, 4).Value = WorksheetFunction.Clean(GetText(strBody, "City:", "State:"))
.Offset(0, 5).Value = WorksheetFunction.Clean(GetText(strBody, "State:", "Zip:"))
.Offset(0, 6).Value = WorksheetFunction.Clean(GetText(strBody, "Zip:", _
"---------------------------------------------------------------------------"))
End With[/vba]

Let us know if it works.

[Rys4K]

Hey KPULS,

Thanks again for getting back to me, I actually came back here to boast a bit as i'm all elated, cause i actually discerned my own question.

Here, in the last three lines of code...

[vba]lngPos = InStrRev(tempString, strEnd)
tempString = Left(tempString, lngPos - 3)
GetText = Trim(tempString) [/vba]
It trims the last three characters from the lngpos, as it sets it as the tempString... All I had to do is change that 3 to a 5, and it removes up to the character return I was looking to remove.

So, the script works beautifully.

I'm undyingly grateful to those who lent a hand (and ideas) in this project, it's saved me countless hours of re-transcribing the data lost from the original file.

The final question I have is, why is it that the script seems to crash when I try to run it on more than 31 emails in a row?

This really sounds like an obscence question, just in that I will obviously never receive 31 additions in one day. But, in trying to add those names from the collection of emails, theres a good 400 that have collected over the last few months, I am more than capable of just running the script on lets say 20 emails at a time, it's really just natural curiosity at the moment.

Thank you all again,

-Rys4K

[Ken Puls]

Hi Rys4k,

Glad to be of help. Very cool that your issue was that easy to solve too! LOL!

As for the crash... I'm not sure... All the variables seemed to be cleaned up an released, so I can't think of why. Maybe Killian may have an idea.

[Killian]

Hi guys,

Glad to hear it's worked out... i meant to put in a comment about the text parsing function - it's quite basic and it looks like you've worked out the adjustment for working back from the start of the second search string to the end of the target text.

As for the crashing problem: I assmue you're processing batches of emails by moving them all into the target folder in one go - in theory, this should be fine, in reality, I guess the folderitems event is wanting to fire repeatedly, while it's still processing - shouldn't be a issue but obviously is, so I would suggest a slightly different approach for batch processing and let the event driven code handle the mails as the arrive.

So, for batch processing, maybe put all the outstanding mail in another folder, then on that folder, do a "For Each MailItem in folder, run ProcessMail2Excel" (passing it a ref to the mailitem in the same way) - so then it's just a case of adding an additional control routine that calls the existing "ProcessMail2Excel" [VBA]Sub BatchProcess()

Dim ns As Outlook.NameSpace
Dim BatchTargetFolderItems As Items
Dim MyMailItem As MailItem


Set ns = Application.GetNamespace("MAPI")

'i'm using my temp folder again, you'll need to change
' this to your designated batch folder
Set BatchTargetFolderItems = ns.Folders.Item( _
"Personal Folders").Folders.Item("Temp").Items

For Each MyMailItem In BatchTargetFolderItems
ProcessMail2Excel MyMailItem
Next

End Sub[/VBA]