Macro To Automate File Retrieval and Email

[Guest]

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 jroject\market.xls
2.Prompt me to select a worksheet (they are named May 1, May 2, etc…)
3.After selecting the worksheet, email range A10100 to (e-mail address removed)
4.Close the workbook with no changes


Is this possible ? Thank you in advance

 

[David Sisson]

Is this possible ? Thank you in advance
Sure

1.Open up the workbook jroject\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 ("jroject\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 A10100 to (e-mail address removed)

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