email : query in body 1

[matrixindicator]

I worked before with email and attachtments in access. Can I insert a query on his own in the body of my email ?

The query is a summary and of course I can use recordset for the data, but in this case I like to automatically insert the query with his desing in the body. How ?

 

[Remou]

Try something like this:

Sub RTFBodyX()
Const ForReading = 1, ForWriting = 2, ForAppending = 3

Dim fs, f
Dim RTFBody, strTo
Dim MyApp As New Outlook.Application
Dim MyItem As Outlook.MailItem

DoCmd.OutputTo acOutputQuery, "Query1", acFormatHTML, "Query1.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("Query1.htm", ForReading)

RTFBody = f.ReadAll
'Debug.Print RTFBody

f.Close

Set MyItem = MyApp.CreateItem(olMailItem)
With MyItem
   .To = "a@b.c"
   .Subject = "txtSubjectLine"
   .HTMLBody = RTFBody
End With
MyItem.Display
End Sub

 

[matrixindicator]

ok, txs Ramou, I do a test and come back to give some feedback.

 

[matrixindicator]

Remou,

I have done a test, its ok, just one little issue.
Now he is sending the mail directly, I just want him to open it so I can verify visually the content before sending.

What little code do I need to add to show before sending ?

Public Function test() As Boolean
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const PROCNAME = "test"
    Dim mail As New smtpMailer
    
    On Error GoTo ErrHandler
    Dim fs, f
    Dim RTFBody, strTo

    DoCmd.OutputTo acOutputQuery, "Query1", acFormatHTML, "Query1.htm"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile("Query1.htm", ForReading)

    RTFBody = f.ReadAll
    
    With mail
        .subject = "TEST"
        If Not .addRecipient(, "p@i.nl", ttTo) Then GoTo Finish
        .BodyHTML = "<h1>Testmail</h1>"
        .BodyHTML = RTFBody

        If Not .Send Then GoTo Finish
    End With
    
    GoTo Finish
ErrHandler:
  MsgBox PROCNAME & vbCrLf & "(" & Err.Number & ") " & Err.Description
Finish:
    Set mail = Nothing
End Function

 

[bubba100]

Try replacing .Send with .Display