Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to automate Access Query Results into Email via Outlook?

Posted on 2010-09-03
9
Medium Priority
?
829 Views
Last Modified: 2012-05-10
Using Access 2003 & Outlook 2003.

I have a simple database that I have a table with email addresses & user names that I want to use to generate an email in outlook from Access.  I seem to have that part working but have two other issues to deal with.

1. I have a query which it's results create a many to one relationship with the user who I want to email.  I'd like to create the email and have the body of the email contain the results of the query for only that user.

2. When Access creates the Outlook email based on my current code it launches the Outlook Security prompt for each email it creates.

I've done some research on both issues but I'm fairly a novice and I need to get this project moving any help is appreciated.  I'm attaching my current code.  The query I need to pull records into the body of the email is called "Event_Email".

Thanks for any assistance.

John

john.weber@chrobinson.com

Public Function SendEmail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset

'Events_Email will be the query showing current events
Dim Event_Email As DAO.Recordset

Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim SubjectLine As String
Dim MyBody As textstream
Dim MyBodyText As String
Dim MyNewBodyText As String
Dim StopRep As Variant
Dim strStopRep As String


Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)

SubjectLine = "IMDL Event Log Report" & " " & Date

Set db = CurrentDb()


'Setting mail list to look at the Email Address table
Set MailList = db.OpenRecordset("Emailer_Address")

Set Event_Email = db.OpenRecordset("Event_Email")

Do Until MailList.EOF

'Set MyBody
MyBodyText = "test"

'may be missing link
Set MyMail = MyOutlook.CreateItem(olMailItem)

MyMail.To = MailList("Email")

MyMail.Subject = SubjectLine

MyMail.Body = MyBodyText

MyMail.Send

MailList.MoveNext

Loop

Set MyMail = Nothing

Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

Open in new window

0
Comment
Question by:WEBEJOH
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33600630
Regarding the security prompt there are options like 3rd party s/w or rewrite code using redemption or outlook
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26450355.html
or the tool that Patrick mentioned which looks neat (I need to look at it) MAPILab
0
 
LVL 16

Expert Comment

by:Sheils
ID: 33600694
I think the sendobject method is better suited for what you want to achieve


Syntax is:

DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

So your code becomes:

DoCmd.SendObject(acSendQuery, "QueryName, acFormatRTF, Me.EmailAddress, , , Subject)
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 800 total points
ID: 33601670
<  I'd like to create the email and have the body of the email contain the results of the query for only that user.>

Notes:

This will not be straightforward as:
1.  Do you want the data "Formatted"?
Remember the way that you normally view a query (Datasheet View) needs "Formatting" to appear the same way in the email body
So for that you can't use SendObject..
You have to use the code you posted substituting: .Body with: .htmlBody
2. Most email filtering programs will flag messages containing HTML in the message body as Spam, and immediately send them to the recipients Junkemal folder.
3. This will be "A LOT" easier if you used a filtered report instead of a query for formatting purposes and for emailing
4. I will presume that in the loop, you are looping each user and each user has an email address field.

Here is perhaps my hardest fought sample file.
It is brute force, but it works.

In order to run it you must:
Load a reference to the Microsoft Outlook Object Library in your VBA Editor.
Load a reference to the Microsoft Scripting runtime Library in your VBA Editor.
Load you own Email Address into each employee record, so as a test, all the emails go to you.

Again, this is very sloppy, but it does work!
;-)
If it works, I will promise to clean it up tomorrow for you.

:-)

JeffCoachman

JeffCoachman

Access-EEQ26451300-EmailDisplayH.mdb
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33601672
The code is "Frankenstein-ed" together from at least 3 different snippets...
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 200 total points
ID: 33603223
Here is some simple code to create an email for each contact in an Access recordset:
Public Sub EMailAllContacts()
'Created by Helen Feddema 31-Oct-2009
'Last modified by Helen Feddema 31-Oct-2009

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim appOutlook As Outlook.Application
   Dim msg As Outlook.MailItem
   Dim strEmail As String
   
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("qryContacts")
   Set appOutlook = GetObject(, "Outlook.Application")
   
   Do While Not rst.EOF
      strEmail = Nz(rst![EmailName])
      If strEmail <> "" Then
         'Create email
         Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEmail
         msg.Subject = "Subject"
         msg.Body = "Message"
         
         'Comment out next line and uncomment Send line
         'to send automatically
         msg.Display
         'msg.Send
      End If
      rst.MoveNext
   Loop
   
ErrorHandlerExit:
   rst.Close
   Set rst = Nothing
   Set appOutlook = Nothing
   Exit Sub

ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in EMailAllContacts procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 33603230
To avoid the annoying security messages, use the Redemption Library and rewrite part of the code like this:

'Redemption objects and corresponding Outlook objects must
   'be declared as Object type
   Dim omsg As Object
   Dim msg As Object
   Dim otsk As Object
   Dim tsk As Object
   
      'Create new mail message and send it now
      Call OpenOutlook
      Set omsg = gappOutlook.CreateItem(olMailItem)
      Set msg = New Redemption.SafeMailItem
      msg.Item = omsg
      
      With msg
         .To = strToEMail
         .Subject = strMessageSubject
         .Body = strBody
         .Send
      End With
      
      'Note:  Messages created with Redemption Library objects
      'are created in the Drafts folder instead of the Outbox,
      'but they will be sent as if from the Outbox.

Open in new window

0
 

Author Closing Comment

by:WEBEJOH
ID: 33618715
Thank you very much for your assistance.  This was my first question post and I'm supermely impressed with the responses and willingness to help out!
0
 

Author Comment

by:WEBEJOH
ID: 33618768
All of the responses were helpful and a special thanks to boag2000 and Helen for teaching me up to find the solutions that I needed.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33618998
No problem, I am glad we gave you a good first impression.


;-)

Jeff
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

824 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