Sent a report showing directly on the mail 1

[haerion]

Hi all,

I have the following problem, I need to send Purchased Order by mail which take the info from a query named "Query to Mail by Vendor" and containing the following information:
PO
Address to ship
Address to bill
line info (qty, item, price)
Buyer

The thing is that I want it directly on the mail, and I'm not sure how to do this.

Thanks for you help!

Haerion

 

[PHV]

Have a look at the DoCmd.SndObject method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haerion]

Hi PHV, thanks for your answer, unfortunately, I am already using the sendobject, what im trying to do is to put all the info on the body of the message instead of a snapshot or txt attachment

 

[Remou]

How about:

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 acOutputReport, "Report1", acFormatRTF, "Report1.rtf"
DoCmd.OutputTo acOutputQuery, "Query1", acFormatHTML, "Query1.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("Query1.htm", ForReading)
'Set f = fs.OpenTextFile("Report1.rtf", ForReading)
RTFBody = f.ReadAll
f.Close

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

The code also shows commented report output.

 

[haerion]

Hi Remou, thanks a lot for your reply, I'm actually working with it and it is a nice code, the only thing is that the best work out is the rtf one and when I try this one on the mail, it give me something like that:

{\rtf1\ansi\ansicpg1252\paperh15840\paperw12240\margl1368\margr1116margt1296\margb720\psz1{\colortbl\red0\green0\blue0;\red255\green255blue255;}{\fonttbl\f0\fcharset0\fnil Arial;\f1\fcharset0\fnil Comic Sans MS;\f2\fcharset0\fnil Comic Sans MS;\f3\fcharset0\fnil Comic Sans MS;\f4\fcharset0\fnil Comic Sans MS;\f5\fcharset0\fnil Times New Roman;\f6\fcharset0\fnil Times New Roman;\f7\fcharset0\fnil Times New Roman;}\pard\plain\sb1860\tqc\tx2280\tx5505{\plain\tab\fs28\b\f1\cf0\cb1 P5031\plain\tab\fs20\f2\cf0\cb1 Trane Quebec{\fs42\par}}\pard\plain\tx5505{\plain\tab\fs20\f2\cf0\cb1... and some more

You have any idea to correct it?

Thanks for your help

 

[haerion]

If I copy paste the RTF onto the mail it work fine tough, any idea how I could do the copy paste with the macro (not good enough to create my own macro yet ), it is just an idea if I can't make the OpenTextFile work with the rtf, since html change the display too much.

Thanks again!

 

[deek05]

What about sending a "snapshot" of the report?

 

[haerion]

It was what i tough first, the only problem with snp is that it is not everyone that can view it unless they already have access or go download it, but since i have a lot of supplier, it will be easier if i can just put it directly on the mail If it was not for this i would have probably go the snapshot way!

 

[NYFashionToGo]

Have a look at this post, I am in same boat as you trying to do the same thing. It does not send it directly from the report however you can build a email based off the same query you built the report off of.... I still am trying to figure this out myself.. But i think this is useful

thread702-396121

 

[deek05]

I ended up sticking a the snapshot download link in the email, so if they didn't have it they could download it.

I would have liked to show the form/report in the email also but we couldn't get it to show what we wanted. Good Luck!

I've got an idea...I'll get back to you if it works!

 

[Remou]

Change
[tt].HTMLBody = RTFBody[/tt]
To
[tt].Body = RTFBody[/tt]

Hopefully that will fix it.

 

[haerion]

Hum... sorry Remou, seem like it does the same thing, it give me a lot of unwanted stuff like I pasted earlier, the :

{\rtf1\ansi\ansicpg1252\paperh15840\paperw12240\margl1368\margr1116margt1296\margb720\psz1{\colortbl\red0\green0\blue0;\red255\green255blue255;}{\fonttbl\f0\fcharset0\fnil Arial;\f1\fcharset0\fnil Comic Sans MS;\f2\fcharset0\fnil Comic Sans MS;\f3\fcharset0\fnil Comic Sans MS;\f4\fcharset0\fnil Comic Sans MS;\f5\fcharset0

 

[Remou]

It sems you are outputting RTF, can you switch that to HTML and change .Body back to .HTMLBody?

[tt]DoCmd.OutputTo acOutputQuery, "Query1", acFormatHTML, "Query1.htm"[/tt]

I do not have your version of Outlook, I think, so I am guessing a bit.

 

[haerion]

I got Outlook 2003 and I am outputting a report on rtf, since html change all the design of the report. Is it possible to output to rtf and after that keep the same rtf to put it back in the mail? If i do a copy paste from the rtf to the mail it is all ok, but the way i was trying it with the "Set f = fs.OpenTextFile("Report1.rtf", ForReading)" doesn't work.

I'm sure there is a way to do a copy paste from the rtf to the mail with a macro, got any idea?

 

[Remou]

Can you post the code as you changed it to suit your application, please?

 

[haerion]

Hi Remou, here is the code, same as the one you gave me, except I put the report name in it.

Private Sub ButtonMailPO_Click()
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 acOutputReport, "Mail PO", acFormatRTF, "Report1.rtf"
'DoCmd.OutputTo acOutputReport, "Mail PO", acFormatHTML, "Query1.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
'Set f = fs.OpenTextFile("Query1.htm", ForReading)
Set f = fs.OpenTextFile("Report1.rtf", ForReading)
RTFBody = f.ReadAll
f.Close

Set MyItem = MyApp.CreateItem(olMailItem)
With MyItem
.To = "test@test.com"
.Subject = "Subject"
.body = RTFBody
End With
MyItem.Display
End Sub

The Report1.rtf is perfect, i need to do an exact copy of it on the mail. I tried to manually copy paste and it work perfect, what would the code be to make it automatically? It is only with the :
Set f = fs.OpenTextFile("Report1.rtf", ForReading)
RTFBody = f.ReadAll

that it doesn't work corectly and that i get the "{\rtf1\ansi\ansicpg1252\paperh15840\paperw12240\margl1368\margr1116margt1296\margb720\psz1{\colortbl\red0\green0\blue0;\red255\green255" thing.

Thanks again for you help remou, I didn't know about this code you showed me first and it gonna help me in some other program too, a really good code indead.

Btw, can you tell me how to put the code in a Code box in the forum? i see it a lot but got no idea how to do it lol.

 

[Remou]

To put code in a code box, use [ignore]

Code Here

[/ignore]. You will get more information from the Process TGML link below.

The

"{\rtf1\ansi\ansicpg1252\paperh15840\paperw12240\margl1368\margr1116margt1296\margb720\psz1{\colortbl\red0\green0\blue0;\red255\green255"

thing is RTF. When you run the code shown above through, is the RTF not formatted? This is a change from Outlook 2000. Try adding

[tt].BodyFormat = olFormatRichText[/tt]

Before .Body, I can only guess as I do not have a copy of 2003 and the code works in 2000.

 

[haerion]

Seem like it doesnt change anything, it say that I am in rich text but still get the same thing
Other ideas?

 

[haerion]

Should I use the tristate to get the format of the rtf when it open?