Excel macro to copy worksheet from the most recent workbook into another workbook and then send email

CC - Have you considered whether you actually even need to send the spreadsheet?  Perhaps just an email notifying users that a new spreadsheet is available with a link to its location?  You could easily accomplish this with a Batch Script (to move the latest to an accessible location and possibly rename it) and Task Scheduler (to send the email).  In this way, you eliminate the overhead of updating another worksheet, and the load on the mail server(s) involved.

Note: This solution is only appropriate if your recipients are able to access a centralize file location.

Hello Mark,

unfortunately I do not have that setup yet. At some point I will have a link to my website, but not yet. So for the moment I need to send an email.

CC

Can I re-ask the question?

1. I have a workbook that opens automatically every morning (OpenWorkbooks.xls)
2.I would like to insert a macro into that workbook that does the following:

Present structure:
In Folder "A" a new workbook is added on a daily basis.
e.g.
20120907 Fixing Centa FXDP Report.xls
20120910 Fixing Centa FXDP Report.xls
20120911 Fixing Centa FXDP Report.xls

In each workbook there is a selection of worksheets, A,B,C etc.....


The macro should select worksheet "A" from the latest workbook (in this case the 20120911 Fixing Centa FXDP Report.xls) and then send an email of that worksheet to another person.

I can then set a timer onto the macro myself.

Thanks,
CC

CC10 - Sounds like you will definitely need some VB coding to accomplish this.  Unfortunately, that's outside my skill set.  I would recommend that you use the Request Attention button.  EE Moderators will call attention to your question.

Do you want to send the whole sheet or do you want to send a portion?

mailing a range from Excel:
http://support.microsoft.com/?kbid=816644

mailing a sheet from Excel:
http://msdn.microsoft.com/en-us/library/ff458119(v=office.11).aspx#odc_office_UseExcelObjectModeltoSendMail_MailingSingleSheetasAttachment

If you need help shoehorning these into a workbook let us know.

I need to send the whole worksheet as an excel worksheet, not a pdf file.

I think i can manage the email sending. What I need is a macro to select the worksheet, It has to choose the latest workbook in the folder , selected by date, and then send the relevant worksheet.

CC

neither of those methods use PDF,
one sends a range as a sort of table in E-mail
the other sends just a sheet out of a workbook as a new workbook attachment.

For the gathering of the file to send is about using File Scripting Objects.

Can knock something together over the weekend to mail sheet as attachment from a file selected as the "max Dated one".

thanks

OK, looking at this one in more detail...

Would you like the file name to be driven by the date (use todays date to calculate the filename) then run if the file exists.
Or would you like the macro to check all the files in the folder and always mail the sheet from the latest dated file?
Not a problem doing either, but will change the code and speed of the macro.

Hello,

It would be best if the macro searches for the file with the latest date, not today's date.

Thanks,
CC

Hello,

this is exactly what I needed. Brilliant!

Many thanks,
CC