UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Email Report On A Specific Day, Access 2013    
 
   
angela1004
post Feb 12 2020, 02:49 PM
Post#1



Posts: 2
Joined: 12-August 19



I have done some searching on this but haven't found a solution so far. I have a report that shows items that are marked as on-hold. Using EMailDatabaseObject, I built a macro that generates an email. I tried using Task Scheduler to send the report (ideally I would like it sent on the first day of each month). TS will open the database but not create and send the email. I've looked on multiple sites and used the /x MacroName function and like I said, the database opens, but the report doesn't send. Is there a way to make this happen with VBA code? Any help will be greatly appreciated.
Go to the top of the page
 
MadPiet
post Feb 12 2020, 03:02 PM
Post#2



Posts: 3,718
Joined: 27-February 09



I think you have to use either Windows Scheduler or you have to write a macro that runs some VBA to do it when the database opens. (SQL Server has jobs for this, which you can schedule, but that's a different kettle of fish).

In your code, check to see if the current date (DATE()) is one where the code should be run.
then check a table for the last time it was run. If that's the current date, then don't run it, else run it...
Go to the top of the page
 
theDBguy
post Feb 12 2020, 03:04 PM
Post#3


UA Moderator
Posts: 78,146
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

If you're not opposed to a person clicking a button every 1st of the month to send out the email, then yes, you can use VBA to send out these emails. If the macro works, you can actually just use it as well - just call the macro in the Click event of a button.

But if you prefer this to happen without human intervention, then we'll have to troubleshoot why your Scheduled Task didn't work, or maybe use a Timer event on a hidden form to do it for you.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
WildBird
post Feb 12 2020, 03:42 PM
Post#4


UtterAccess VIP
Posts: 3,725
Joined: 19-August 03
From: Auckland, Little Australia


You could put all the code required into a single database, and have the code run in the OnOpen of a form that is set to open when the database opens.

CODE
Private Sub Form_Open(Cancel As Integer)
If ProcessPosReq Then
    'MsgBox "Well done, you can go home now! :-)", vbOKOnly, "Success"
    DoCmd.Quit acQuitSaveNone
End If
End Sub


Above runs when the database opens, and then shuts down. It used to show a message box so the user knew it worked, but now it just runs as I commented out the msgbox.

Then go to Windows Scheduler and set it up so that the database opens every first of the month.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Feb 12 2020, 04:02 PM
Post#5



Posts: 3,718
Joined: 27-February 09



You could write the last time the code executed into a table if you wanted... then it's stupid easy to deal with.
Grab the last date using DMAX, then compare that to the current date, and go from there...
Go to the top of the page
 
projecttoday
post Feb 12 2020, 06:06 PM
Post#6


UtterAccess VIP
Posts: 12,232
Joined: 10-February 04
From: South Charleston, WV


Does the database have code that sends the email in it? Does Windows Scheduler open a macro in the database? What does this macro do?

--------------------
Robert Crouser
Go to the top of the page
 
rzw0wr
post Feb 12 2020, 07:32 PM
Post#7



Posts: 371
Joined: 28-March 12
From: Indiana


When the data base opens check the date with,

CODE
DateSerial(Year(Date()), Month(Date()) + 1, 0)


This shows the first day of the month.

You may have to write some code to see if this is a weekday and not the weekend.

Dale

--------------------
Access 2010 32 bit.
Not really very good at access.
Go to the top of the page
 
angela1004
post Feb 13 2020, 09:40 AM
Post#8



Posts: 2
Joined: 12-August 19



Thank you all for your timely responses. I decided that Task Scheduler was probably the way to go and I was able to keep working at it until I got it to work. Again, thanks so much for the help.
Go to the top of the page
 
theDBguy
post Feb 13 2020, 11:31 AM
Post#9


UA Moderator
Posts: 78,146
Joined: 19-June 07
From: SunnySandyEggo


Hi Angela. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th June 2020 - 09:42 PM