Automatic Email of report

[hooi]

I need help! I have to send out access report as Excel file on the 15th of every month to several users from my access application automatically without human intervention.

My plan is to create a VBA code and then create a shortcut for this code and place it on the PC, then I'll use Windows' Scheduler to run it automatically.

Please advise whether there is other better way to achieve what I want. My trial VBA code as shown below doesn't work as nothing happened when I try to run it.

---------------------------------
Sub AutoUsageBilling()
Dim stDocName As String

stDocName = "rptAutoUsage"
DoCmd.SendObject acSendReport, stDocName, acFormatXLS, "myemail@myco.com", , , "Monthly Usage Billing", , False

End Sub
---------------------------------

Thanks...

 

[simongallop]

I am not sure if you can call VBA code from scheduler, but you can call MACROS.

1/ Change the name of Sub AutoUsageBilling() to Function AutoUsageBilling()

2/ Create a MACRO Call it AutoRun

3/ First line of the macro will be RunCode

4/ Set the code to be run to Function AutoUsageBilling()

5/ In scheduler after the name of the database you want /X AutoRun

HTH

 

[hooi]

Hi Harry,

I've done as you've suggested, but when I tried to run the Macro, Macro failed to run and a message window is displayed:

Macro Name: AutoRun
Condition: True
Action Name: RunCode
Arguments: AutoUsageBilling()

How should I fix this problem? Thanks for helping...

 

[hooi]

New discovery. It only works if acFormatXLS is removed from the SendObject statement. However, doing this will require human intervention to select a format to send. if I've selected Excel 97-2002 format, and cllick on the OK button to send, it activates Outlook and a window with the following message is shown:

---------------------------------------
A program is trying to automatically send e-mail on your behalf. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No".
---------------------------------------

At this point, human intervention is required too to click on the 'Yes' button.

Am I right to say that since this whole process requires human intervention, therefore when Access is not getting the input, (in this case two responses required), the Macro fails.

Is there a way that I can do the sending of email fully automatically without requiring human response?

 

[WayneRyan]

hooi,

I don't use email with Access, but these threads seem to show
that it is possible.

http://www.access-programmers.co.uk...d=273963&sortby=lastpost&sortorder=descending

Wayne

 

[hooi]

Hi Wayne,

Thank you for your response. I've just found that when I changed acFormatXLS to acFormatRTF, it will proceed to bring up the Outlook.

There is a free Microsoft ScriptIT tool which I plan to use to automatically select the 'Yes' button for sending the email. (There is an article about it on http://www.winnetmag.com/Windows/Article/ArticleID/4462/4462.html)

Does anyone know where I can download Microsoft ScriptIT tool as I'm not able to locate it?

Thanks...

 

[WayneRyan]

hooi,

How about here?

http://www.infotech.jyu.fi/software/utils/freeware/Microsoft/ScriptIt/

Wayne

 

[hooi]

Fantastic! Thank you very much Wayne.