VBA and MID Function to extract strings in Emails...Loop maybe?

[Kreiz]

Have my emails populating into Excel and need assistance pulling a substring (8 digit number) out of the string placing in column F, then equipment into G. I think I need loop maybe?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	*	*	*	*	*	*	*	*	*	*
2	8/26/2011 12:57	Tender - FCL - SAN ANGELO, TX - 09/01 04:00	Anheuser-Busch, Inc.	FALSE	ORIGINAL LOAD TENDER Carrier: THIS COMPANYBUNCHES OF INFON/A 88888811 THE 8 DIGIT NUMBER I NEED AND IS DRY	88888811	*	*	*	*
3	8/26/2011 12:57	Tender - FCL - SAN ANGELO, TX - 09/01 03:00	Anheuser-Busch, Inc.	FALSE	ORIGINAL LOAD TENDER Carrier: THIS COMPANYBUNCHES OF INFON/A 88888812 THE 8 DIGIT NUMBER I NEED AN IS REEFER	88888811	*	*	*	*
Sheet6

'need use these for column F and G through VBA, i can only get the first email's number i need entire column.
'=MID($E2,FIND("N/A",$E2)+6,8) =MID($E2,FIND("N/A",$E2)+25,3)

I can get the macro to pull the string needed (8 digit number i.e. '88888811' in this case) from the body of the email for the first row but not the rest of the rows.

ActiveCell.FormulaR1C1 = "=MID($E2,FIND("n/a",$E2)+6,8)"

I thought the above code would place this into the cell but no.

So I am using tndrID = Mid(E, myloc + 6, 8)

Sub SplSubjNAutoFillDWN()

Dim Lastrow As Long
Dim G As Range, H As Range
Dim i As Range, J As Range
Dim K As Range, B As Range
Dim c As Range, E As Range
Dim D As Range, F As Range
Dim myloc As Variant, tndrID As String
Dim tndrEQ

Lastrow = ActiveSheet.Range("a:a").Cells.SpecialCells(xlCellTypeConstants).Count + 1

Set D = Range("d2")
Set E = Range("e" & atvrw)
Set E = Range("e2")
Set F = Range("f2:f" & Lastrow&)
Set G = Range("g2")
Set H = Range("h2")
Set i = Range("i2")
Set J = Range("j2")
Set K = Range("k2")
'define location in body to start the search from.
schstr = "n/a"
myloc = Application.WorksheetFunction.Search(schstr, E)
'define Tender ID and Tender EQ from email body
tndrID = Mid(E, myloc + 6, 8)
tndrEQ = Mid(E, myloc + 25, 3)
Application.DisplayAlerts = False
'F.Select
'ActiveCell.FormulaR1C1 = "=MID($E2,FIND("n/a",$E2)+6,8)"
F.Value = tndrID
G.Value = tndrEQ
'need use these for column F and G 
'=MID($E2,FIND("N/A",$E2)+6,8)   =MID($E2,FIND("N/A",$E2)+25,3)

   Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=True, Comma:=True, Space:=False, Other:=True, OtherChar:= _
        "-", FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)) _
        , TrailingMinusNumbers:=True


   Range("F2:g2").Select
        'Selection.AutoFill Destination:=Range("$F2:g" & Lastrow&)
 Application.DisplayAlerts = False
    Calculate
    
End Sub

This is the code I have modified to find in cell e2 the desired number and place it in column F then the equipment type (dry or reefer) in column G. I believe I've made a mess as it is not placing the MID function into the cell but just the result, so when i fill down the column I am not getting the function to fill down but the extracted string's value.

Have a feeling I need a loop, i am bad with loops......Please assist,