Have Outlook attachments import and paste into spreadsheet

What do you mean by "paste into"?  Are you saying you want the files actually embedded in the spreadsheet?  Please explain a bit more.

Here's what I'd like to do:
An email comes in and has attachments. They are pdf, excel, jpg, gif, html or word. I would like to either copy and paste each individual attachment into a specific cell in Applicant Status.xls or drag it to that cell.

I'd like to be able to click on the file in the Applicant Status.xls sheet and the application would open up. Ideally, I'd like the files to show as icons not as text links as it shows here that can be done: http://www.mydigitallife.info/how-to-add-attach-or-insert-a-file-in-microsoft-office-excel-worksheet/

The solution the above link provides works if the files were already saved, but I would like to copy and paste or drag and drop directly from Outlook.  Can it be done?
Since they are attachments they are not yet saved to my hard drive.

How about saving the attachments to a folder and linking to them in the spreadsheet?

That is what I'm trying to avoid, it's another step I'm wanting to streamline out if possible.

I mean doing that using automation, not manually.

sure!!!

Ok.  What columns do you want the links to go in?

well check out this file here is how it work (this is a start though)

open it and activate macroes.
you have 2 buttons
1 that will save the attachements of any email you select in outlook
1 that will save all email's attachments of a folder selected in outlook

all the attachements are saved to a folder called OLAttachments in MyDocuments of the loged in user.

So here is how to test it.
open the file activate macroes and open outlook and select any email that contains attachments then click on the first button to the left the Import Email attachements then go to that created folder 'OLAttachments' in Mydocuments open it and you will see the 3 files there.

The rest is pretty simple to adapt to any worksheet you may have to simply put the location of these files in a cell so when you click on the cell it will open these files.

