Macro To Automate File Retrieval and Email
 

I would like to use a macro to do the following:

After Excel is opened, run the macro to do the following:

1.Open up the workbook j:project\market.xls
2.Prompt me to select a worksheet (they are named May 1, May 2, etc€¦)
3.After selecting the worksheet, email range A10:D100 to
4.Close the workbook with no changes


Is this possible ? Thank you in advance

Macro To Automate File Retrieval and Email
 

Is this possible ? Thank you in advance

Sure

1.Open up the workbook j:project\market.xls
Look at GetOpenFilename method in Excel help. This allows you to
navigate to the file and select it. If you know it will ALWAYS be
that filename at that file location, just hardcode the path and
filename.
set MyWB = workbooks.open ("j:project\market.xls")

2.Prompt me to select a worksheet (they are named May 1, May 2, etc...)
Search for 'Worksheets userform array' and you'll probably find plenty
of examples where the Worksheet names are loaded into an userform for
you to choose from.

3.After selecting the worksheet, email range A10:D100 to
I'm not 100% sure on this one, but you should be able to 'insert an
object' into the email through Outlook. The object would be the
range. You may have to record a macro in Outlook while you preform
this task manually the first time and see what kind of code it
generates.

4.Close the workbook with no changes
MyWB.Close SaveChanges:=False