VBA MyArray and split formula

[amulgreat]

HI, i am new to VBA and trying to export specific format data from Outlook mail body to Excel. i have below formula but it is malfunctioning or can say not complete.
Following things is missing in below script.

No of line items or we can say row in outlook body is sometime higher sometime lower, so for e.g. if in below script no. if line items are in min range of 51, incase in next mail line items are higher then 51, it does not read those line items, if increasing range from 51 to 58 and incase of next mail line items are lesser then 58 it through error as Subscript out of range....? so not sure what should be correct formula for dynamic range of row line items

2nd things missing in formula, as if want to exclude multiple line items or row how to do. currently in below script "If J <> 5 Then" just excluding row # 5, if need to exclude 5,6,7,8,9,10,13,15,18,22,23,24,25,26,27,28,29,30 what should be right script for same.

Need expert help

Sub OUTLOOK_IMPORT_EMAILBODY()

Dim O As Outlook.Application
Set O = New Outlook.Application
Dim OMAIL As Outlook.MailItem
Set OMAIL = O.CreateItem(olMailItem)
Dim ONS As Outlook.Namespace
Set ONS = O.GetNamespace("MAPI")
Dim MYFOL As Outlook.Folder
Set MYFOL = ONS.GetDefaultFolder(olFolderInbox).Folders("DUMMY2")

Dim Myarray As Variant, J As Long, R As Long, c As Long
R = 2: c = 1

For Each OMAIL In MYFOL.Items

Myarray = Split(OMAIL.Body, vbCrLf)


For J = 4 To 58
If J <> 5 Then

Sheets("sheet1").Cells(R, c).Value = Myarray(J)
c = c + 1

Else
'nothing
End If
Next J

R = R + 1: c = 1

Next OMAIL
End Sub

 

[Alex Blakenburg]

The first part is easy change your
For J = 4 to 58
to
For J = LBound(Myarray) to UBound(Myarray)
Note: LBound will be 0, so if you are thinking in line items you will need to offset everything by 1, so line 1 will be 0 in Myarray

 

[amulgreat]

The first part is easy change your
For J = 4 to 58
to
For J = LBound(Myarray) to UBound(Myarray)
Note: LBound will be 0, so if you are thinking in line items you will need to offset everything by 1, so line 1 will be 0 in Myarray

Thanks Alex, it worked but as you mentioned it is starting from 0, instead of earlier it was 4, see if you can help to figure out same.....

 

[Alex Blakenburg]

The bottom end doesn't matter as much, just that you need to offset by 1.
So if you want the output to start from line 5, that is item 4 in Myarray, so just replace the LBound(Myarray) with 4.

 

[amulgreat]

i

Thanks Alex, it worked but as you mentioned it is starting from 0, instead of earlier it was 4, see if you can help to figure out same.....

With your new formula, i think if i am able to resolved 2nd problem it will resolve this as well as i can skip all unwanted line items.....

 

[amulgreat]

The bottom end doesn't matter as much, just that you need to offset by 1.
So if you want the output to start from line 5, that is item 4 in Myarray, so just replace the LBound(Myarray) with 4.

Thanks, it worked, problem 1 is now resolved.... many thanks for your express revert....now only need to work on my 2nd problem where need to skip unwanted line items

 

[Alex Blakenburg]

I am sure there are better options but if you want a quick and dirty option.

Do something like this inside your For loop (except the Dim put that after your other Dim statements)

    Dim LineNo As Long
    
    LineNo = J + 1

    Select Case LineNo
    
        Case 5, 6, 7, 8, 9, 10, 13, 15, 18, 22, 23, 24, 25, 26, 27, 28, 29, 30
            ' Lines to exclude - Do nothing
        
        Case Else
            Sheets("sheet1").Cells(R, c).Value = Myarray(J)
            c = c + 1
    
    End Select