Excel VBA: Extract Attachments from Saved Outlook Emails

Learn to build a super-time saving code where we will extract attachments from saved Outlook Emails.
We will NOT connect directly to the Outlook Email inbox. Instead, we will copy emails from the Outlook Inbox, place them into a folder on our computer drive and then use Excel VBA to loop over these emails and extract each attachment. This is a safer way to interact with received emails and can be used in your work environment to accurately extract hundreds of attachments.
You can explore more options for what's possible using the Mail and Attachments object from the Microsoft Learn page. Check out the Methods and Properties for the below two links.
Email Object:
learn.microsof...
Attachment Objet:
learn.microsof...
All the code will be built from scratch. The code is available on my website
skillsandautom...

[@darmok4401]

Dint know about this method. useful.. thanks

[@lizaaggad2191]

hello ! thank you for the video ! could you please give a solution to extract attachments from archived mails?

[@biplabgupta374]

Hi, I am new to the VBA automation. I am working on a similar project where I need to add the sender Name, received time of email etc. along with the above attachments. Can you please help me with the code as to where should I add the code for sender Name, received time of email. Thanking you in advance. regards BG.

[@niceymanukonda]

Hello, Thank you for this amazing video. I was able to pull the attachments for .msg but my requirement is for .eml file type and I wasn't able to extract them and got an error. Can you please help me with a solution for this kind of file type?

[@hemantlahoti68]

I want mail subject line as intial of each file name eaxtracted

[@jordan1499]

how could i configure this to work when the Outlook Extractor folder is located in one drive which i can access from my laptop?

[@SkillsandAutomation]

I am not sure about OneDrive. OneDrive Personal on desktop should be fine. You can construct the Folder Path like this "C:\Users\\OneDrive\\", which works for me. Source -techcommunity.microsoft.com/t5/excel/excel-vba-to-refer-to-files-stored-on-onedrive-instead-of-local/m-p/3894487 But, if you don't have the desktop version, I think it becomes the same as SharePoint and iterating over folders may not be possible. Haven't worked with it to be sure.

[@dattamestry7213]

Can we also add sender name and receive time of mail in this code?

[@SkillsandAutomation]

Hi. Yes. These are properties of the email object e.g. you can use oMail.SenderEmailAddress and oMail.ReceivedTime. There are heaps more properties available. You can check out the Microsoft Learn page for what's available. I will put the link in the description.

[@biplabgupta374]

@@SkillsandAutomation Hi, I am new to the VBA automation. I am working on a similar project where I need to add the sender Name, received time of email etc. along with the above attachments. Can you please help me with the code as to where should I add the code for sender Name, received time of email. Thanking you in advance. regards BG.

[@SkillsandAutomation]

@@biplabgupta374 Hi, I have added code to output sender Name, received time of email and count of attachments onto a worksheet. You can check it out on the blogpost for a similar video i have made skillsandautomation.com/useful-code/download-multiple-attachments-from-outlook/. Scroll down to the heading: Code to Output Details of Email and Save Attachments. Hope that helps.

[@carl-johanbrantberger6331]

I have lots of "tasks" in Outlook. These have attachments that I want to extract and save. Can I use this code or does it need to be adjusted in some way? I have tested but it seems that the code can not interpret "tasks". These are not real .msg files or?

[@SkillsandAutomation]

Yes, you should be able to. Tasks is a separate object. So, in the code, you will need to replace Outlook.MailItem with Outlook.TaskItem. No other change to the code needed. I just tried it on one saved task with 2 attachments. And they extracted fine. Hope it works for you as well. Also, in this scenario, you wont be able to extract regular emails and tasks in the same code. Emails will need the original code (with MailItem) and tasks will need the new code (with TaskItem).

[@carl-johanbrantberger6331]

@@SkillsandAutomation Hi and thanks for the tip. It works but I have encountered another problem. When I run the macro over a batch of files, the macro stops and I get an error message. I have tested a bit and it seems that files (tasks) that contain screenshots are the problem. Is there a way around this? My task files may contain .jpg .png .docx. xlsx .pdf etc. My main goal is to extract the .pdf files but it would be sweet to be able to extract all file types. The screen capture is of no important for me.

[@SkillsandAutomation]

@@carl-johanbrantberger6331 Hi, I think you have encountered a picture embedded inside the outlook message (device independent bitmap) which can't be extracted. One way to get around this is to put in some error handling. Before the line: For Each oAttach In oMail.Attachments, add a line: On Error Resume Next. and after the line: Next oAttach, add a line: On Error GoTo 0. This will allow the VBA to skip the attachment extraction whenever it encounters a problematic file such as the bitmap. Hope that works for you. (I've tested it on my end on a task with a screenshot and the code skips over it.) I have also, added all of this code for Outlook Tasks to my blogsite - link below. You can extract the code from there. I have put in a note for how to extract files with specific extensions such as pdf as well. skillsandautomation.com/useful-code/extract-attachments-from-saved-outlook-emails-using-excel-vba/#Extract_Attachments_from_Outlook_Tasks

[@carl-johanbrantberger6331]

​@@SkillsandAutomation Hi again, Appreciate your time trying to resolve this. I'm almost there it feels but I'm still getting an error message ("Run time error no. 13'.: incompatible types" ..or files? -Translated from swedish) and the execution stop. The macro high light this possible problem; Set oTask = oApp.CreateItemFromTemplate(fileItem.Path) Have no clue what to do.

[@SkillsandAutomation]

@@carl-johanbrantberger6331 Hi, If its failing at that line, it may seem that the file is not a task. Would it be possible that you are trying to extract tasks and regular emails in the same batch, as they both look the same once saved? I have posted a code that will work for both tasks and emails, and anything else will be skipped over. I would suggest that you run a small batch, and verify that everything has been extracted correctly. skillsandautomation.com/useful-code/extract-attachments-from-saved-outlook-emails-using-excel-vba/#Extract_Emails_and_Tasks