You may also try selecting an entire folder (make sure it does not contain too many emails and press on the second button and it will copy all attachements to that folder also.

Enjoy
gowflow
OUTLOOK-saveattachments.xls

ooops my attachement missed the juice !!! try this one you will hv in col A starting row 10 the results click on the cell in blue and see.
gowflow
OUTLOOK-saveattachments.xls

Also, how will the code know what row to put the links in?

I'll take a look gowflow, thanks

@BlueDevilFan, Good question. I think that as long as we put them in the same row, right after the email address that would be best. Don't really need them to go in a specific cell, now that I've been thinking about it.

What I'm after is to verify that I got files from joeblow@emailaddress.com and what they were.  So, if I were to say a specific cell I would change the order of the cols and rename them to this:

Col A - date
B - First Name
C - Last Name
D- Email Address
E - Spreadsheet (profile xls they send me)
F - Passport (they could send it as a xls file, jpg or pdf)
G - Utility Bill (could be a doc, jpg or pdf)
However, if it is impossible to get these files in the specific cells then to just have them one after another in the same row right after the email address, that can work.

here is it more specifically adapted to your file.
Activate macroes and make sure the library Microsoft Outlook xx is added in vba Tools Refrences prior to running it.

click on any cell you want to update this row with the links for the attachments so it picks up the row say you clicked on the email concenring Priscilla so click on D6 then press on the button save links and see the results at column X and forward if you have 1 attachement it will be in X if 2 in X y if more it will take more columns on the same row. click on the ataachement and it will open the coresponding document.

PS it is very important you click on the cell prior to activating the button so it knows where to put the data at the coresponding row.
gowflow
Applicant-Status-scrubbed.xls

So the solution should add a row in the spreadsheet for each email processed?

: BlueDevilFan - yes, however, a person may send multiple emails to get me all the files

did you try the solution proposed ?
gowflow

haven't had a moment yet, gowflow. let me look it over here and get right back to you

I guess it give you what you want.
gowflow

gowflow: how do I do this: Activate macroes and make sure the library Microsoft Outlook xx is added in vba Tools Refrences prior to running it.

open the file goto vba open the menu tools and then refrences and then locate the item Microsoft Outlook 12.0 or 14.0 depending what version is there tick it and press ok save the workbook then close it then open it again and activate macroes.

then open outlook and click on the email that have an attachment that you need to import.
in your excel file click on the cell in Col D that corespond to the data of this specific email I gave the example of Pricila in D6 as presume the data in the excel already exist prior to you importing the link of the attachments if I am mistaken this can be modified to import as well the data from the email to fill in the coresponding columns.

Once you select the cell then you click on the button in D1 Save link and it will import the links of all the attachements that exist in this specific email on that specific row you clicked from col X onward to the right depending on how many attachment you have.

Any more clarifications pls advise.
gowflow

gowlflow, the reference items Microsoft Outlook Library 14.0 was already checked. I guess that was the one?

Selected email with attachements. Selected Col D (it has an email address in that column - not sure if that matters) clicked the Save Links button

and it is still hanging after about 5 minutes with the hour glass, - but now... let's see, can't click on the spreadsheet - seems like it's thinking ... but nothing is happening and can't click on the spreadsheet - no interaction. My computer is not frozen, just excel. So I'm not sure what is going on.

you should not celect the entire column D but only the cell that corespond to that email information if it does not exit then select an empty cell  in an empty row. What oyu did is you selected the entire column so it hanged. CTRL ALT DELETE and end task for the workbook and try again selecting 1 cell that corespond to the data you want to import.
gowflow

Did not select the entire Column just the cell in Col D. restarted excel, selected this time an empty cell and it put 1, 2, 3 and 4 in cols Y, Z, AA and AB. Then clicked on the links and the saved attachments opened up. Fantastic!!

Questions: where are the attacments saved? Can I tell you where I want them saved?
Based on what I said above to BlueDevil:

Col A - date
B - First Name
C - Last Name
D- Email Address
E - Spreadsheet (profile xls they send me)
F - Passport (they could send it as a xls file, jpg or pdf)
G - Utility Bill (could be a doc, jpg or pdf)

1. Can we instead put the links to these files in cols E, F, G and H? (I will reorder the cols)
2. Is it possible to put an icon, a graphic or something more descriptive than a numbered link. Something that tells me what it is by looking at it rather than having to open it up?
3. Can we also insert the email address in Col D?

I will gladly post another question for any or all of these if you'd like.

where do we get first name and last name the email you attached have only the attachments !!!
the rest can be managed.
gowflow

Well, that would mean delving into an attached spreadsheet (if there was one) or the attached html file (if there was one), so let's not go there (-yet : ). Extracting the email address is good enough.

so lets summit up I propose following:
Col A Received Date of the email
Col D Email sender
Col E if the attachment is .xls
Col F if in the name of the attachment there is the word 'passeport'
Col G other attachment
Glo H other attachement

if I look again at the email you attached the word passeport is not there and seems we cannot create a pattern.

My suggestion is you decide how many columns Maximum you can have as attachements and we will dump the links in those if you feel you can get up to maximum 5 attachement and they would go from Col E,F,G,H,I we will always try to put the xls in Col E and the rest as they come and you will see in the hyperlink the name of the file like
bank-statement
id-national-high
etc...

would this be ok ? if yes pls specify how many columns for attachement and you can label them Attachment1 Attachment2 etc...

gowflow

The thing with passport files is that they can send them as a jpg, pdf, embedded in an excel file, gif or in a word file - same with the address verification file. The only file consistently xls is the profile spreadsheet.

So the only consistent formatted file is a spreadsheet, however there could be more than one excel file depending on what format they send the passport file.

What you propose will work. We probably should have five cols.

Questions:
- What do we do if there are two applicants in one email with double the amount of attachments? Or five or more? (It happens)
- What if an applicant resends an attachment in another email that is an update of a previous attachment? (The passport was a bad scan, or missing some info in the spreadsheet, etc)

well here it is !!! you said it and I thought (as I am very much used to your special cases and the unformated situations ...) reason why I plugged this just at the end of the columns.

my suggestion is do not get bogged with a limited number of columns. Just start your attachement from the last columns on. This way I can build the macro that it will keep on adding after the last column used this way no matter how manny addon or extra copies all will be there and meaningful.

Take a look at the attached sample I downloaded your email sample in hte columnss that I proposed but given your last comment definitively in favor of starting from your last column on.

Don't try to click on the link you will surely get an error file not found as it is pointing on the location on my pc. But at least it give you an idea of how it will llook like and bearing in mind your type of work I think you will be able to manage to get your info handy quick and efficient.

PS for sure this version accounts for 1 xls will modify it to get all of them properly.
gowflow
Applicant-Status-scrubbed.xls

Thank you, gowflow.

The most important information for me to see up front is: do I have their attachments? So seeing the attachments right next to their email, gives me what I need to see.

So, let's keep them where you have them, but have 5 Columns for now.
This is great!

It's not too often I get more than one applicant's files in a single email.

Oh, I just thought, suppose if they send a zip? Can those be unpacked and the files inserted into columns. Maybe that functionality should be another question?

ok here is the new version and here is how it works.

1) I have inserted 5 columns from E to I and called them Attachments1, 2, 3, 4, 5 then you have your regular columns which ends at AB then I put in AC Attachements.

2) You import and email that have 2 attachments they go in E and F
3) You import and email that have 1 attachments it goes in E
4) You import and email that have 6 attachments they go in E, F, G, H, I and last one goes in AC
5) You import and email that have 8 attachments they go in E to I and AC, AD, AE
6) you import an email that has 2 attachments in a row where previously you had data that had 4 attachments so you had previously data in E, F, G, H the 2 new attachments will go in I and AC etc.... you can input as many attachemtns as you want in the same row they will keep adding to the right after the last existing attachment.

You may try this lookin your outlook folders for any kind of email that have say 3 attachments and process it in a blank row then process it again in that same row and you will see how it will add up.

