HOW TO EXTRACT ATTACHMENTS FROM OUTLOOK EMAIL USING EXCEL VBA

How to Save Attachments from Outlook (if you have multiple emails in outlook and you want to save all the attachments and you are doing manually then this code will help you and save your time.)

I joined a new office few years back and found a lady quite irritated and almost in the condition of giving up the day-to-day routine job. So, I asked her the reason. Is all fine with her? She replied, client sends her 96 emails and all those.

Emails have attachments which she must download one by one on desktop and then run a macro which compiles the data, but that later. Her problem was that she had to do this manually and it every day was taking 2 hours of her work time.

So, no productivity, no promotion, no goodie -goodie in boss’s eyes. 🙂 So, Our Excel VBA comes to rescue to make her life beautiful, far off from tiredness. I made this code and now entire work happens in 5 seconds only. Hahaha…Cheers to VBA.

Enjoy the code.

First, we must Activate Outlook Application in VBA Editor.

If you will work with any other application which is not the part of MS Excel Like: – MS Access, MS PowerPoint, MS Word, Web Browser so first we must activate the library or you can say a reference of an object (see below image1) which allows us to use VBA Classes of those applications otherwise Excel VBA will not allow you to access those classes. For more information, you can check our blog on CLASSES.

How to Select Outlook Application Library : Open VBA Editor -> Go to Tools -> Reference.

All External Libraries are sorted alphabetically so you can select it/them easily. For example, Microsoft Outlook library or reference , if you like to select, just press “M” and you will find yourself on Libraries starting with “M”. So , follow this method. Remember, Outlook library will be available as per your office version. For example, if you are using office 365 or 2016, you will see Microsoft.Outlook.16.0. (refer to image2) Same way, 15.0, 14.0 ,12.0 etc. for before version. I wonder why there was no 13.0 …may be because Microsoft also considers 13 as unlucky number like I do and many Indians with me. 😊

Image1

Image2

Dim OLook As Outlook.Application

Declare Outlook application as on object variable. I have given it a name called OLook.

Set OLook = New Outlook.Application

When you define any object variable then you use “Set” Keyword and if your outlook is not open this code will be responsible to open outlook in backend

Dim Omail as outlook.Mailitem

“Mail item” is an outlook class which is always going to be used if you are dealing with emails. For e.g., here we must open emails and see if they have attachments and then save them on desktop accordingly.

Set Omail = OLook.CreateItem(olMailItem)

Creating my email object now using set keyword. Earlier it was only dimensioned by using Dim. Remember, Dim is like thinking of a name to your unborn child. And Once set keyword is used, means your child has arrived in the world and now you are ready to play with him.

Dim ONamespace as Outlook.Namespace

Namespace class is used to refer to any outlook default Folder – Inbox, Sent, Draft etc. Here we have all inbuilt folders. This class is used whenever we are dealing with folders in outlook. Otherwise , you don’t use this class. For eg, If we want to make a code which sends emails then you don’t need this class. Because, to send an email ,I don’t need folders. Got it ? So, what you say now? I say that knowing classes in VBA is first thing and using them wherever they are needed is the second important thing.

Set ONamespace = OLook.GetNamespace(“MAPI”)

This line will create Onamespace object and later we will use its method “GetDefaultFolder” to work with a folder.

Dim OFol as Outlook.Folder

By declaring OFol as folder class, you are telling VBA that there is going to be a folder and that is what we want to target by going into each and every email and pulling out the files from each email. Ofol is a must for us otherwise VBA will be angry on you as she does not know where to go. Did I just make VBA feminine?

Set OFol = Onamespace.GetDefaultFolder(olFolderInbox)

I choose “olfolderinbox” because we are dealing with an inbox folder though you can choose any inbuilt folder you want to work with.You shall see a dropdown when you will write this syntax, needless to say, if writing correctly. 😊

Dim OAtmt as Outlook.attachment

For example, of you have received 50 emails in a day and 30 emails are with attachment and 20 emails are without attachment then this class will help you to deal with only those emails which has attachments and attachment could be anything it could be Excel File, Txt File, PDF, JPG etc.

‘Collection Loop begins now…which is an inbuilt loop gifted to us by Microsoft ever since I am born. Oops……I mean ever since I know VBA. Must be a decade now. 😊

For Each Omail in OFol.items

For each is a collection loop which is already explained in collection loops topic in blog section as to how it works. This loop will take us on each and every email one by one so whatever the number of emails we have in our folder defined above.

Next Omail

Next pushes the vba to move from first email to next one.

 For Each OAtmt In Omail.Attachments

We have created one more loop which is an Inner loop, in earlier loop we have looped emails but before we go from one email to second, we must check it current email has any attachment, if yes, then how many . You don’t need to count because this for each will automatically counts the attachments and start extracting them till not done, outer loop for emails will not start.

Next OAtmt

OAtmt.SaveAsFile “d:\” & OAtmt.Filename

Now when inner loop will work, and it will find a email with attachment then this code will  save the attachment “OAtmt.SaveAsFile” on a path given by us.

Code for you:-

Sub SaveFiles()

Dim OLook As Outlook.Application
Set OLook = New Outlook.Application

Dim Omail As Outlook.MailItem
Set Omail = OLook.CreateItem(olMailItem)

Dim ONamespace As Outlook.Namespace
Set ONamespace = OLook.GetNamespace("MAPI")

Dim OFol As Outlook.Folder
Set OFol = ONamespace.GetDefaultFolder(olFolderInbox)
   
Dim OAtmt As Outlook.Attachment

 
 For Each Omail In OFol.Items

        For Each OAtmt In Omail.Attachments
                 
                 OAtmt.SaveAsFile "d:\" & OAtmt.Filename

        Next OAtmt
        
 Next Omail

End Sub