Solved

Open outlook to send mail from access database

Posted on 2011-03-03
10
959 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:valmatic
  • 6
  • 3
10 Comments
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
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

0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
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

0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
whats the error you get "because it cannot find outlook"

could it be reference to a different version of outlook?
0
 
LVL 7

Author Comment

by:valmatic
Comment Utility
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
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 39

Assisted Solution

by:als315
als315 earned 100 total points
Comment Utility
Here is same problem described:
http://www.ozgrid.com/forum/showthread.php?t=73886&page=1
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.
0
 
LVL 7

Author Comment

by:valmatic
Comment Utility
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
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 400 total points
Comment Utility
that code is in c# win app not access though.

utilities.cs:
 
#region Send Mail
        public static void SendMail (string strTo, string strFrom, string strBCC, string strMessage, string strSubject, string file1, string file2)
        {

             emailerror:           
            if (strTo == null)
            {

                strTo = "sball@dmu.ac.uk";
                //strTo = "tmason@dmu.ac.uk";
            }

            MailMessage mail = new MailMessage();

            //set the addresses
            mail.From = new MailAddress(strFrom);
            mail.To.Add(strTo);
            mail.Bcc.Add(strBCC);

            //set attachments
            if (file1 != "")
            {
                Attachment data = new Attachment(file1);
                mail.Attachments.Add(data);
            }

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

            //send the message
            try {
            SmtpClient smtp = new SmtpClient();
            smtp.Send(mail);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                strTo = null;
                goto emailerror;
            }
        }

		#endregion

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 (vis4.email == "")
                            {
                                txtStaff.Text = vis4.email;
                                stfEmail = "sball@dmu.ac.uk";
                                //create email
                                emailSubj = cmdStaffCode.Text + " Attendance Monitoring Registers";
                            }
                            else
                            {

                                txtStaff.Text = vis4.email;
                                stfEmail = vis4.email;

                                //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");

                            //SendEmail(bigemailtxt);

                            //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 = "sball@dmu.ac.uk";

                               Utilities.SendMail(stfEmail, "qls@dmu.ac.uk", "qls@dmu.ac.uk", emailBody, emailSubj, AttachPath1, AttachPath2);





                        }

Open in new window

0
 
LVL 7

Author Closing Comment

by:valmatic
Comment Utility
Thanks,  Lots of great tips here but as I said, I'll repost question as a different main topic.
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
nice one.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Outlook Free & Paid Tools
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now