Solved

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

Posted on 2015-02-07
8
53 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
  • 3
  • 3
8 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
...Nice code there Ron...
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Thanks Jeff.
:-)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now