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
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