Possible to do the same thing when your emails are stored in a windows folder rather than an outlook one and it certainly is possible to do that so let's take a look and see how it works.
To get started i've created a new excel workbook saved as a macro enabled file and then in the visual basic editor created a new module and started a simple subroutine.
I've got this file stored in the same folder as a couple of subfolders as well so there's one there called attachments which is currently empty but that's where we'll end up saving the.
Attachments later and then the other folder called emails already has some message files stored in it and each one of these has at least one attachment i'll let you provide the emails and the.
Attachments yourself for this example all i'm going to do to start with is write some code to get the name of the first file in that folder so to help with that back in the visual.
Name as string i'll eventually spell that correctly i'm going to store a path in the email folder path variable so let's say email folder path equals.
And then i want to refer to this workbooks path and then concatenate to the end of that backslash emails backslash what i can then do is try to get the.
Name of the first file in that folder and i want to do that using the directory function or the dir function and store the result in the email file name variable so i can say email file.
Name equals dir and then open some round brackets and i could just pass in the email folder path variable and in fact if i do that and then say.
Debug.print so that i can print out the name of that file email name when i run the subroutine it gets the.
Name of the first file in that folder
Now that folder could contain lots of other file types rather than just msg files so to make sure we're limiting this list to only msg files we could add.A wildcard search pattern to the end of our email folder path so in the directory function or the dir function we can concatenate in some double quotes an asterisk dot msg.
And then if we run the subroutine again that will return exactly the same end result next i'd like to do the same thing for all of the other files in the folder.
With the msg extension so to do that i'm going to wrap a loop around my debug.print statement so let's start the loop by writing the word do and then i'm just going to indent the debug.print.
Statement and then a couple of lines further down i'm going to write the word loop and then i'm going to add a condition to the loop line loop until.
The email file name variable equals an empty string now to make sure that the email file name variable does eventually return an empty string or contain an empty string.
I want to try to use the dir function again to get the next file name in the same folder so i'm going to just before i loop i'm going to say email file name equals and.
Then call the dir function again without passing any arguments to it so if you don't pass any arguments to the dir function it will use the initial settings here.
So if i just clear the contents of the immediate window and then i could step through this subroutine using the f8 key just so you can see that the loop does go round and.
You can see that it's retrieving the
Next file name and the next file name and then this time when we hit f8 again there are no more files with a dot msg extension so the email file name.Variable contains an empty string so the loop ends and then so does the subroutine now it is possible when we first begin running this code that there are no.
Files in that folder with an msg extension and if that was the case the dir function would return an empty string initially.
It would therefore be pointless going into this loop to do whatever we're about to do so let's add a little if statement just before the do until loop if email file name equals an empty.
String when we first try to capture a file we can then say exit sub because it would be pointless entering the loop and repeating these instructions.
Next i'd like to use each of these files to create a new email object using the file as a template and to do that we're going to use microsoft outlook i want to make sure i get as much help.
As possible from the intellisense when i write this next set of code so i've already set a reference to the microsoft outlook object library if i head to the tools menu and choose references we'll.
See that i've set a reference to microsoft outlook 16.0 object library so once i've done that it means i can declare variables from the outlook library so let's start by declaring a.
Variable to hold a reference to an instance of the microsoft outlook application so i'll say dim o lk as outlook dot application i'd also like a variable to hold a.
Reference to the new mail item that we'll create so let's say dim mi as outlook.mail item once i've established that i've found at least one file with a message extension.
Before we begin looping i'm going to say set olk equals new outlook.application and then inside the loop after i've printed out the email file name i'm.
Going to say set mi to be equal to olk dot create item from template and we're going to use the file that we found to provide the template for the.
New email if i open up some round brackets there you'll see that i need to pass in a template path so this can't just be the file name it.
Needs to be the combination of the folder path and the file name so let's concatenate those two variables together so email folder path and.
Email file name so that will generate the new mail item object now if you wanted to see the email appear on screen you could then say.
Mi.display just to check that your code is working but it's absolutely not necessary to do that so i'm not going to bother displaying my mail item object somewhere in here we're going to save.
The attachments and we'll get to that in just a moment let me just add a quick comment to save attachments and then once we've finished with that mail item object we'll want to close it.
And not bother saving any changes so i'm going to say mi.close and then provide the ol discard constant to say don't bother saving any changes so i appreciate we won't really see.
Anything exciting happen here but if i just comment out the debug.print statement to avoid cluttering up the immediate window and then run the subroutine again we'll hopefully see.
That at least we get no failures so that's a good sign next i'd like to add the code that will loop through the attachments collection of each email object we're creating.
So to help with that let's declare another variable from the outlook library i'm going to say dim 80 as outlook dot attachment and looping through the attachments.
Collection is fairly straightforward so just below this comment where i've said save attachments we can say for each a t in m i dot attachments.
I'll then give myself a couple of blank lines and say next a t and then inside this loop rather than jumping straight to the saving part let's make sure that we're actually.
Referencing the attachments by debug.printing something so i'm going to say debug.print then i'm going to just add a quick indent level to the debug.print.
Statement the simplest way i can think to do that is just type in a comma and then after that i can say a t dot file name then i'm going to bring back the.
Debug.print statement for the email file name and i'll clear the contents of the immediate window and then run the subroutine again.
And we should see this time that we get a list of all of the file names along with the attachments in those messages the final step then is to add the code.
That will save each of these attachments in the appropriate folder so again just to help with that i'm going to declare another variable to store the folder path for the attachments folder so let's.
Head up to the top and say something like dim attachments fold path as string and then i can say attachments fold path equals.