VBA MyArray and split formula

amulgreat

New Member
Joined
Mar 17, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
580
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 17, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
580
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 17, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 17, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
580
Office Version
  1. 365
Platform
  1. Windows
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)

VBA Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,727
Messages
5,638,019
Members
416,999
Latest member
smulttjukken

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top