Automatically run query in Excel and send email?

reefoid · 14 Oct 2010 at 11:00

I have a spreadsheet that runs a query against a FoxPro database. At the moment, I have to open Excel, refresh the data and I then email the workbook to various recipients.

Does anyone know of a way I can automate this process? For example, at 4 p.m. each day the spreadsheet would automatically open, refresh the data and then email itself? Sounds like a bit of a tall order, but is this possible?

Thanks

A[L]C · 14 Oct 2010 at 11:17

Very possible. Which email client do you use? I created a spreadsheet which sends to lotus notes but not tried outlook (although its definitely possible)

reefoid · 14 Oct 2010 at 11:19

I'm using Outlook. We have both 2003 and 2007, but a solution for just one of these will be good. How have you got it working with Lotus?

A[L]C · 14 Oct 2010 at 11:21

vba.

Take a look at these

http://www.google.co.uk/search?q=ex...s=org.mozilla:en-US:official&client=firefox-a

reefoid · 14 Oct 2010 at 11:22

Thanks, will take a look.

A[L]C · 14 Oct 2010 at 11:25

give me a shout if you get stuck, although I dont have outlook set up

fretted · 14 Oct 2010 at 13:10

You will need more than VBA, if you want to do what you are processing unless you plan to keep your Excel spreadsheet at all times

reefoid · 14 Oct 2010 at 13:36

I can get the spreadsheet to open automatically using Windows task scheduler. I've taken a look and I'm pretty sure VBA can do the rest. Will give it a go this afternoon, but may be back with questions.

Cheers.

A[L]C · 14 Oct 2010 at 13:58

Set the vba up in the 'thisworkbook' section under

Private Sub Workbook_Open()

End Sub

and put Application.Quit at the end if you want to exit excel (if you have any other sheet open this would be a problem)

or you can close the workbook and specify if you want to save changes by

ActiveWorkbook.Close SaveChanges:=False
or
ActiveWorkbook.Close SaveChanges:=True