How to automate Access Query Results into Email via Outlook?

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

WEBEJOHAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<  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
 
rockiroadsCommented:
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
 
SheilsCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Jeffrey CoachmanMIS LiasonCommented:
The code is "Frankenstein-ed" together from at least 3 different snippets...
0
 
Helen FeddemaConnect With a Mentor Commented:
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
 
Helen FeddemaCommented:
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
 
WEBEJOHAuthor Commented:
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
 
WEBEJOHAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
No problem, I am glad we gave you a good first impression.


;-)

Jeff
0
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.