• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

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

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
eyes59
Asked:
eyes59
  • 3
  • 3
4 Solutions
 
Jeffrey CoachmanCommented:
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
 
IrogSintaCommented:
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
 
eyes59Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jeffrey CoachmanCommented:
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
 
IrogSintaCommented:
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
 
Jeffrey CoachmanCommented:
...Nice code there Ron...
0
 
IrogSintaCommented:
Thanks Jeff.
:-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now