1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
| Option Base 1
Dim WithEvents ObjSentItems1 As Items
Private Sub ObjSentItems1_ItemAdd(ByVal Item As Object)
Dim AdresseFichier As String, ObjOL As Object, Tabl(), Existe As Boolean
If Item.Class <> olMail Then GoTo fin
AdresseFichier = "C:\Test.xls"
If (IsFileOpen(AdresseFichier)) Then
For Each ObjOL In ObjSentItems1
If Not Existe Then
ReDim Tabl(5, 1)
Existe = True
Else
ReDim Preserve Tabl(UBound(Tabl, 1), UBound(Tabl, 2) + 1)
End If
With ObjOL
Tabl(1, UBound(Tabl, 2)) = .CreationTime
Tabl(2, UBound(Tabl, 2)) = .Subject
Tabl(3, UBound(Tabl, 2)) = .Body
Tabl(4, UBound(Tabl, 2)) = .To
Tabl(5, UBound(Tabl, 2)) = .SenderName
End With
Next ObjOL
With GetObject(AdresseFichier)
With .Sheets(1)
.Cells(.Rows.Count, 2).End(xlUp).Resize(UBound(Tabl, 2), UBound(Tabl, 1)).Value = .Application.Transpose(Tabl)
.Range("B2:F" & .Cells(.Rows.Count, 2).End(xlUp).Row).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlYes
End With
End With
End If
End If
End Sub |
Partager