Solved

Send an Access Report via email in the message body

Posted on 2004-03-22
35
773 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Open excel file after being created by Access 3 22
Numbers are exporting as text 5 26
Splitting out Data 14 27
Error in SQL Query 36 34
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now