Tracking Outlook emails with Excel

[Excellover123]

Hello Message Board.

I’m a stock control manager & between myself & my team we send 000’s of orders per month & I need a quick & robust way of tracking what replies I’ve had for email order, or more importantly, when I’ve not had a reply at all, so I know when to chase for one.

Is there a way of extracting the data from Outlook in to excel so this can be analysed?

I’m no wiz , but I was thinking, if there’s more than 1 email with the same subject then I’ve received at least 1 response.

Many thanks in advance.

Gary.

 

[JEC]

I wrote such kind of code for a company today. If you really want to do this right, you need some Regex as well to extract the wanted 000's, to filter out the rest of the unwanted text.
A mail Reply has a different name than the initial mail, sent by you or your team. It comes with RE:.... etc

Anyways, this is how you list your Sent and received items next to each other. Just to give you a start. At least you have something to compare.

Sub jec()
 Dim it As Variant, y As Long, x As Long
 ReDim objSent(1000, 1) As Variant
 ReDim objRec(1000, 1) As Variant
 
 With CreateObject("outlook.application").GetNamespace("MAPI")
   For Each it In .getdefaultfolder(5).items
     objSent(x, 0) = x + 1
     objSent(x, 1) = it.Subject
     x = x + 1
   Next
   
   For Each it In .getdefaultfolder(6).items
     objRec(y, 0) = y + 1
     objRec(y, 1) = it.Subject
     y = y + 1
   Next
 End With
 
 With Sheets(1).Cells(1, 1)
    .Resize(x, 2) = objSent
    .Offset(, 3).Resize(y, 2) = objRec
 End With
End Sub

 

[Excellover123]

I wrote such kind of code for a company today. If you really want to do this right, you need some Regex as well to extract the wanted 000's, to filter out the rest of the unwanted text.
A mail Reply has a different name than the initial mail, sent by you or your team. It comes with RE:.... etc

Anyways, this is how you list your Sent and received items next to each other. Just to give you a start. At least you have something to compare.

Sub jec()
 Dim it As Variant, y As Long, x As Long
 ReDim objSent(1000, 1) As Variant
 ReDim objRec(1000, 1) As Variant
 
 With CreateObject("outlook.application").GetNamespace("MAPI")
   For Each it In .getdefaultfolder(5).items
     objSent(x, 0) = x + 1
     objSent(x, 1) = it.Subject
     x = x + 1
   Next
  
   For Each it In .getdefaultfolder(6).items
     objRec(y, 0) = y + 1
     objRec(y, 1) = it.Subject
     y = y + 1
   Next
 End With
 
 With Sheets(1).Cells(1, 1)
    .Resize(x, 2) = objSent
    .Offset(, 3).Resize(y, 2) = objRec
 End With
End Sub

Thank you so much, Jec. I will give this a go in the morning. All the very best, Gary.

 

[JEC]

You’re welcome!?