Automate a macro in an open spreadsheet (for dummies)

[Johngalt1998]

Disclaimer: while I can use the Excel macro recorder, I have zero knowledge of VBA, other than being able to spell it.

I have a spreadsheet that has live stock prices in it and changes throughout the business day. During the business day, I would like to email the spreadsheet, as an attachment, to a single recipient.

I have found an Outlook add-in that will allow me to automate the emailing of an attachment, but the file to be attached must always have the same name.

So I recorded a simple Excel macro that saves the spreadsheet (saving it over the original file name), but I don't know how to invoke the spreadsheet every 5 or 10 minutes.

If you have any suggestions, including an add-in to suggest, about this - that a dummy could do - please help!

 

[VoG]

Hello. You can do this using the OnTime method - see http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[Johngalt1998]

Thanks,

I've seen Ontime in my searches, but am so technically incompetent that I can't figure it out.

 

[Michael M]

Open Excel and Press ALT + F11 to open the VBA editor
In the toolbar select Insert / Module
You'll notice in the LH pane there is now a folder called module
Click on it and Paste this in the RH pane

Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue("00:15:00"), "NAME OF YOUR MACRO HERE"
End Sub

Now save and close
The next time you open the workbook it will run your macro every 15 minutes, whileever the workbook is open

 

[Johngalt1998]

Thank you Mr. Michael. Will try. It looks simple enough for EVeN me.

I wish I had something to add to the forum.

 

[Michael M]

Don't worry, if you hang around here long enough, you will start to contribute !!
First thing to do, is learn about the stuff you need, then you can pass on your new found knowledge.

 

[Johngalt1998]

Please don't laugh at me, but...

Do you have any counsel for Excel 2003?

Open Excel and Press ALT + F11 to open the VBA editor
In the toolbar select Insert / Module
You'll notice in the LH pane there is now a folder called module
Click on it and Paste this in the RH pane

Private Sub Workbook_Open()
  Application.OnTime Now + TimeValue("00:15:00"), "NAME OF YOUR MACRO HERE"
End Sub

Now save and close
The next time you open the workbook it will run your macro every 15 minutes, whileever the workbook is open

 

[Michael M]

Everyone on this Board is using either / both / all versions of Excel
Some still have Lotus and other skills

Also, the macro provided will work in earlier versions than 2007.