Attach Files to Excel Using VBA to Select from Windows Explorer

[Gadgetgav]

Hi All,

I have a spreadsheet with a user form on it for submitting requests to a helpdesk and attaching files.
Ideally I would like to automate this as much as possible. So I would like a button on the form to bring up a Windows Explorer window to select a file then attach it within the spreadsheet on a seperate tab.

This form will be used by multiple users and will involve multiple files saved in different locations so I can't force the filepath/filename in the code.

There will be code to email the whole spreadsheet (inc attachment) to the helpdesk.

I would also like to have some code for the helpdesk to use when they open the spreadsheet to extract the files and save them in a specific folder with a specific filename (based on info filled in on the form). I can do the code to get the filename etc but would need help to extract them in the first place.

Any help would be greatly appreciated.</SPAN>

Thanks in advance,
Gavin

 

[Norie]

Gavin

What do you mean by 'attaching' the files?

Do you mean you want to embed them as objects on a worksheet?

 

[Gadgetgav]

I think that would be the best way, yes but if you know of another way then I'm open to suggestions?
Basically we need the form to gather details of the request and need to also send a file (could be xls, pdf or doc) then extract it at the other end in the most automated way possible.

 

[Norie]

If you embed multiple files into a workbook you'll probably end up with a pretty bloated workbook.

You mention the workbook will be emailed?

Why not attach the other files to the email as well?

The filenames/paths to the other files could be listed somewhere and the code to email the workbook could refer to the list and attach the files to the email.

 

[Gadgetgav]

The difficulty with that method is that I don't know where any of the files will be stored on the host PC (or what they'll be called) and I would like it to be automated at the other end for saving the files, without having to write code into Outlook on multiple machines that will be receiving the emails.

It's unlikey to be more than 1 or 2 small files per form so it shouldn't increase the size or the workbook greatly

 

[Norie]

Why won't you know where the files are stored?

Aren't they being selected via a Windows Explorer dialog?

 

[Gadgetgav]

Sorry, did you mean to use an explorer dialog to populate a list of files then attach them? I considered that but as I said I want the user at the other end to use code within this Excel file to save the files with different names depending on what was entered on the form. I assumed that wouldn't be possible if they were sent as seperate files?

 

[Norie]

Gavin

I started looking into this but came across a bit of an obstacle - how do you save the embedded files?

You can open them in their associated application but I can't seem to find a straightforward way to save them.

 

[Gadgetgav]

That's pretty much the same problem I'm coming up with. I can get them to embed in various ways but can only open them on my PC not on someone else's after emailing it to them