Hope this meets your requirement.
gowflow
Applicant-Status-scrubbed.xls

Just tried it on a regular 4 attachment email and worked great! It could be awhile before I get an email with more than that so I'm going to accept this as a solution. I'm pretty sure we'll be modifying this spreadsheet as we go along. Could you tell me where on the C drive these files are saved and if I can ask you to put them in a specific location?

This is directed to BlueDevilFan - hopefully you are there.

And this is another question I will pose if you can do it. Could you then take the excel sheet that comes in the email and import it into an Access database with a click of a button?

And just as I said I would have to wait awhile, in comes an email with 54 attachments!!!
And, they all went in as you said they would. Now, can I cut and paste where I want them, which would be below the orginal 4? As a matter of fact, that might be a solution:

that when more than four attachments come in, they are inserted in succeeding rows below the first entry with the same email address is col D.

If that can be done I will submit a new question for this added work. Let me know.

yes for sure anything can be done.
no need to post an other question for this but let me get this correctly
You need now 4 columns of attachemtn not 5 is that correct then when more than 4 in same row you want to duplicate that row onto  anew row and put the attachment remaining in this new row etc ... is that what you want ?
gowflow

Exactly, and also need the email address copied in Col D and put in all the rows and the same for the date copied in succeeding rows as well in Col A

You're a miracle worker!

Of course, creating rows cannot overwrite any rows that may be populated beneath the newly created rows. It needs to push the rows down. (Just want to make sure : )

And, if you wouldn't mind, could you make it so each new email address inserted (each new record) highlights the entire row in different colors. As you can see it is hard to tell what row belongs to which when you scroll so far to the right.

entire row in different colors ???
Then it will be eye bogling !!!
I suggest a row say in a certain color and the next in the same colr bu dimmed or light and then the next in that color

like dark lite dark lite etc...
if ok let me know what color.
gowflow

ok, you're right. Then enough differentiation (big word !) that I can see they're different rows - clearly. Thanks.

thanks gowflow. I have a day trip I have to make today and will look at it later in my evening time. Your efforts are very much appreciated. thank you.

your welcome. Anytime.
gowflow

worked perfectly, gowflow - even with the email that had 54 attachment. Kept chugging it out.

Thanks a bunch!  

I didn't think this was really doable but you proved me wrong once again. Great work!!

great glad could assist you once more.
gowflow

you had previously wasked where the attachments were saved on disk and if this could be changed. I had mentioned it once I first posted my answer but maybe you failed to notice. It is saved in the user you looed in the pc under Mydocuments and in the folder olattachements. If you need to modify this name let me know and I will point out where.
Any other help let me know.
gowflow

Oh yes! I forgot about that. Where I'd like them saved is here: "C:\Users\Michael\Sovereign Archives". Thanks.

Another thing I realized: Is it possible to have the column width, containing the attachment file names you import, be no wider than 10? I don't need to see the whole file name which often times stretches the column width too wide.

Also, I have modified the columns listed (not the Attachment columns), reducing them to what I actually need. Please see and use the attached where I have the column headings only.

Yes, there is another project. I will tell you about it when this one is finally done.

Scrubbed-Applicant-Status-4-28-1.xls

Noted and 3 questions:

1) In "C:\Users\Michael\Sovereign Archives" what is the username you log into windows ? is it Michael ?

2) In Folder Sovereign Archives I presume you only have these attachments case yes then ignore the rest of the comments or you have data there ?? if yes, then you should have a folder for these attachements not to be mixed with the rest of the data. Pls provide the name of the folder so the full path will be (Change Attachements to the name you want):
"C:\Users\Michael\Sovereign Archives\Attachments"

3) I do not see you modified in the file you attached the Column width to be 10. It is still variable to what will be in there. I will remove the Autofit of these 4 columns in the macro and pls advise if I shold fix the width to be 10 as it is too small.

Waiting for your specific answer to the 3 points to modify and upload the final version.
gowflow

I don't login, but yes, that is the name.

That folder contains all the various file types from previous emailed attachments, yes. "C:\Users\Michael\Sovereign Archives" is where I would like them.

Yes, please fix the width at 10. I just need to see that they are there and if want to see more of the file I can manually expand the column and/or click into it.

Thank you!

ok here it is. Pls check it and let me know.
gowflow
Scrubbed-Applicant-Status-4-28-1.xls

Going to call it done, gowflow. Thank you very much.

Now, we are going to do more with this spreadsheet if you're willing. If so, I will post a link to the question here.

It involves extracting information from the spreadsheet attachments (which is attached to the email we are processing) and pasting that data into the Applicant Status spreadsheet you just modified. Let me know.

ok no problem. Let me know the link here.
gowflow

here it is gowflow. thank you!

https://www.experts-exchange.com/questions/28113301/Import-more-data-into-spreadsheet-already-worked-on.html