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

Open in new window

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]"
    '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])
                    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])
                    End With
                End If
            End If

'chopped off rest of code here as it is more of the same with different parms.

Open in new window

Who is Participating?
Simon BallConnect With a Mentor Commented:
that code is in c# win app not access though.

#region Send Mail
        public static void SendMail (string strTo, string strFrom, string strBCC, string strMessage, string strSubject, string file1, string file2)

            if (strTo == null)

                strTo = "";
                //strTo = "";

            MailMessage mail = new MailMessage();

            //set the addresses
            mail.From = new MailAddress(strFrom);

            //set attachments
            if (file1 != "")
                Attachment data = new Attachment(file1);

            if (file2 != "")
                Attachment data2 = new Attachment(file2);
            //set the content
            mail.Subject = strSubject;
            mail.Body = strMessage;

            //send the message
            try {
            SmtpClient smtp = new SmtpClient();
            catch (Exception ex)
                strTo = null;
                goto emailerror;


Open in new window

code run for each staff member to make their email, including importing the txt for the email body from a file.

foreach (get_edir_from_staff_codeResult vis4 in res4)
                            if ( == "")
                                txtStaff.Text =;
                                stfEmail = "";
                                //create email
                                emailSubj = cmdStaffCode.Text + " Attendance Monitoring Registers";

                                txtStaff.Text =;
                                stfEmail =;

                                //create email
                                emailSubj = "Attendance Monitoring Registers";

                            emailSubj += " Week 23";

                            string words = vis4.fnames;
                            string[] split = words.Split(new Char[] { ' ', ',', '.', ':', '\t' });

                            Salute = split[0].ToString() + ' ' + vis4.sname.TrimEnd() + ",";
                            string bigemailtxt = System.IO.File.ReadAllText("D:\\DEVELOPMENT\\winAps\\WinappCrystalASLWStaff\\WinappCrystalASLWStaff\\bodytxt.txt");


                            //string emailBody = "Dear " + stfForename + " " + stfSurname + " \r\n\r\n";
                            //    string emailBody = "Dear " + cmdStaffCode.Text + ", \r\n\r\n";
                            string emailBody = "Dear " + Salute + " \r\n\r\n";

                            //emailBody += "Thankyou for registering your graduation requirements using DMU Graduation Ceremony Web Booking \r\n\r\n";
                            emailBody += bigemailtxt + "\r\n\r\n";
                            //emailBody += "\r\n\r\n Regards\r\n\r\nGrad & Conf Support";

                            //string strEmail = "";

                               Utilities.SendMail(stfEmail, "", "", emailBody, emailSubj, AttachPath1, AttachPath2);


Open in new window

Simon BallCommented:
i've never tried it with outlook, but i believe you can get access to open outlook like the other office apps..

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.
Select Case Me.Email_Output_Option
    Case 1
        Dim mess_body As String
        Dim rst As DAO.Recordset
        Dim appOutLook As Outlook.Application
        Dim MailOutLook As Outlook.MailItem
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(olMailItem)
        Set rst = Form_F_People_Mailings.RecordsetClone
        Do While Not rst.EOF
            If IsNull(rst!Email) Then
                MsgBox "skipping " & _
                Form_F_People_Mailings.LastName & _
                " no email address."
                GoTo skip_email
            End If
            mess_body = "Dear " & rst!Salutation & " " & _
            rst!LastName & "," & _
            vbCrLf & vbCrLf & Me.Mess_Text
            Set appOutLook = CreateObject("Outlook.Application")
            Set MailOutLook = appOutLook.CreateItem(olMailItem)
            With MailOutLook
                .To = rst!Email
                    .Subject = Me.Mess_Subject
                    .Body = mess_body
                    If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
                        .Attachments.Add (Me.Mail_Attachment_Path)
                    End If
                    'next line would let MS Outlook API send the note
                    'without storing it in your sent bin
                    '.DeleteAfterSubmit = True
                End With

Open in new window

Simon BallCommented:
sorry, just re-read.

in your code, does it already.

this guy has a close app action:

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Simon BallCommented:
whats the error you get "because it cannot find outlook"

could it be reference to a different version of outlook?
valmaticAuthor Commented:
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
Simon BallCommented:
do you have an email server?  i use a gadget in 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.
als315Connect With a Mentor Commented:
Here is same problem described:
Autor is closing all outlook instances before creating message and then opening it.
You will need additional error handling (I can't open outlook immediately after closing, but, if this sub is starting twice, all is working).
You can also close Outlook after task is completed and start it every time without check.
valmaticAuthor Commented:
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
valmaticAuthor Commented:
Thanks,  Lots of great tips here but as I said, I'll repost question as a different main topic.
Simon BallCommented:
nice one.
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.