Solved

Send an Access Report via email in the message body

Posted on 2004-03-22
35
777 Views
Last Modified: 2010-05-18
Send an Access Report via email in the message body

I want to be able to send reports by email and have the report be the message body of the email and not an attachment.  (Like when using the Send Page by email function of Internet Explorer)

I have used the following to send reports via email as Attachments.

Dim stDocName As String
stDocName = "rptReport"
DoCmd.OpenReport stDocName, acPreview, , "[PersonID] ='" & Me.PersonID & "'"
DoCmd.SendObject acSendReport, "rptReport", acFormatSNP, "email@email.com", , , "Vacation approval request", "Please review the attached vacation request and approve it via the database", False

Is there a way to send the report within the Message body?

Thanks
Omron60
0
Comment
Question by:omron60
  • 11
  • 11
  • 7
  • +1
35 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 10650387
Hi omron60,
Its the .Body

    Set Out = CreateObject("Outlook.Application")                'Email the Items to Suppliers
    With Out.CreateItem(olMailItem)
           .Recipients.Add EmailAddress
'           .Recipients.Add(cc1).Type = olCC
'           .Recipients.Add(cc2).Type = olCC
           .Subject = SubjectText
           .Body = MessageText
           .Attachments.Add "c:\RFQ-Letter.rtf"                  'Attach the printed Report from c:\
           .Send
    End With

Hope this helps

Jaffer
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10650440
just like Jaffer said

simiilar solution here

http://www.experts-exchange.com/Databases/MS_Access/Q_20916531.html

0
 

Author Comment

by:omron60
ID: 10650513
Sorry about the ignorance but where do I place this code?

Omron60
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 65

Expert Comment

by:rockiroads
ID: 10650623
whichever code you take, you best place it in a module

create as a general function so it can be reused (like my post link - it takes parameters)

then you simply call that module name

e.g.

public sub SendItMate()
 ...
end sub

0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10650629
it all depends on how you want to send your email,
if you are reading infro from Tables like, his/her emailaddress,
then you will have to get all this info first then go through the code,

Ok, I will make simpler,
make a Form which will have the following fields,
EmailAddress
SubjectText
MessageText
Command button with name command1, in the properties of this button, look for On Click, click in that field, use the little arrow and choose [Event Procedure], then click on the little button with 3 dots, this will take you to VB editor,
Change the code to this

Private Sub Command1_Click()
    Set Out = CreateObject("Outlook.Application")                'Email the Items to Suppliers
    With Out.CreateItem(olMailItem)
           .Recipients.Add [EmailAddress]
'           .Recipients.Add(cc1).Type = [CCAddress] 'if you want to send CC
           .Subject =[ SubjectText]
           .Body = [MessageText]
'           .Attachments.Add "c:\RFQ-Letter.rtf"                  'if you want to send attachments
           .Send
    End With
End Sub

jaffer
0
 

Author Comment

by:omron60
ID: 10651565
jjafferr,

I have been able to make everything work except placing the report in the message body.  Within your code is .Body = [MessageText]  How do I get the report here.

rockiroads,

I don't know how to call the module.  I named the module EmailBody

Thank you both for your help.

omron60
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10651988
omron60
please go to this link ans see how it is explained, especially towards the last few posts

http://www.experts-exchange.com/Databases/MS_Access/Q_20275364.html?query=html+outlook&searchType=topic
0
 

Author Comment

by:omron60
ID: 10652909
jjafferr,

It appears that the explanation is in regards to placing a string of text in html format.  What I would like to do is insert a report into the message body.  Possible?  Will this technique work for that?

omron60
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10656369
to call, after clicking your button in your form

e.g. button was called cmdSend

the handler would be cmdSend_Click

simply call the module EmailBody with your parameters

private sub cmdSend_Click

    EmailBody( ... your list of parameters ...)

e.g.. if EmailBody is based on SendEmail then its

EmailBody "MyReport.rtf", "email@email.com", "Vacation approval request","Please review the attached vacaton request and approve it via the database"




like jjaffer said, where are you getting your info from,the form I take it - he has give the example of the form which looks like how you need to do it, you somehow need to initiate the creation of the report and emailing it.


0
 

Author Comment

by:omron60
ID: 10661611
I am confused.
Do either of you know where I can see an example?
Omron60
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10665218
Hello omron60

Take it easy man, your question is not as easy as you might think, especially when a full code is required, it needs time :o)

Here is a full explaination,

Private Sub Command1_Click()

'1+++++
'You have to output your Report and save it on a disk drive.
'You can do it either with RTF or HTML to maintain the Report Format.
'I used HTML
    DoCmd.OutputTo acOutputReport, "YourReportName", acFormatHTML, "c:\YourReportOutput.HTML", False

