Automatic E-mail - Use of hyperlink in cell to add attachment

Good Day Lads & Ladettes!

Tried to solve this in another thread but it wasn't going anywhere.

Below is my sub to send an automatic e-mail depending on the date in a Cell. Each row contains metadata about a file (a transmittal - PDF), and in column 'A' of that row it has a file number 'ENER-P12345-0413-001' which is also a hyperlink to the file in question. I'd like to use the hyperlink address stored in this cell to attach the file to them generated e-mail automatically. Does anyone know if this is possible? Or how to reference the hyperlink address stored within this cell?

Many thanks to anyone who can solve this one - I figure the data must be stored in the cell somewhere so it should be possible, but I'm no whiz kid like you macroids out there!

Please Login or Register  to view this content.

Hey,

Thanks for the info!

I can change the way I launch the e-mail, it'll take a bit of fiddling around but that can be done.

Would you know if it would be possible to use the hyperlink address in the cell as the information for the attachment in the above method?

I'm going to post this here - This guy seems to be doing a similar thing, any way we could use this and apply it to the application I'm doing?

http://stackoverflow.com/questions/5...-in-excel-cell

Yeah, I mean something like that Thanks.

I'm kind of rubbish at putting it in context though, and I'm having trouble changing the macro from a shell execute to the early or late binding like you had in your post. This is the problem with trying to create complex macros with no visual basic knowledge.
As soon as it gets to the .To = part I get an error.
If I post what code I have can you help me correct it please?

Okay,

Well I managed to change the execution of the e-mail with the late binding example you gave.

This seems to be working fine, now all I have to do is work out how to exctract the hyperlink address from the cell and use that for the attachment path! Exciting

Please Login or Register  to view this content.

It's this line

Please Login or Register  to view this content.

I'm using a file on the desktop with a short url now, still getting the same problem. I've even tried using the get hyperlink as a function instead,but it just causes the same error. Maybe you have a reference library in use that I don't? Could you possibly paste the entire subroutine your using and I can try just replacing my one with it?

Whats really annoying is that when I hover over the

Please Login or Register  to view this content.

it shows the correct file path to the pdf. It just comes up with that error and I don't know what to do to get past it.

This is actually driving me crazy. It just doesn't seem like the coding is wrong

Change this from late binding to early binding. It still doesnt work, but it's went from being a runtime error to a compile error - 'Argument not optional' and it highlights this part '.Add ='

If I remove the attachments line of code it works fine, so there's something wrong with this one line. I'll give rep to anyone who gives me any insight into why this isn't working, even if they're wrong because I'm out of ideas and I'd hate for a single line of coding to stop me solving this

Solved!

and yet not solved

This is the corrected code

Please Login or Register  to view this content.

The problem - the workbook is in the same main folder as the files, but in different subfolders (if you get what I mean). Because excel shortens the hyperlink it means it won't work as an attachment. Is there anyway to have the hyperlinks as the full address?

This code seems to work when the workbook is on my desktop and the hyperlink to the file is on a different drive.

Solved! Code below.

Not exactly the neatest way around it but it works! Pretty much had to set a constant for the address of the folder the files & workbook are kept in(FPath), then used the grab hyperlink (FName) to get the futher extension of folders and combined them to make another variable (Ease).

As I said, not exactly the neatest way, but it works! Can I give Rep to myself? :P

Please Login or Register  to view this content.

A Big thanks to Abousetta for all his help! Cheers Dude!