If anyone can see fault in the macro below please help me.
I use this macro to open outlook e mails and format the contents into a table. each time only a small amount of lines are produced as tables the rest as one continous column. any help appreciated

thanks

John






Sub GetHMASplits()
Dim myNameSpace As NameSpace
Dim myMailbox As String
Dim myInbox As MAPIFolder
Dim myUnreadFolder As MAPIFolder
Dim myReadFolder As MAPIFolder
Dim myUnreadMailItem As Object
Dim strBranchCode As String
Dim strBody As String
Dim intFullLength As Integer
Dim NextInString
Dim strHMA As String
Dim myInspector As Inspector

Dim myExcel As Excel.Application
Dim myWkb As Workbook
Dim myWks As Worksheet
Dim myRowNo As Integer

'Make reference to inbox
Set myNameSpace = ThisOutlookSession.GetNamespace("Mapi")
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
'Make reference to HMA folders
myMailbox = "MailBox - $Finance Heal Regions"
Set myUnreadFolder = myNameSpace.Folders(myMailbox).Folders("HMA Unread")
Set myReadFolder = myNameSpace.Folders(myMailbox).Folders("HMA Read")

Set myExcel = New Excel.Application
myExcel.Visible = True
Set myWkb = myExcel.Workbooks.Add
Set myWks = myWkb.Worksheets(1)
myRowNo = 1
'Process until the unread message folder is empty
Do Until myUnreadFolder.Items.Count = 0
'Make a reference to the last email received
Set myUnreadMailItem = myUnreadFolder.Items.Item(1)
'Check to see if the item is a mail item
'If not, move the item to the Other Items folder
If myUnreadMailItem.Class <> olMail Then
myUnreadMailItem.Move myInbox
Else
'Check to see what the subject of the mail is and process as appropriate
If Mid(myUnreadMailItem.Subject, 6, 15) = "HMA Bonus Split" Then
strBranchCode = Left(myUnreadMailItem.Subject, 4)
strBody = myUnreadMailItem.Body
Do Until InStr(1, strBody, strBranchCode, 1) = 0
NextInString = InStr(2, strBody, strBranchCode, 1)
If NextInString = 0 Then
strHMA = strBody
strBody = ""
Else
strHMA = Left(strBody, NextInString - 1)
strBody = Right(strBody, Len(strBody) - Len(strHMA))
End If
myWks.Cells(myRowNo, 1) = strHMA
myRowNo = myRowNo + 1
Loop
Set myInspector = myUnreadMailItem.GetInspector
myInspector.CurrentItem.UnRead = False
myInspector.Close olDiscard
Set myInspector = Nothing
myUnreadMailItem.Move myReadFolder
Else

End If
End If
Loop
'Tidy up the data
myWkb.Activate
myWks.Activate
If myWks.Range("A1") <> "" Then
myWks.Columns("A:A").TextToColumns Destination:=myWks.Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
myWks.Cells.EntireRow.AutoFit
myWks.Cells.EntireColumn.AutoFit
myWks.Range("A1").Select
End If
'Free memory
Set myNameSpace = Nothing
Set myInbox = Nothing
Set myUnreadFolder = Nothing
Set myReadFolder = Nothing
Set myUnreadMailItem = Nothing
Set myInspector = Nothing
Set myExcel = Nothing
Set myWkb = Nothing
Set myWks = Nothing
End Sub

Hi John

Your problem is proving quite complex, so far both our VBA trainners have looked at your code, along with a myself who has high level programming language knowledge (Java, C, C++). I am sorry to say that so far we have been unable to spot the problem that you have.

I would ask that if you have got any further with the problem, could you post back and let us know, as we are continuing to work on this problem for you.

Some advise that I think will be useful for you, is that when coding you should go through and write code comments as you go, so both yourself and others know exactly what your code is meant to be doing at each point. This will make your code much clearer to read for people that have not worked on coding it.

If we find an answer, we will get back to you as soon as possible.

David