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

Posted on 2015-02-07
Medium Priority
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")

Do Until rs.EOF

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

    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.


Thank You
Question by:eyes59
  • 3
  • 3
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1000 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!
    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

    Set ts = Nothing
    Set fso = Nothing
    '-----Send the email -------------------------------------------
    ' Modified from:
    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
    '   Else
'           .Save
'           .Send
    '   End If
    End With
    Set objOutlook = Nothing

End Sub

Open in new window

Hope this helps

LVL 29

Assisted Solution

IrogSinta earned 1000 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


Author Comment

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.
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'.

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1000 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...
...but as I also stated, ...this would only need to one line of code if you simply attached the query to the email...
LVL 29

Accepted Solution

IrogSinta earned 1000 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

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

    MsgBox Error$
    Resume AssignedWork_Exit

 End Function

Open in new window

LVL 74

Expert Comment

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

Expert Comment

ID: 40597368
Thanks Jeff.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

586 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