Solved

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

Posted on 2015-02-07
8
74 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 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