Send report automatically via Outlook once daily

I have it working when I run the macro so I just need to get the macro to run the first time access is open that day.   I dont want it to be sent each time access is opened that day, only the first time its open. Also there is a message box that pops up warning me that a program is trying to send an email on my behalf asking me if I want to proceed.  I want to keep it from asking me that because it stops the macro until I hit yes.  
Who is Participating?
flavoConnect With a Mentor Commented:
I dont use outlook so i cant help you with the warnings (there are serveral Q's asking that... ill look)

the other bit... im thinking add a new table called "tblSendReport" with the field "dtSent" as a date - short date

then open a new module and copy this in

Function sendReport()
'add a refrence to DAO Object Library 3.?
Dim db as dao.database
Dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset("select * from tblSendReport where dtSent = #" & Date() & "#;"
  If rs.recordcount = 0 then
     'not done yet
     docmd.runmacro "macMyMacroName"
     db.exectue "insert into tblSendReport ( dtSent ) values ( #" & date() & "# );"
  end if

set db = nothing
set rs = nothing

end sub

then create a macro Called "AutoExeC" then from the list on the left select "RunCode" then type this in the box down the bottom asking for a function name


All Done!

here's a few links to look at..

If you are not confident in VBA then you're in a little trouble...

Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
The message box that pops up indicates that your Exchange Administrator has applied the Outlook E-mail Security Update, which is installed to combat various email viruses, and the message is by design.  See;en-us;262997

You'll probably need to work with whomever runs your email system to work around this.

Good luck.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

robyergovichAuthor Commented:
Dave, I followed your instructions and I think this will work but so far it doesnt do anything when I open the database.  When I run the AutoExeC macro it gives me an error message saying "user-defined type not defined".  Is this because of the note you put in about addinf a reference to the DAO library?  I dont know how to do that.  Anyway let me know if you have any ideas.  I think I follow the solution for the most part.  Also will this fix it so the macro is run when anyone opens the database or only when I open it?  
robyergovichAuthor Commented:
P.S. -  I will be out of town until Thursday so I wont get a chance to work on this again until then.  Thanks in advance for your help.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
robyergovich  - Looks like you've received some solid advice from multiple experts.  Since this question appears to be getting old, please consider awarding points for the effort and closing it.  Thanks in advance.  -Jim
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.