OOM : How to retrieve Outlook attachments ( Reference, Value, Embedded and OLE) using VBA?
Please find the following code snippet for retrieving various Outlook attachments - Reference, Value, Embedded and OLE using Outlook Object Model (OOM) & VBA:
'Code Snippet : How to retrieve Outlook attachments using Visual Basic for Application (VBA)
Dim omailitem As Outlook.MailItem
Dim oattach As Outlook.Attachment
Dim ofolder As Outlook.Folder
Set ofolder = Application.Session.PickFolder
Debug.Print "Total Items available in Folder : " & ofolder.Items.Count
For Each Item In ofolder.Items
Set omailitem = Item
Debug.Print "Item Subject :" & omailitem.Subject & " Size (in bytes):" & omailitem.Size
If omailitem.Attachments.Count > 0 Then
For Each oattach In omailitem.Attachments
If oattach.Type = Outlook.olByReference Then
Debug.Print "By reference : " & oattach.FileName & " Size (in bytes) : " & oattach.Size
ElseIf oattach.Type = Outlook.olByValue Then
Debug.Print "By Value : " & oattach.FileName & " Size (in bytes) : " & oattach.Size
ElseIf oattach.Type = Outlook.olEmbeddeditem Then
Debug.Print "Embedded item : " & oattach.FileName & " Size (in bytes) : " & oattach.Size
ElseIf oattach.Type = Outlook.olOLE Then
Debug.Print "OLE : " & oattach.FileName & " Size (in bytes) :" & oattach.Size
End If
Debug.Print "-------------------"
Next
End If
Next
Here,
- olByReference The attachment is a shortcut to the location of the original file.
- olByValue The attachment is a copy of the original file and can be accessed even if the original file is removed.
- olEmbeddeditem The attachment is an Outlook message format file (.msg) and is a copy of the original message.
- olOLE The attachment is an OLE document
You can also get more info from : http://msdn.microsoft.com/en-us/library/aa220737(office.11).aspx
Result will be like this (sample data provided for your view):
Total Items available in Folder : 3
Item Subject :Fw: Mormons and Roses - A "Cheers" TV Show ClipSize (in bytes):4057344
By Value : Mormonscantsendflowers_1.wmvSize (in bytes) : 4054264
-------------------
Item Subject :Rate spectrum on exotic position JEX and JNKSize (in bytes):2537614
By Value : JNK_JEX_IR.xls Size (in bytes) : 2525885
-------------------
Item Subject :Fw: Blue Angles in San Francisco(May be a repeat)Size (in bytes):741293
By Value : image001.jpg Size (in bytes) : 61862
-------------------
By Value : image002.jpg Size (in bytes) : 59804
-------------------
By Value : image003.jpg Size (in bytes) : 81551
-------------------
By Value : image004.jpg Size (in bytes) : 48063
-------------------
By Value : image005.jpg Size (in bytes) : 62174
-------------------
By Value : image006.jpg Size (in bytes) : 55008
-------------------
By Value : image007.jpg Size (in bytes) : 76879
-------------------
By Value : image008.jpg Size (in bytes) : 42201
-------------------
By Value : image009.jpg Size (in bytes) : 45637
-------------------
By Value : image010.jpg Size (in bytes) : 56987
-------------------
By Value : image011.jpg Size (in bytes) : 2308
-------------------
By Value : image012.jpg Size (in bytes) : 97229
-------------------
By Value : image013.jpg Size (in bytes) : 28169
-------------------
