valmatic
asked on
Open outlook to send mail from access database
Hi. I currently send notification/update emails from a number of access databases using scheduled tasks in windows. Tasks run at set times on a server. They open the database in question, run a macro that makes a program call to run some code and emails get sent out through the outlook client on that machine. In the past I had this running on a WinXp machine and left outlook up and running all of the time. I recently had to move tasks to a server and now the notification times out with an error ( ) because it can't find Outlook. If I open Outlook and run the task, it runs through fine. I don't really want to leave outlook running all the time on my server. Is there a better way to handle what I want to do or is there a way to get this to work sort of in the background, rather than having Outlook open? Task run property is included. I've also attached code for my VBA notification - a condensed version.
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" "s:\Quality Management System\_Records\Record Database\CAPADB\capalog.mdb" /user georgew /pwd nwksysadm100 /WRKGRP "s:\Quality Management System\_Records\Record Database\CAPADB\secured.mdw" /x LateCAPANotice
Option Compare Database
'Send pop-up message or email notice based on timeliness of a given CAPA. At a specified time,
'this code scans the capalog table via the SUP_CAPANotice query and notifies necessary parties.
'The time is set by a Scheduled task on BobI's PC. If Bob's PC is off then the notification does not run.
Public Function Notify()
On Error GoTo Err_Notify
Dim stAppName As String
Dim winuser As String
Dim capano As String
Dim Ass As String
Dim supID As String
Dim sec3due As String
Dim sec4due As String
Dim dept As String
Dim SS1 As String
Dim SS2 As String
Dim Descr As String
Dim rst01 As ADODB.Connection
Set rst01 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = rst01
myRecordSet.Open "[SUP_CAPANotice]"
myRecordSet.MoveFirst
'----------------------------------------
'Issue Notices for Section III late Capas'
'send pop-ups to assignee and email to both assignee and supervisor
Do Until myRecordSet.EOF
If IsNull(myRecordSet.Fields(12).Value) Then '(If sec 3 complete date is blank)
If myRecordSet.Fields(11).Value < Now() Then '(If sec 3 due date is < today)
winuser = myRecordSet.Fields(29).Value
capano = myRecordSet.Fields(0).Value
Ass = myRecordSet.Fields(8).Value
supID = myRecordSet.Fields(31).Value
sec3due = myRecordSet.Fields(11).Value
Descr = myRecordSet.Fields(18).Value
'SEND POPUP to Assignee
stAppName = "net send " + [winuser] + " CAPA NO. " + [capano] + ", assigned to you, was Due: " + [sec3due] + " and is Late."
Call Shell(stAppName, 1)
'stAppName = "net send " + [supID] + " SUPERVISORY ALERT - CAPA NO. " + [capano] + ", assigned to " + [Ass] + ", was Due: " + [sec3due] + " and is Late. " + _
'"Please review this CAPA with your employee as necessary. Thank you..."
'Call Shell(stAppName, 1)
'ALSO SEND EMAIL to Assignee
Dim Outlook
Set Outlook = CreateObject("Outlook.Application")
Dim Message 'As Outlook.MailItem
Set Message = Outlook.CreateItem(olMailItem)
With Message
.Subject = "CAPA ALERT - Your CAPA is Late"
.Body = " CAPA ALERT - CAPA No. " + [capano] + ", assigned to you, is late as of " + [sec3due] + _
". Description: " + [Descr] + ". Notice has also been sent to your immediate supervisor. Thank you..."
.Recipients.Add ([Ass])
.Send
End With
'AND EMAIL to SUPERVISOR
Set Outlook = CreateObject("Outlook.Application")
Set Message = Outlook.CreateItem(olMailItem)
With Message
.Subject = "SUPERVISORY CAPA ALERT - A CAPA for your department is Late"
.Body = " CAPA ALERT - CAPA No. " + [capano] + ", assigned to " + [Ass] + ", is late as of " + [sec3due] + _
". Description: " + [Descr] + ". Please verify that your employee is working on this CAPA. " + [Ass] + " has also been notified. Thank you..."
.Recipients.Add ([supID])
.Send
End With
End If
End If
myRecordSet.MoveNext
Loop
'___________________________________________________________
'chopped off rest of code here as it is more of the same with different parms.
sorry, just re-read.
in your code, does it already.
this guy has a close app action:
http://visualbasic.ittoolbox.com/documents/closing-application-through-vba-event-12144
in your code, does it already.
this guy has a close app action:
http://visualbasic.ittoolbox.com/documents/closing-application-through-vba-event-12144
whats the error you get "because it cannot find outlook"
could it be reference to a different version of outlook?
could it be reference to a different version of outlook?
ASKER
Hi Sudonim. I run the task, whcih opens Access, which starts to run the code and I get the first warning. This is from Advanced Security for Outlook by MAPILabs, which bypasses outlook security and allows emails to be sent from other programs. It can't access CDO, which I'm using to send the mail in my script. Office 2010 doesn't come with CDO but I installed the add-in. I hit ok on teh 1st wanring and then get the 2nd generic error. I hit ok on this error and processing stops, my databse is left open and Outlook never does open.
If I open outlook before I run the task, everything runs thruogh fine and emails are sent out.
I was hoping to get this to run even while my server is logged off but that must be the real issue. Maybe I'm looking at this all wrong because the code needs an active signon to open the database and run correctly anyway. Any ides on a better way to handle sending email from a dataabse in this manner. Is Access even capable of this? Maybe I need to move to SQL? Anyone have any thoughts on this? NotificationError.doc
If I open outlook before I run the task, everything runs thruogh fine and emails are sent out.
I was hoping to get this to run even while my server is logged off but that must be the real issue. Maybe I'm looking at this all wrong because the code needs an active signon to open the database and run correctly anyway. Any ides on a better way to handle sending email from a dataabse in this manner. Is Access even capable of this? Maybe I need to move to SQL? Anyone have any thoughts on this? NotificationError.doc
do you have an email server? i use a gadget in C#.net to send emails with pdf files attached from exporting 2 different crystal reports....
I use a c# utils class which one of my colleagues wrote or lifted off the web. My when i run my app it loops through 700 lecturers in a dropdown list, in batches of 100, and opens 2 different crystal reports and saves them as pdf, then makes an email for the lecturers and attaches the pdfs....and i had to have it pause 3 mins after every 100, after about a 1 minute rest, the pc sends the emails it has created to the email server.
I don;t think it touches outlook at all.
I use a c# utils class which one of my colleagues wrote or lifted off the web. My when i run my app it loops through 700 lecturers in a dropdown list, in batches of 100, and opens 2 different crystal reports and saves them as pdf, then makes an email for the lecturers and attaches the pdfs....and i had to have it pause 3 mins after every 100, after about a 1 minute rest, the pc sends the emails it has created to the email server.
I don;t think it touches outlook at all.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI. als315 - thanks for post. Thats actually a useful tip but I'm still not sure that's my answer.
Secure database needs to open and run logon to get macro to run unless I'm not thinking this through right.
Sudonim, That sounds cool, what's the gadget and can you post the code or are we talking a huge program. I do have an exchange server but even if I push emails direct through server, do I still need an active signon to get Access to run my macro/code? I guess the question is still: Is it possible to run a macro within a database without actively opening that database? Is this possible in SQL? Maybe I need to open a new post since the question has changed but answer if you have some input here.
thanks guys
Secure database needs to open and run logon to get macro to run unless I'm not thinking this through right.
Sudonim, That sounds cool, what's the gadget and can you post the code or are we talking a huge program. I do have an exchange server but even if I push emails direct through server, do I still need an active signon to get Access to run my macro/code? I guess the question is still: Is it possible to run a macro within a database without actively opening that database? Is this possible in SQL? Maybe I need to open a new post since the question has changed but answer if you have some input here.
thanks guys
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, Lots of great tips here but as I said, I'll repost question as a different main topic.
nice one.
http://www.blueclaw-db.com/access_email_send_outlook.htm
Not my code, just lifted from first google search...
you probably have to add a reference to outlook library in thevba editor while editing the access code on the server, so youpick up its version of outlook library.
Open in new window