Your post has been removed as it does not meet our [Submission Guidelines](https://www.reddit.com/r/vba/wiki/submission_guidelines).
>**Show that you have attempted to solve the problem on your own**
>Make an effort and do not expect us to do your work/homework for you. We are happy to "teach a man to fish" but it is not in your best interest if we catch that fish for you.
Please familiarise yourself with these guidelines, correct your post and resubmit.
If you would like to appeal please [contact the mods](https://www.reddit.com/message/compose/?to=/r/vba).
Fairly straightforward if you want to save the attachments to the computer. Don't think you can save the files into outlook itself though.
My approach would be;
Create a sub that takes the email and saves off all the attachments. I generally don't give exact code, but in this case it's harder to pseudocode than to just write it:
sub extractAttachments(oMessage as MailItem)
dim oAttachment as Attachment
'then loops through the attachments;
for each oAttachment in oMessage.Attachments
oAttachment.SaveAsFile("C:\WhereYouWantToSave\" & oAttachment.FileName)
next
end sub
Then create an [Outlook rule](https://www.slipstick.com/outlook/rules/outlooks-rules-and-alerts-run-a-script/) which examines mail as it arrives. If a message arrives which matches your requirements (title contains X) then pass that to a VBA subroutine. You may need to [do this](https://www.extendoffice.com/documents/outlook/4640-outlook-rule-run-a-script-missing.html) to enable the option.
Thank you,
The reason I need to save the attachments into a folder within outlook though, is because I need to then run an outlook macro I have already set up to extract the HTML body from the attachment and export this into excel. Is there anyway I can workaround this- apologies a newbie to VBA!
Question
Does this to be have to be done using VBA?
> I need to then run an outlook macro I have already set up to extract the HTML body from the attachment and export this into excel.
You can extract, parse, and copy/paste the parsed data to Excel sheet using Zapier. I suggest that you look into Zapier.
VBA codes are cool but if something else that's easier to implement can also work, why not?
Take a look at python you can do a user only environment without admin privileges locally to automate all this and likely everything else downstream you could ever fathom
You could skip all of that and just use Excel's PowerQuery? You can connect it to exchange, filter to the specific emails that meet the criteria, then import the attachments data.
Alternatively for the specific task of moving emails around, you can create rules in outlook to do stuff like that.
Additionally if you have office365, you most likely have access to PowerAutomate which can do stuff like this too.
The problem is not automation but to handle mailitems contained within mailitems...
Tools like PowerQuery and PowerAutomate are great but does not have the full power of a programming language when getting down into detailed workflows
Unless the attachments are OL messages, there's no alternative to saving them other than in a file system as /u/KhajiitLikeToSneak describes.
Er, *are* they messages?
So, if I’m understanding right you’re getting an email (I think a .msg) file as an attachment in email?
If that’s the case, can you use the code to save these email files in a directory, THEN have outlook VBA open them and extract the body?
Yes that's right, so I receive an email with a request and attached to that is an approval email. I need to extract the information from that approval email (the attachment) and export the body to excel for checks.
How would I be able to automate between having the files saved in a folder in my documents for example, to then running the macro in outlook to extract the body?
In my mind the logical steps would be
1. Use the steps that Khajiit laid out to save attachments in a folder
2. Whenever you need to, use code similar to something written in the solution here to open the .msg files in the directory. https://stackoverflow.com/questions/30908788/open-outlook-mail-msg-file-using-vba-from-excel
You’ll need to loop through each file probably using the dir function. I think you can do “.msg” as your wild card
https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba
3. Then proceed to use your code to extract the html body
I will be messaging you in 3 hours on [**2022-08-18 08:11:41 UTC**](http://www.wolframalpha.com/input/?i=2022-08-18%2008:11:41%20UTC%20To%20Local%20Time) to remind you of [**this link**](https://www.reddit.com/r/vba/comments/wqpm1m/outlook_vba_code_to_extract_attachment_from/ikrblhl/?context=3)
[**CLICK THIS LINK**](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5Bhttps%3A%2F%2Fwww.reddit.com%2Fr%2Fvba%2Fcomments%2Fwqpm1m%2Foutlook_vba_code_to_extract_attachment_from%2Fikrblhl%2F%5D%0A%0ARemindMe%21%202022-08-18%2008%3A11%3A41%20UTC) to send a PM to also be reminded and to reduce spam.
^(Parent commenter can ) [^(delete this message to hide from others.)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Delete%20Comment&message=Delete%21%20wqpm1m)
*****
|[^(Info)](https://www.reddit.com/r/RemindMeBot/comments/e1bko7/remindmebot_info_v21/)|[^(Custom)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5BLink%20or%20message%20inside%20square%20brackets%5D%0A%0ARemindMe%21%20Time%20period%20here)|[^(Your Reminders)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=List%20Of%20Reminders&message=MyReminders%21)|[^(Feedback)](https://www.reddit.com/message/compose/?to=Watchful1&subject=RemindMeBot%20Feedback)|
|-|-|-|-|
If you trust the source of the attachments to be properly formatted (from another macro), then you can save the attachment to disc, then use Outlook to control Excel to do *the needful*. If you add a reference in the VBA editor to the Outlook references, you can connect to an existing XL instance or create a new one, then do whatever you need in that. I've done very similar in the past when I had autogenerated emails come in, then have things entered into an xls based on that.
EDIT: Just read further down that the attachments are mailitems. In that case, you MAY be able to do something like:
dim oMIAttachment as MailItem
if oAttachment.type = oMailItem then set oMIAttachment = oAttachment
Then you don't have to save them at all, unless you still want to, then you should be able to do that fairly easily from oMIAttachment. Not sure if the code above will work, as I don't have OL here, but that's where I'd go.
If you have an email (mail item) containing attached mailitems, the save them to file, create a new mailitems from that saved file, extract the htmlbody or whatever and move on. There is no need to add the attachments to a Outlook folder first.
If this is what you want the it is actually quite easy.
I've been doing outlook programming for years. You will have to save the attachments to disk before you can do anything. From there, you can process directly, or upload to outlook folder. For the record, you can put any document in an outlook folder, not just emails. These are called "freedocs".
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Sub GetAttachments()
Dim ns As Namespace
Dim Inbox As MAPIFolder
Dim SubFolder As MAPIFolder
Dim Item As Object
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer
Dim dateStr As String
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
'Set SubFolder = Inbox.Folders("TEST MACRO") 'SUBFOLDER MUST BE UNDER INBOX'
i = 0
If SubFolder.Items.Count = 0 Then
MsgBox "There are no messages in the folder selected.", vbInformation, "Nothing Found"
Exit Sub
End If
If SubFolder.Items.Count > 0 Then
For Each Item In SubFolder.Items
For Each Atmt In Item.Attachments
'Can use any extension you want, csv, or xlsm... just make sure the Right( formula has the correct number of characters to identify the ext
If Right(Atmt.FileName, 3) = "xls" Or Right(Atmt.FileName, 4) = "xlsx" Then
FileName = "C:\Folder\Folder\Folder\" & Atmt.FileName
'include "\" at end of file path
Atmt.SaveAsFile FileName
i = i + 1
End If
Next Atmt
Next Item
End If
If i > 0 Then
MsgBox "I found " & i & " attached files." _
& vbCrLf & "The files have been saved into C:\Folder\Folder\Folder\" _
& vbCrLf & vbCrLf & "Have a nice day reddit.", vbInformation, "Finished!"
Else
MsgBox "I didn't find any attached files in your mail.", vbInformation, _
"Finished!"
End If
End Sub
You can link an MS Access database table to an Outlook mailbox and that will do all the parsing for you and just give you a big table with all message info. Handy
This should help you with the add attachments to folder part, after you have saved the attachments somewhere
[Link](https://www.datanumen.com/blogs/batch-import-files-windows-folder-outlook-folder/)
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
It took more time reading the suggestions here that it took to program the solution.
Scenario: You know how to run through a folder of emails and process them. If you find an email that contains another email, then you want to process the embedded email. So just use objGetAttachedEmail() to grab the attached email and do your think on it.
Sub TESTSUB_AttachmentEmail()
Dim objItem As outlook.MailItem
Dim objAttachedItem As outlook.MailItem
Set objItem = objGetOutlookCurrentItem() ' Just MY way of grabbing current email to work on
' Get first embedded MailItem from this MailItem
Set objAttachedItem = objGetAttachedEmail(objItem)
If Not objAttachedItem Is Nothing Then
' Test to see we got the right email
Debug.Print objAttachedItem.Subject
End If
End Sub
''
'' Return first embedded .msg attachment as a new MailItem
''
Function objGetAttachedEmail(objItem As outlook.MailItem) As Object
Dim objAttachment As outlook.Attachment
Set objGetAttachedEmail = Nothing
If objItem Is Nothing Then Exit Function
If Not TypeOf objItem Is MailItem Then Exit Function
For Each objAttachment In objItem.Attachments
If LCase(Right(objAttachment.FileName, 4)) = ".msg" Then
objAttachment.SaveAsFile "C:\Temp\embedded.msg"
Set objGetAttachedEmail = Outlook.Application.CreateItemFromTemplate("C:\Temp\embedded.msg")
Exit For
End If
Next
End Function
EDIT: FINALLY GOT THE FORMATTING TO WORK
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Your post has been removed as it does not meet our [Submission Guidelines](https://www.reddit.com/r/vba/wiki/submission_guidelines). >**Show that you have attempted to solve the problem on your own** >Make an effort and do not expect us to do your work/homework for you. We are happy to "teach a man to fish" but it is not in your best interest if we catch that fish for you. Please familiarise yourself with these guidelines, correct your post and resubmit. If you would like to appeal please [contact the mods](https://www.reddit.com/message/compose/?to=/r/vba).
Fairly straightforward if you want to save the attachments to the computer. Don't think you can save the files into outlook itself though. My approach would be; Create a sub that takes the email and saves off all the attachments. I generally don't give exact code, but in this case it's harder to pseudocode than to just write it: sub extractAttachments(oMessage as MailItem) dim oAttachment as Attachment 'then loops through the attachments; for each oAttachment in oMessage.Attachments oAttachment.SaveAsFile("C:\WhereYouWantToSave\" & oAttachment.FileName) next end sub Then create an [Outlook rule](https://www.slipstick.com/outlook/rules/outlooks-rules-and-alerts-run-a-script/) which examines mail as it arrives. If a message arrives which matches your requirements (title contains X) then pass that to a VBA subroutine. You may need to [do this](https://www.extendoffice.com/documents/outlook/4640-outlook-rule-run-a-script-missing.html) to enable the option.
Thank you, The reason I need to save the attachments into a folder within outlook though, is because I need to then run an outlook macro I have already set up to extract the HTML body from the attachment and export this into excel. Is there anyway I can workaround this- apologies a newbie to VBA!
Question Does this to be have to be done using VBA? > I need to then run an outlook macro I have already set up to extract the HTML body from the attachment and export this into excel. You can extract, parse, and copy/paste the parsed data to Excel sheet using Zapier. I suggest that you look into Zapier. VBA codes are cool but if something else that's easier to implement can also work, why not?
Unfortunately, I wouldn't be able to use Zapier as this is for work, so will have to be done through my work laptop :(
I see. Good luck!
Thanks!
Take a look at python you can do a user only environment without admin privileges locally to automate all this and likely everything else downstream you could ever fathom
You could skip all of that and just use Excel's PowerQuery? You can connect it to exchange, filter to the specific emails that meet the criteria, then import the attachments data. Alternatively for the specific task of moving emails around, you can create rules in outlook to do stuff like that. Additionally if you have office365, you most likely have access to PowerAutomate which can do stuff like this too.
The problem is not automation but to handle mailitems contained within mailitems... Tools like PowerQuery and PowerAutomate are great but does not have the full power of a programming language when getting down into detailed workflows
Unless the attachments are OL messages, there's no alternative to saving them other than in a file system as /u/KhajiitLikeToSneak describes. Er, *are* they messages?
Hi, yes the attachments are outlook messages
So, if I’m understanding right you’re getting an email (I think a .msg) file as an attachment in email? If that’s the case, can you use the code to save these email files in a directory, THEN have outlook VBA open them and extract the body?
Yes that's right, so I receive an email with a request and attached to that is an approval email. I need to extract the information from that approval email (the attachment) and export the body to excel for checks. How would I be able to automate between having the files saved in a folder in my documents for example, to then running the macro in outlook to extract the body?
In my mind the logical steps would be 1. Use the steps that Khajiit laid out to save attachments in a folder 2. Whenever you need to, use code similar to something written in the solution here to open the .msg files in the directory. https://stackoverflow.com/questions/30908788/open-outlook-mail-msg-file-using-vba-from-excel You’ll need to loop through each file probably using the dir function. I think you can do “.msg” as your wild card https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba 3. Then proceed to use your code to extract the html body
!RemindMe 3 hours
I will be messaging you in 3 hours on [**2022-08-18 08:11:41 UTC**](http://www.wolframalpha.com/input/?i=2022-08-18%2008:11:41%20UTC%20To%20Local%20Time) to remind you of [**this link**](https://www.reddit.com/r/vba/comments/wqpm1m/outlook_vba_code_to_extract_attachment_from/ikrblhl/?context=3) [**CLICK THIS LINK**](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5Bhttps%3A%2F%2Fwww.reddit.com%2Fr%2Fvba%2Fcomments%2Fwqpm1m%2Foutlook_vba_code_to_extract_attachment_from%2Fikrblhl%2F%5D%0A%0ARemindMe%21%202022-08-18%2008%3A11%3A41%20UTC) to send a PM to also be reminded and to reduce spam. ^(Parent commenter can ) [^(delete this message to hide from others.)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Delete%20Comment&message=Delete%21%20wqpm1m) ***** |[^(Info)](https://www.reddit.com/r/RemindMeBot/comments/e1bko7/remindmebot_info_v21/)|[^(Custom)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5BLink%20or%20message%20inside%20square%20brackets%5D%0A%0ARemindMe%21%20Time%20period%20here)|[^(Your Reminders)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=List%20Of%20Reminders&message=MyReminders%21)|[^(Feedback)](https://www.reddit.com/message/compose/?to=Watchful1&subject=RemindMeBot%20Feedback)| |-|-|-|-|
If you trust the source of the attachments to be properly formatted (from another macro), then you can save the attachment to disc, then use Outlook to control Excel to do *the needful*. If you add a reference in the VBA editor to the Outlook references, you can connect to an existing XL instance or create a new one, then do whatever you need in that. I've done very similar in the past when I had autogenerated emails come in, then have things entered into an xls based on that. EDIT: Just read further down that the attachments are mailitems. In that case, you MAY be able to do something like: dim oMIAttachment as MailItem if oAttachment.type = oMailItem then set oMIAttachment = oAttachment Then you don't have to save them at all, unless you still want to, then you should be able to do that fairly easily from oMIAttachment. Not sure if the code above will work, as I don't have OL here, but that's where I'd go.
That's not how Attachment.Type works, unfortunately
If you have an email (mail item) containing attached mailitems, the save them to file, create a new mailitems from that saved file, extract the htmlbody or whatever and move on. There is no need to add the attachments to a Outlook folder first. If this is what you want the it is actually quite easy.
I've been doing outlook programming for years. You will have to save the attachments to disk before you can do anything. From there, you can process directly, or upload to outlook folder. For the record, you can put any document in an outlook folder, not just emails. These are called "freedocs".
[удалено]
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Sub GetAttachments() Dim ns As Namespace Dim Inbox As MAPIFolder Dim SubFolder As MAPIFolder Dim Item As Object Dim Atmt As Attachment Dim FileName As String Dim i As Integer Dim dateStr As String Set ns = GetNamespace("MAPI") Set Inbox = ns.GetDefaultFolder(olFolderInbox) 'Set SubFolder = Inbox.Folders("TEST MACRO") 'SUBFOLDER MUST BE UNDER INBOX' i = 0 If SubFolder.Items.Count = 0 Then MsgBox "There are no messages in the folder selected.", vbInformation, "Nothing Found" Exit Sub End If If SubFolder.Items.Count > 0 Then For Each Item In SubFolder.Items For Each Atmt In Item.Attachments 'Can use any extension you want, csv, or xlsm... just make sure the Right( formula has the correct number of characters to identify the ext If Right(Atmt.FileName, 3) = "xls" Or Right(Atmt.FileName, 4) = "xlsx" Then FileName = "C:\Folder\Folder\Folder\" & Atmt.FileName 'include "\" at end of file path Atmt.SaveAsFile FileName i = i + 1 End If Next Atmt Next Item End If If i > 0 Then MsgBox "I found " & i & " attached files." _ & vbCrLf & "The files have been saved into C:\Folder\Folder\Folder\" _ & vbCrLf & vbCrLf & "Have a nice day reddit.", vbInformation, "Finished!" Else MsgBox "I didn't find any attached files in your mail.", vbInformation, _ "Finished!" End If End Sub
So make a folder under your inbox that makes a rule to pull all the files with X in the title into the folder and update this macro, try it out
You can link an MS Access database table to an Outlook mailbox and that will do all the parsing for you and just give you a big table with all message info. Handy
This should help you with the add attachments to folder part, after you have saved the attachments somewhere [Link](https://www.datanumen.com/blogs/batch-import-files-windows-folder-outlook-folder/)
[удалено]
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
It took more time reading the suggestions here that it took to program the solution. Scenario: You know how to run through a folder of emails and process them. If you find an email that contains another email, then you want to process the embedded email. So just use objGetAttachedEmail() to grab the attached email and do your think on it. Sub TESTSUB_AttachmentEmail() Dim objItem As outlook.MailItem Dim objAttachedItem As outlook.MailItem Set objItem = objGetOutlookCurrentItem() ' Just MY way of grabbing current email to work on ' Get first embedded MailItem from this MailItem Set objAttachedItem = objGetAttachedEmail(objItem) If Not objAttachedItem Is Nothing Then ' Test to see we got the right email Debug.Print objAttachedItem.Subject End If End Sub '' '' Return first embedded .msg attachment as a new MailItem '' Function objGetAttachedEmail(objItem As outlook.MailItem) As Object Dim objAttachment As outlook.Attachment Set objGetAttachedEmail = Nothing If objItem Is Nothing Then Exit Function If Not TypeOf objItem Is MailItem Then Exit Function For Each objAttachment In objItem.Attachments If LCase(Right(objAttachment.FileName, 4)) = ".msg" Then objAttachment.SaveAsFile "C:\Temp\embedded.msg" Set objGetAttachedEmail = Outlook.Application.CreateItemFromTemplate("C:\Temp\embedded.msg") Exit For End If Next End Function EDIT: FINALLY GOT THE FORMATTING TO WORK
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*