Import Email to Excel From newest to Oldest

hajiali

Active Member
Joined
Sep 8, 2018
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
Hello I Thought I had solved this in a previous post I had however its still not working. I have the code below to pull email info to excel, However, its not importing them from newest to oldest which is what Im needing.

I have tried adding
Set olfoder = Items.Sort("[ReceivedTime]", False) after
For Each xMailItem In olFolder.Items

still no luck.

below is part of the code, however I have the rest of the code in the following link HERE.

VBA Code:
For Each xMailItem In olFolder.Items
        Set xDoc = xMailItem.GetInspector.WordEditor
        For I = 1 To xDoc.Tables.Count
        Set xTable = xDoc.Tables(I)
        xTable.Range.Copy
        xWs.Range("Y1").Select
        xWs.Paste
        Range("X1").Offset(I, 0).Value = xMailItem.ReceivedTime
        Range("X1").Offset(I, 0).Columns.AutoFit
        Range("X1").Offset(I, 0).VerticalAlignment = xlTop
    Next
    Call EmailCopy
Next
    I = I + 1
    
Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing
Range("X:AH").ClearContents
Call TextToNumbers
    MsgBox "ALL BIDS FROM EMAIL HAVE BEEN IMPORTED.", vbOKOnly
End If
End Sub
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

hajiali

Active Member
Joined
Sep 8, 2018
Messages
330
Office Version
  1. 2016
Platform
  1. Windows
I have thought of that, however, before this macro is ran there will be lot of data in the sheet already. value in column A for all the other data will be blank. column A is where the

VBA Code:
Range("X1").Offset(I, 0).Value = xMailItem.ReceivedTime
end up when the macro reaches
Code:
 Call EmailCopy
portion of the code.

So when I sort them afterwards, all the data that was there before this macro is ran goes to the bottom, I need these data to remain on the top. and the Value in row A for all the data that were there before this macro is ran needs to be blank for conditional formatting purposes.
 

Richynero

Board Regular
Joined
Jan 16, 2012
Messages
148
Why dont you drop the results into an array, sort that array, and then drop that into your model. If you cant work out how to use an array just put them somewhere else in your spreadsheet.

Personally, I would do away with VBA entirely and use Microsoft Flow to update your excel spreadsheet each time an email is received. Microsoft Flow is far simpler for the average business user to pick up and far easier to tweak.


 

Richynero

Board Regular
Joined
Jan 16, 2012
Messages
148
Also, in regards to the postioning (some rows going to the top etc) if you gave each line an index number you could use that to ensure the rows go where you want them.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,432
Members
412,529
Latest member
cTatch
Top