Table Macro

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