+ 0 Votes Writing to a file is a bit more complicated than that.... robo_dev 3 years ago To begin with, if 'Results' is a variable, it needs to be declared, no? http://www.exceluser.com/explore/questions/vba_textcols.htm You might want to start by writing to a text file, just to get it working, as it's more difficult to write to an excel file + 0 Votes I think there's a simpler way to solve this ... Gast?n Nusimovich 3 years ago Larry, Hi, I see that all fields in the body of the messages have a field name, a colon, and a field value. So, you could use the Split function with the colon as separator, and you get rid of the problem of how many positions from the left for each field you need to extract the values. To give you a hint on how to use Split, in the following link you may find a simple and useful code sample: http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/ I hope this is of help for you. Kind regards, Gast??n + 0 Votes To some extent NetMan1958 3 years ago it works for me. I'm using Excel and Outlook 2007. I created a folder in Outlook named "New Contractor Registrations" and placed an email in it that is an exact copy of your example email. It writes " 0000001501" in cell A2, " Larry" in cell B2, " Tyner" in cell C2 (including the leading space in each field) and then I get : Run-time error '5' : Invalid procedure call or argument + 0 Votes Possible Cause johnbseaman 3 years ago Ok it could be that 'undefined' is seen as a code response and not a value in excel. You could try changing it another field name i.e. undef or dontknow. + 0 Votes Try Adding jboughton29 3 years ago Try Adding the Option Explicit statement as well. I still feel that it is unhappy with trying to manipulate an "Empty" string on first go. + 0 Votes Could be a negative length string Realvdude 3 years ago If Len(Info(x)) - CutBack(x) goes negative you'll get a error 5. May I suggest splitting the body into a string array based on vbCrLf (Enter)? My example just builds the body string, as my Outlook accesses Exchange Server, but you can substitute your body string. Sub Results() Dim body As String, sts() As String, iCnt As Integer, colPos As Integer body = "Contractor ID Number : 0000001501" + vbCrLf + _ "First Name : Larry" + vbCrLf + _ "Last Name : Tyner" + vbCrLf + _ "undefined: Self" + vbCrLf + _ "Address Street 1 : 4787 Country Manor Drive Address Street 2 :" + vbCrLf + _ "City: Sarasota" + vbCrLf + _ "Zip Code : 34233" + vbCrLf + _ "State: FL" + vbCrLf + _ "Email: lttsr1@ verizon.net" sts() = Split(body, vbCrLf) For iCnt = 0 To UBound(sts) colPos = InStr(sts(iCnt), ":") If colPos > 0 Then 'we have a piece of information If Len(sts(iCnt)) > colPos + 1 Then 'we have a value for it 'Insert into worksheet Debug.Print Mid(sts(iCnt), colPos + 1) 'The value End If End If Next End Sub You'll note that Address 2: is not handled. You can place it on its own line in the email or add a second InStr starting at colPos + 1 to capture its position. At the insert into worksheet, you could add a Select statement and check Left(sts(iCnt),colPos -1) for the information name and determine column to place the value. + 0 Votes Saving as a text file Spitfire_Sysop 3 years ago I agree that saving the data as a text file first will save you a lot of data processing time. There are functions built in to excel for handling text file input. You are attempting to re-code these functions. You can save each message in to a text file of the same name in a loop using excel to place the data you want in to the proper cells of a workbook. This can all be done from a VBScript within excel. When you open a text file in excel you can make map the data to cells based on a character like spaces, commas or the colon. Here is the text open command: <code> Workbooks.OpenText Filename:= "emailfile.txt", Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=":", TrailingMinusNumbers:=True </code> The important part is <code>Other:=True, OtherChar:=":"</code> This will put everything on the right of the colon in column B and then you can do whatever processing you want to it. + 1 Votes This works NetMan1958 3 years ago This code expounds on what "techr" posted above. I tested it and it works for me. Sub Results() Dim OLF As Outlook.MAPIFolder, oMAPI As Outlook.Namespace Dim sts() As String, iCnt As Integer, colPos As Integer, i As Integer, Row As Integer, Col As Integer Set oMAPI = GetObject("", "Outlook.Application").GetNamespace("MAPI") Set OLF = oMAPI.Folders.Item("Personal Folders").Folders("New Contractor Registrations") For i = 1 To OLF.items.Count Row = i + 1 'current Excel row sts() = Split(OLF.items(i).body, vbCrLf) For iCnt = 0 To UBound(sts) Col = iCnt + 1 'current Excel column colPos = InStr(sts(iCnt), ":") If colPos > 0 Then 'we have a piece of information If Len(sts(iCnt)) > colPos + 1 Then 'we have a value for it 'Insert into worksheet Sheets("Results").Cells(Row, Col) = Mid(sts(iCnt), colPos + 1) End If End If Next Next i End Sub + 0 Votes Try this NetMan1958 3 years ago From what I can tell, Run-time error 9 is a "subscript out of range" error. Run it in Excel and add the following line just above the problem line so that it looks like this: 'Insert into worksheet Debug.Print Row & ", " & Col & ", " & iCnt & ", " & colPos & vbCrLf Sheets("Sheet1").Cells(Row, Col) = Mid(sts(iCnt), colPos + 1) Then run it so that you get the error. Go to the last line in the Immediate Window and examine the vlaues for Row, Col, iCnt and colPos to see if they are out of whack. + 0 Votes Regex bjblok 3 years ago Hi, You will find that the use of Regex (Regular Expressions) will help cut down on the code and make it more readable. http://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex.aspx Bart + 1 Votes RE: colPos = 22 NetMan1958 3 years ago You posted : "OK - Got it to run thru. Row = 2 Col = 1 colPos = 22 (that doesn't seem right)" I think that is OK as colPos represents the position of the colon in the line. What was the value for "iCnt" ?