Results 1 to 3 of 3
  1. #1
    Kaye960 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2015
    Posts
    9

    Adding query results to email body

    I have a module that is working that creates a query and attaches the results to and email and sends it to a distribution list. What I would like to do is to put those results in the body of the email so that the results are easier to see and eventually figure out how to make it so that if there are no results no email is sent.
    Code:
    Function Send_Negatives()On Error Resume Next
    'Set a Reference to the Microsoft Outlook ?X.X Object Library
     'Get Outlook if it's running
     Dim oApp As Object
    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
         'Outlook wasn't running, start it from code
         Set oApp = CreateObject("Outlook.Application")
        
    End If
    Dim strReportName As String
    Dim oLook As Object
    Dim oMail As Object
    
    
    Dim strTO As String
    Dim strMessageBody As String
    Dim strSubject As String
    Dim HTMLBody
    strReportName = "Negatives"
    DoCmd.OutputTo acOutputQuery, "qry_SN_DailyNegativeQty", acFormatHTML, "R:\Sandy's playground" & "\" & strReportName & ".htm", False
    
    
    
    
     
    Set oLook = CreateObject("Outlook.Application")
    
    
    
    
    Set oMail = oLook.CreateItem(0)
     
    '*********************** USER DEFINED SECTION ************************
    strTO = "Snesmith@ctmmedia.com"
    strMessageBody = "Here is the negative report"
    strSubject = "Negatives"
    '*********************************************************************
     
    With oMail
     .To = strTO
     .Body = strMessageBody
     .Subject = strSubject
    .Attachments.Add "R:\Sandy's playground" & "\" & strReportName & ".htm"
       '.Display
         .Send
    End With
    
    
    Set oMail = Nothing
    Set oLook = Nothing
    
    
        
    End Function
    I figured out how to send it in HTML, but I can't figure out how to put it in the body of the email.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Kaye960 View Post
    ...
    I figured out how to send it in HTML, but I can't figure out how to put it in the body of the email.
    There are several ways to approach this. If you were to continue to go with your Report that is exported as an HTML file, you would have to read each line of the file and concatenate each line within to your strMessageBody. You should be able to read each line and assign it to your string variable using TextStream.

    The following is from my personal notes:
    Code:
    'Reference Microsoft Scripting Runtime
    Dim fso As New FileSystemObject
    Dim txsFileChars As TextStream
    Dim objMyTextFile As File
    Dim strFileName As String
    Dim strFilePath As String
    Dim strReadLine As String
    
    strFilePath = "C:\Test\TextFiles\"
    strFileName = "SmallTextFile.txt"   'You will want to create a Folder object and enumerate file objects
    
    Set objMyTextFile = fso.GetFile(strFilePath & strFileName)
    Debug.Print objMyTextFile.Name
    Set txsFileChars = objMyTextFile.OpenAsTextStream(ForReading)
    While txsFileChars.AtEndOfStream = False
    strReadLine = txsFileChars.ReadLine
    Debug.Print strReadLine
    Wend
    What I would probably do is write out some html in VBA and use variables for the various fields. A query opened with DAO would assign values to the variables as I looped through the recordset and sent emails. So strMessageBody would look something like.

    Code:
    strMessageClose = "</body></html>"
    strMessageBody = "<!doctype html><html><head><meta charset="utf-8"><title>Negatives</title></head><body>"
    strMessageBody = strMessageBody & "<p>Dear&nbsp;" & strCustomerName & ":</p>"
    strMessageBody = strMessageBody & "<p>The status of your order is now&nbsp;" & strOrderStatus & ".</p>"
    strMessageBody = strMessageBody & strMessageClose
    Another option may be to use the Outlook Object you have opened with VBA and tell it to use a template for the body. I believe you can use a word doc and our an HTML page as a template for the body. So you should be able to use Merge Fields in a Word doc and, somehow, use the result as a template for each of your emails.

  3. #3
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Take @ItsMe's approach to getting the html into a string. Then pass it back to your oMail object. Note you'll need to ensure the format is set as well.

    See: https://msdn.microsoft.com/en-us/lib.../ff869635.aspx

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-18-2015, 11:24 AM
  2. Email report as body of email (RTF)
    By TheDeceived in forum Programming
    Replies: 4
    Last Post: 07-23-2012, 06:39 AM
  3. Email report as body of email
    By chrish20202 in forum Programming
    Replies: 6
    Last Post: 01-15-2012, 07:23 PM
  4. Query displayed as body of email - edit
    By virnier in forum Access
    Replies: 0
    Last Post: 03-16-2011, 01:26 PM
  5. Email from report to Email body
    By Ehmke66 in forum Programming
    Replies: 4
    Last Post: 01-03-2011, 01:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums