Solved

load ACCESS 2003 query result into the body of an OUTLOOK 2010 email message

Posted on 2015-02-07
8
77 Views
Last Modified: 2016-07-12
I am trying to have the result of an ACCESS 2003 query load into the subject line & body
of an OUTLOOK 2010 email.  The code is below:


Option Compare Database
'------------------------------------------------------------
' Assigned Work
''------------------------------------------------------------
Function  AssignedWork()
On Error GoTo AssignedWork_Err

Dim rs As DAO.Recordset
Dim strDate As Date
Dim strCount As Integer

Set rs = CurrentDb.OpenRecordset("QryAssignedWrk")

rs.MoveFirst
Do Until rs.EOF
   
Loop

strCount = [Wrk_ident]
strDate = Format(Now() - 1, "mm/dd/yyyy")

    'send the query result in  an email msg
DoCmd.SendObject , "", "", "aName@something.com", "", "", &strCount"-Assigned Work Items  on"&strDate, "Assigned Work count is" & strCount&"If you nolonger need this information please contact me.  mYname@something.com, False, ""

Set rs = Nothing

    DoCmd.SetWarnings False
   
    DoCmd.Quit

AssignedWork_Exit:
    Exit Function

AssignedWork _Err:
    MsgBox Error$
    Resume AssignedWork _Exit

End Function


I want the subject line of the email to read:  [strCount] Work Items Assigned on [strDate]
I want the body of the email to read:  Assigned Work count is  [strCount] for [strDate]. If you nolonger need this information please contact me.  myName@something.com.    

Two issues occur the first is the loop will not stop, the second is the email is not sent.


Thoughts?

Thank You
0
Comment
Question by:eyes59
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 40596458
Note that it is *Much* easier to simply attach an HTML, Excel, ...etc. version of the Query to the email.
DoCmd.SendObject acSendQuery, "YourQuery", acFormatHTML, "RecipientEmail", , , "YourSubject", "YourEmailBodytext"

Open in new window



The issue here is that a query is an Access construct and cannot really "display" in an email...
...Unless you save it as an HTML document first.
Then read it in, line by line, into the email body (with the .HTML Body element.)

Here is the code:
Private Sub btnEmail_Click()
'(Note. You must set a reference to the MS Outlook Object Library in the VBE)
'Notes: I swear, this worked once with Office 2007/FireFox
'But since then I get: "This page uses frames, but your browser doesn't support them."
'...in the email body...
'Excel format version?, Office Version?, Default browswe/ Browser version???
'?
'This was the actual solution: http://www.experts-exchange.com/Microsoft/Applications/Q_26529605.html#a33880306
Dim strSQL As String
    
    '----- Save HTML file as a "String"
    '(From Ozgrid.com, THANKS!
    'http://www.ozgrid.com/forum/showthread.php?t=61124&page=1)
    Dim TempFile As String
    Dim fso As Object
    Dim ts As Object
    Dim strMSG As String
    'Location of the HTML file
    TempFile = "c:\YourFolder\YourQuery.html"


    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

    strMSG = ts.ReadAll

    ts.Close
    Set ts = Nothing
    Set fso = Nothing
    '-----------------------------------
    
    '-----Send the email -------------------------------------------
    ' Modified from:
    'http://support.microsoft.com/kb/161088
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    'Dim objOutlookAttach As Outlook.Attachment
    
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
    With objOutlookMsg
        ' Add the To recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add("RecipientEnmailAddress")
        objOutlookRecip.Type = olTo
    
    '    ' Add the CC recipient(s) to the message.
    '    Set objOutlookRecip = .Recipients.Add("Michael Suyama")
    '    objOutlookRecip.Type = olCC
    '
    '   ' Add the BCC recipient(s) to the message.
    '    Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
    '    objOutlookRecip.Type = olBCC
    
       ' Set the Subject, Body, and Importance of the message.
       .Subject = "Your Subject"
       .HTMLBody = strMSG
       '.Importance = olImportanceHigh  'High importance
    
    '   ' Add attachments to the message.
    '   If Not IsMissing(AttachmentPath) Then
    '       Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    '   End If
    
       ' 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
    '-------------------------------------------------------------

End Sub

Open in new window


Hope this helps

JeffCoachman
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 250 total points
ID: 40596496
The loop will not stop because you do not have the line rs.MoveNext to go the next record.  What is the purpose of your loop anyway?  Where you trying to create a delimited string that had all the records in your query so that you could add the string to the body of your email?  Or where you just trying to get the count of the records?  If you just needed a count, the loop is unnecessary.  

You mentioned that you wanted the body of the email to read: Assigned Work count is  [strCount] for [strDate]. If you no longer need...  . Does this mean that you don't need the query result into the body?  Were you just going to send the query as an attachment on the email?

As a side note, your naming convention is confusing.  You declared your variables as a Date and Integer data type yet the names you gave them start with "str" which is meant to designate String data types.  Better names to use would be:
Dim dtAssigned as Date
Dim iCount as Integer

Ron
0
 

Author Comment

by:eyes59
ID: 40596834
The result of the query will always be one record.   I want the result of the query to be in the body of the email.  Thank you for the naming convention information.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 40596885
I want the result of the query to be in the body of the email.
The code I posted should get you what you want...
http://www.experts-exchange.com/Database/MS_Access/Q_28612480.html#a40596458
...but as I also stated, ...this would only need to one line of code if you simply attached the query to the email...
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 250 total points
ID: 40597107
If you want to send emails where the body is nicely formatted, then sending the query or report via HTML as Jeff pointed out is how you should go about doing it.  For simple emails where the formatting isn't that important you can do something like this.  The caveat is that you have to play with the number of tabs in order for the fields to be spaced properly in the body of your email.

This code has sample headers that you would change to match yours. Since you say that the query will always return a single record, I'm assuming then that the field [Wrk_ident] gives the number of Assigned Work Items.  No need to loop through the recordset since you said it always returns a single record.  As for dtAssigned, you have that set to Date-1.  It makes more sense to use a field in your recordset for this if there is one.

'------------------------------------------------------------
 ' Assigned Work
 ''------------------------------------------------------------
Function AssignedWork()
    On Error GoTo AssignedWork_Err
    
    Dim rs As DAO.Recordset
    Dim dtAssigned  As Date
    Dim iCount As Integer
    Dim sSubject As String
    Dim sBody As String
    
    Set rs = CurrentDb.OpenRecordset("QryAssignedWrk")  
   
    If Not rs.EOF Then
        iCount = rs![Wrk_ident]
        dtAssigned = Date - 1     'this should probably be something like rs![Wrk_Assigned_Dt]
        sSubject = iCount & " Work Items Assigned on " & dtAssigned
        sBody = "Assigned Work count is " & iCount & ". If you no longer need this information please contact me.  myname@something.com" & vbCrLf & vbCrLf

        sBody = sBody & "ACCOUNT#" & vbTab & "DATE PAYMENT WAS POSTED" & vbTab & vbTab & "AMOUNT" & vbCrLf
        sBody = sBody & "========" & vbTab & "=======================" & vbTab & vbTab & "======" & vbCrLf & vbCrLf
        
        sBody = sBody & rs!BANK_ACCT & vbTab & rs!PostDate & vbTab & vbTab & rs!Amount & vbCrLf
    End If

    rs.Close
    Set rs = Nothing
    
    'send the query result in  an email msg
    DoCmd.SendObject , , , "someone@someplace.com", , , sSubject, sBody, True
    Exit Function
     
AssignedWork_Exit:
    Exit Function

AssignedWork_Err:
    MsgBox Error$
    Resume AssignedWork_Exit

 End Function

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40597352
...Nice code there Ron...
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40597368
Thanks Jeff.
:-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

717 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