Create code to run macro and email 1

[Spiderdan1016]

I am wondering if anyone knows if it is possible to write VBA to automatically every morning run a macro and then email a report that is built by the macro to a specific email address. I'm pretty sure that can be done but the trick comes here...in the macro 2 text boxes pop up asking for a from and to date. i need the VBA to automatically fill in the from and to dates where the from us the first day of the current month. and the to date is the previous days date.

 

[Programmer1974]

Hello Spiderdan!

Depending on how your macros are set up, you may have multiple options here. Please expand on what kind of macros you are running (excel, word, other?).

For sending e-mails:
Do a search in this forumn on 'SMTP' or 'CDO.Message'.

If your macros are running in VB, there is a way that you can simply pass the values you want populated in the from/to dates. Again, this is dependant on what kind of macros you are running. I can respond better here if you fill in that bit of info.

If all else fails, an interesting solution is the following:

Set objShell = WScript.CreateObject("WScript.Shell")
objShell.SendKeys "abcd"
Set objShell = Nothing

You will obviously have to work out the timing here, but based on the info you gave, this is about the only answer I can provide . You can find more information on the SendKeys method at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/wsmthsendkeys.asp

Good luck!

 

[Spiderdan1016]

The macro is an Access macro that runs a series of about 25 different queries.

 

[Programmer1974]

Okedoke, this should help:

Dim objAccess
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\myDB.mdb"
objAccess.DoCmd.RunMacro "macTest"
objAccess.Quit
set objAccess = Nothing

If you want to show that access is running, then include this line:

objAccess.Visible = True

You mentioned above that you had some inputs that you needed to take care of automatically. If you wanted to mess with the code I provided with the sendkeys method, have at it. I'm not sure if there is a way to pass a value to a macro like that, so you might concider rewriting your queries in VBA provided by Access, or even in the VBScript itself. That way, you could at least programatically pass values to be populated and not even have a need to display an input box.

Good luck!