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

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]"
    
    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.

Open in new window

Si Ball

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

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.

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
        rst.MoveFirst
        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
                    .Send
                End With

Open in new window

Si Ball

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

Si Ball

whats the error you get "because it cannot find outlook"

could it be reference to a different version of outlook?

valmatic

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

Si Ball

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.

SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

valmatic

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

ASKER CERTIFIED SOLUTION

Si Ball

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

valmatic

ASKER

Thanks, Lots of great tips here but as I said, I'll repost question as a different main topic.

Si Ball

nice one.