Sending an automated email via Outlook from Access 2010

I have a database for professional licenses, and I'd like to automatically send an email with the license expiration status report attached when the license is nearing it's expiration date.  I already have the query and reports ready to go, now I just need to get the automated email working properly.  Can someone please help me? Thank you.
mckenziehenryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John SalleSenior IT Systems EngineerCommented:
Instead of thinking about it sending through Outlook, you should have it send through your Email server. If that is Exchange on-site or Office 365 you can send up connectors to allow relay traffic. Otherwise you'll need to go to your mail provider with the proper settings. Typically they have some sort of mail relay options setup so end-users can email from Copiers and other devices that need it.
0
Jeffrey CoachmanMIS LiasonCommented:
Clearly Define: "nearing it's expiration date"

Sending this will be easy if all you need is a basic email.

If you need things like formatting, read receipts, attachments, ...etc, ...then things get complicated.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
What have you got so far?
You'll want to add a reference to the MS Outlook xx.0 Object Library
Start here
http://www.experts-exchange.com/articles/17466/Properly-open-Outlook-as-an-Application-object-in-VBA.html

So you get Outlook on the go

Option Explicit
Public WasOpen As Boolean
Private Function FireOutlook() As Outlook.Application
On Error Resume Next
Dim objOutlook As Outlook.Application

Set objOutlook = GetObject(, "Outlook.Application")
'MsgBox Err.Number & " " & Err.Description
If Err.Number = 429 Then
    Err.Clear
    WasOpen = False
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
Else
    WasOpen = True
End If
Set FireOutlook = objOutlook
End Function

Open in new window


Once you have an Outlook automation object on the go you create a MailItem

Set objOutlook = StartApp("Outlook.Application")
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookExplorers As Outlook.Explorers
Dim ns As Outlook.Namespace
Dim Folder As Outlook.MAPIFolder
Set ns = objOutlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderInbox)
Set objOutlookExplorers = objOutlook.Explorers
Dim DisplayMsg As Boolean
DisplayMsg = true

If wasOpen = False Then
    objOutlookExplorers.Add Folder
    Folder.Display
    'done opening
End If


' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

Open in new window


Add some recipients
        ' Add the To recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add("Nick67@ee.com")
        objOutlookRecip.Type = olTo 'to
        ' Add the CC recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add("SomeOneCC@youroutfit.com)
         objOutlookRecip.Type = olCC 'cc
         ' Add the BCC recipient(s) to the message.
          Set objOutlookRecip = .Recipients.Add("mystery@noname.com")
          objOutlookRecip.Type = olBCC
          ' Set the Subject, Body, and Importance of the message.
            .subject = "hi!"
          '.Importance = olImportanceHigh  'High importance
          'the body of the meaage
           .body = "this is the body of the message."
          'if you want HTMLBody then
          '.BodyFormat = olFormatHTML
         '.HTMLBody = "<p>This is HTML</p>
        ' add an attachment
         Set objOutlookAttach = .Attachments.Add("c:\temp\ARealFolder\ARealfile.pdf")

         ' Resolve each Recipient's name.
         For Each objOutlookRecip In .Recipients
             objOutlookRecip.Resolve
         Next
         ' Should we display the message before sending?
         If DisplayMsg Then
             .Display
         Else
            .Save
            .Send
         End If
End With


Set objOutlook = Nothing

Open in new window


more or less
Nick67
0
Nick67Commented:
and I'd like to automatically send an email with the license expiration status report attached when the license is nearing it's expiration date.

You'll still need an event to drive 'automatically'
On the main splash screen of my app there are warnings of expirations
You then go to a form, enter a timeframe, and all the possibilities appear for notices to be sent.
There's a check box for 'don't send -- like for people who you know have quit, died, left whatever-- and then a loop executes creating pdf reports and attaching them to emails and sending them out.

We get batches of 60-70 going daily.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mckenziehenryAuthor Commented:
Jeffrey--
I have the query setup to return "Expires within next 30 days" for all licenses that are within 30 days of their expiration date.  The report shows all licenses that expire within next 30 days.  I would like for the database to assess this date and send the notification email out "automatically" even when the database is not open. Does that clarify my earlier question?  

The email needs to tell recipient that all the licenses that are expiring within the next 30 days (includes state, license #, license type).  I originally planned for this to be the attached report, but if you can do it simpler without an attachment, I'm open to suggestions.  

Thank you very much.
0
Jeffrey CoachmanMIS LiasonCommented:
Expires within next 30 day
...That is what the first link illustrates:
http://www.experts-exchange.com/Software/Microsoft_Applications/Q_27688897.html#a37884180

Jeff
0
Kelvin SparksCommented:
"I would like for the database to assess this date and send the notification email out "automatically" even when the database is not open."

Access will not do anything unless it is open, and then needs some event to fire the action.

I have managed this type of setup previously by using Windows Scheduler to open the database, the database has an AutoExec Macro that executes the required actions and then closes the database. This would probably achieve what you want.

There's probably other third party products that would assist with this as well.


Kelvin
0
Nick67Commented:
There's probably other third party products that would assist with this as well.
Like VBScript
What you can automate in VBA you can automate in VBScript.
What you can't do is any WinAPI stuff.

But you could conceivably execute all this in VBScript.
0
mckenziehenryAuthor Commented:
Thank you all for your feedback.

Jeffrey,
I'm not getting any error messages nor is any of this lighting up red:
 Dim rst As DAO.Recordset
 Set rst = CurrentDb.OpenRecordset("SELECT * FROM me.TblLicenseInfo WHERE License Expiration Date=#" & Format(DateAdd("d", -30, Now), "mmddyyyy") & "#")

 If Not (rst.EOF And rst.BOF) Then
   Do While Not rst.EOF
   '/ send the email
   DoCmd.SendObject acSendNoObject, , "", , "mckenzie.henry@woodgroup.com", , , "License is Expiring", "The licenses listed below are expiring within the next 30 days, please take appropriate action" & vbCrLf & "State" & rst!State & vbCrLf & "LicenseNumber" & rst!LicenseNumber & vbCrLf & "LicenseType" & rst!LicenseType, False
   
   rst.MoveNext
   Loop
 End If

However, and I'm sure this is a dumb question, I'm not sure exactly where to put it.  When I open the VBA editor, it's showing up on a report; shouldn't this be associated with the applicable table?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.