Find files in folder where part of file name match Range and attach to Mail.

Hi All

So, if the macro below finds a "Yes" in

Please Login or Register  to view this content.

and the value in that range is "1234" for instance then i want all files in the specified folder where the file name contains 1234 to me attached to the mail.

So even if the file name is "what a good 1234 day it is", that will be attached to the mail as it contains 1234.

There are PDF and Excel files in the folder.

Please Login or Register  to view this content.

Thanks in advance for any help.

Hi All

Ok, so i found the macro below which looks like it might work, but i do not know how to incorporate it into my original macro that creates the mail.
Also on the macro below, the file type is defined as PDF and i would like for the macro to search for PDF as well as XLS & XLSM.

The part in red needs to be variable to fit with the original code(i think this would be something like

Please Login or Register  to view this content.

?)

Any help would be much appreciated. Thanks in advance!

Please Login or Register  to view this content.

Incorporate this...

Please Login or Register  to view this content.

I am guessing this thread HERE is somehow connected...

Hi sintek

Yes they are connected, i just like to split the different questions up into separate parts to try make it easier for members searching for specific info.

Thank you very much. Will check out the code a bit later and let you know how it goes before i mark this solved.

Hi @sintek

So the code you provided works perfectly, but i am still stuck in an infinite loop and can't figure out why.

Below is the final code(with the loop error) if anyone is interested.

The code seems to never stop running and if i hit ESC then it debugs to this part :

Please Login or Register  to view this content.

Any ideas?

Final Code(with loop error) :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Awesome! Works perfectly thank you very much.

One last thing if i may.......

It's not the same files that are sent the whole time(forgot to mention, so i tried this :

Please Login or Register  to view this content.

,but that does not work....

any ideas?

What is the value in cells(cell.row,"Q")...Does it include the extensions...Does it include a wildcard character "*"

Uploading a sample file depicting your actual files setup is always important...That way members can assist with actual code and not have to guesstimate...

the values will always be a number 4 to 8 digits and on occasion be preceded with a letter or 2 like QU.

busy creating a separate sheet to upload, will do so shortly....my original is waaaaaay too large to upload even if i do clean it up it.

Please Login or Register  to view this content.

check the value of each of the above as follows...
Just before the statement put

Please Login or Register  to view this content.

This will depict the two values...
The like parameter searches a wildcard comparison... Which the two above will never have as the file extension is not included in your statement...

As per post 3...

Please Login or Register  to view this content.

Hi

See attached as requested.

the values will always be a number 4 to 8 digits and on occasion be preceded with a letter or 2 like QU.

You are confusing us now...Column Q has Yes or No...So are you trying to search for files with Yes or No names???

No, it searches for rows with Yes/No to determine opening a new mail or not and then searches the Offset value in Column P for the file to attach.

i.e. if there is yes in column P then create a new mail with the files of the "like" name in the same row in column p attached to that mail.

So why this in your code

Please Login or Register  to view this content.

this indicates file name as per Column Q

dummy mistake

should be

Please Login or Register  to view this content.

thanks for pointing that out.

sorry for the confusion

You are wanting this perhaps...Untested...

Please Login or Register  to view this content.

yes thats it.

another mistake, i put (-1,0) above but it should be as you have it the other way around

the workbook is still giving a error though, so i don't think that i complited it correctly.

giving me end if without break if and same with, with.

Are not closing one of your condition statements...Don't have the file or code so cannot assist

hi

i attached the file a few posts up

see attached

Please Login or Register  to view this content.

still giving "next without for" here :

Please Login or Register  to view this content.

will you perhaps attach the workbook with the corrections in and highlight the corrections?

lol, not getting impatient....just worried i'm taking up too much of your time

Oops for got the attachments add snippet...

Please Login or Register  to view this content.

Cannot test right now and working from lounge Pc...No Outlook

cool, will try it again a bit later and let you know.....gotto run quickly for now

thank you very much so far sintek

Alright, so it's working now, but for some reason it changes the offset values for the body with each mail.

So in the code, the offset values stay the same but the first mail will give the correct values for :

Please Login or Register  to view this content.

then the next mail that opens will show as if the code has moved one over on the offset to :

Please Login or Register  to view this content.

the next mail after than moves one more and so on.

What would be the reason for that?

Please Login or Register  to view this content.

Lets assume the loop is in row 5...the above code takes the value from [D6] ... .Offset(-1, -13) means 1 row below , 13 columns to Left
as you are looping cells in column "Q"
this snippet

Please Login or Register  to view this content.

is actually

Please Login or Register  to view this content.

so if you want to get a value within that same row...make use of the offset values i.e.

cell.offset(,1) means 1 column to right same row...
cell.offset(,-1) means 1 column to left same row...
I assume you don't want to reference cells above or below active row so the row must not be referenced in the code...

I notice that you are wanting to reference your Headers in your Email body...make use of this snippet for that... i.e.

Please Login or Register  to view this content.

You are awesome sintek.

thank you very, very much!!!!!!

I'm so happy, i'll repeat that in Klingon..... nagh SoH, qatlho'qu'

..........................
THANKS.gif