'2+++++
'Now we will open this file and read the contenet of it in a String Variable called FullText
Dim MyString
Open "c:\YourReportOutput.HTML" For Input As #1    ' Open file for input.
Do While Not EOF(1)    ' Loop until end of file.
    Line Input #1, MyString    ' Read each Line into MyString variable.
    FullText = FullText & MyString     'Put all the Lines read into one String, FullText
Loop
Close #1    ' Close file.

'3+++++
'In Outlook, go to Tools>Option>Mail Format>HTML (use this message format)
'Why HTML, because the base of it is Text, but the output is formated HTML
'We need to have a String to put in the .HTMLBody, not a file

Dim ol As New Outlook.Application
Dim olMail As Outlook.MailItem

    Set olMail = ol.CreateItem(olMailItem)

    With olMail
        .To = "who@where.com"
        '.CC = strCC
        '.BCC = "address2@aa.com"
        '.Attachments.Add "c:\somefile.txt"
        '.Attachments.Add "c:\somefile2.txt"
        .Subject = "Testing"
        .HTMLBody = FullText '"c:\CVPrint1.HTML"
        '.Body = "This is the body..."
        '.Display
        .Send
    End With

    Set olMail = Nothing
    Set ol = Nothing
End Sub

Are we forgiven now ;o)

jaffer
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10665727
jaffer is on the right track though Im a little confused why step 2 has been created

To expand on the OutputTo

DoCmd.OutputTo acOutputReport, "Name of Report in MDB", "OutputFormat", "Path/Name of report on hard drive", False

the outputformat can be one of

acFormatASP
acFormatDAP
acFormatHTML
acFormatIIS
acFormatRTF
acFormatSNP
acFormatTXT
acFormatXLS

apparently, if you leave this parameter blank, it will prompt you




when the report has been created, you simply use that created report as your attachment
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10667111
Hi rockiroads
you said "you simply use that created report as your attachment"
This is where I went wrong too,
omron60 wants the Report as the Body of the message, not as an attachment.

in step 3, my 3rd remark was 'We need to have a String to put in the .HTMLBody, not a file

thats why we need step 2, to convert the Report File to a String.
I didn't try RTF, but HTML Reports holds the Report Formats better than RTF Reports.

I never did anything like this before, so it was ineresting :o)

jaffer
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10667217
gotcha, will FullText as a string be able to cope with say a very large string?
I thought there might be a size limit or something
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10667287
Hmmmm
Good question, which requires a test,

I opened one of those html files I saved sometime ago (Not a Report HTML), did a Print Preview,
And there were 18 A4 pages as an output (Big enought !),

And the verdict was

A TOTAL SUCCESS

except for those images which didn't show up.

jaffer
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10667326
well, that has answered that then, good stuff

should it not be possible to include images?, maybe omron60 does not require that then
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10667357
Shhhhhhhhh
don't say it so loud, he might hear you ;o)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10668203
sorry guv, i'll try to remember to whisper next time
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 125 total points
ID: 10668324
I just did another test,
I printed a Report from Access with our company logo (Image) and output that to HTML, RTF and Excell.

Access did not send the image to either. very interesting,
However, if you have your images on your web site, you can always give the link in the Report, this way, the images will not be part of the body, but it will be linked, this is what spammers do.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10668547
I wouldnt expect it to go to RTF

maybe then its not supported? just text only, if thats the case, not much point having html output then (apart from formatting)

There are still people out there using plain text for reading their emails, and not html, so sometimes its not always worth it


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10685880
Ive just run a report with a logo and sent it to a file, no luck, looks like it Access does not support it
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10686412
Hi Omron60
I think rockiroads and myself had plenty of time to discuss a few matters here,
whats your outcome?

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10697707
Hello, anybody home !!!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10697721
back at home but about to have breakfast!
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10697733
Hi rockiroads
Seems you are located somewhere in Europ ?
isn't 10 am too late for breakfast (LOL)

I hope Omron60 says something too :o(

jaffer
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10697796
well anytime I have snacks like waffles and eggs, no matter what time of day or night, its like having breakfast, 'cos I stuff myself with crumpets, juice and tea
(vegetarian household so no bacon!)

maybe omron is still working on the sample code provided by us!
0
 

Author Comment

by:omron60
ID: 10698104
Sorry,

I haven't had time to give the suggestions a try.

Omron60
0
 

Author Comment

by:omron60
ID: 10776586
Sorry but I still have not gotten back to this project.
omron60
0
 

Author Comment

by:omron60
ID: 11059528
I apologize to jjaffer and rockiroads for not getting back to this project.  I appreciate their efforts but I have not had time to work on this.  When I do get back to this project it will take a good deal of time as it is a complicated project for me.
omron60
0
 
LVL 34

Expert Comment

by:flavo
ID: 11939752
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: jjafferr

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Flavo
EE Cleanup Volunteer
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

860 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