zip all files in a folder and email using a vlookup function

  • Hi all! I'm trying to create a macro that will find the parent folder of the active workbook, zip all files within that workbook, then email that zipped file to a distribution list that is located in a separate file within the same drive. ideally, afterwards it would also delete the zip file that had just been created. can anyone help me with this? The vlookup would reference a stationary cell within each of the files i use (same tab name and cell location) 'Summary'!B2


    this is the vlookup i was trying to use
    =VLookup(B2, "\\shared drive location\[Distribution Lists.xlsx]Distribution Lists'!$B:$E", 3, False)

  • Re: zip all files in a folder and email using a vlookup function


    Hi coribrutal, welcome to Ozgrid!


    The majority of your request is quite simple to achieve in a macro - but I don't understand the relevance of the vlookup? what does this have to do with zipping the files and sending an email?


    Is the vlookup pointing to location of the distribution list??

  • Re: zip all files in a folder and email using a vlookup function


    Yes, the distribution list needs to be easy to update so a single file with just the name of the client and the distribution list is a few columns over. The importance of needing all the files in the parent folder of the active workbook zipped is that some have multiple reports that need to be put together in on email. I can't have the distribution lists on the reports themselves for this same reason. does that make sense?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!