Sending a report from access to the body of an e-mail

[tebule]

I have searched everywhere on the net for the answer to my question and I found a post on this forum: Sending a report by mail. But even in the post there was no solution.

I would like to send a report that I have created in access 2003 in an e-mail not as an attachment, but in the body. I can't find the code to publish my report to word from Access. Here is what I have. Any help is greatly appriciated as I fear I am in over my knowledge level. The code below is what I have found from different examples.

Sub MakeEmail()
Dim objOutlook As Outlook.Application
'Dim objOutlookExp As Object
Dim objDrafts As Object
Dim objEmail As Object
Dim strBody, strTitle, strTo As String
Dim Doc As Word.Document
Dim objAccess As Access.Application
Dim Rpt As Access.Report

Set objOutlook = New Outlook.Application
Set Doc = objOutlook.ActiveInspector.wordeditor
' Open the outlook drafts folder
Set objDrafts = objOutlook.Session.GetDefaultFolder(olFolderDrafts)
If objDrafts = "Drafts" Then
' Create new email in Drafts folder
Set objEmail = objDrafts.Items.Add
Set Doc = objEmail.getinspector.wordeditor
Set wdParg = Doc.Paragraphs
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Need to know how to Publish from Access to word in VBA Code
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Copy the published Report
Doc.Selection.WholeStory
Doc.Selection.Copy

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
strBody = "This is where I need to paste Wholestory, maybe using a"
DataObject.GetFromClipboard ""
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
strTitle = "Access to Outlook Test"
objEmail.To = strTo '-an e-mail address
objEmail.Body.printtext Text:="Report for today:"
objEmail.Body.printparagraph
wdParg.Paste 'to paste my report
objEmail.Body.printparagraph
objEmail.Body.printtext Text:="If any problems call"
objEmail.Body.printparagraph
objEmail.Body = strBody
objEmail.Subject = strTitle
Set objDoc = objEmail.Attachments
objDoc.Add strAttach
' Save email in drafts folder
objEmail.Close olSave
Else
MsgBox "No Drafts Folder"
End If
End Sub

 

[darbid]

I assume you have seen this recent question here http://www.access-programmers.co.uk/forums/showthread.php?t=175323&highlight=outlook

I have seen the question asked in an Outlook forum and it was not really answered. I am not 100% sure but I think you cannot just add a MSAccess report to the body part of a MailItem.

I think you need to get your report into another form first and then add that newer form to your MailItem. I have Zero experience with reports (other than I know they do not open in Word very well) but maybe try to get your report into Excel.

Further and I am just thinking out load at the moment and typing - someone will pick me up if I am wrong - You want to add a report to the body of an email. How could you manually do this? - for example if I want to add Excel tables to an Email then I cut and copy the table - but I assume what Outlook does in the background is changes the Format of the email to Word as the email editor. Thus allowing the table to be seen. So if Access reports do not translate well to Word documents how can we expect Reports to suddenly be great emails when Outlook uses Word for its body?

 

[tebule]

darbid: Thanks so much for the response. I was wondering how to publish a report to word via vba code. It translates well when I do it manually. This report would not look good via excel. how do you add a report to the body of an e-mail in code?

I was hoping this code would do that:
'Copy the published Report
Doc.Selection.WholeStory
Doc.Selection.Copy

'+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
strBody = "This is where I need to paste Wholestory, maybe using a"
DataObject.GetFromClipboard ""
'+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
strTitle = "Access to Outlook Test"
objEmail.To = strTo '-an e-mail address
objEmail.Body.printtext Text:="Report for today:"
objEmail.Body.printparagraph
wdParg.Paste 'to paste my report
objEmail.Body.printparagraph
objEmail.Body.printtext Text:="If any problems call"
objEmail.Body.printparagraph
objEmail.Body = strBody


How do I get the word editor to come up? I found some code:
set doc = objOutlook.activeinspector.wordeditor But when I tried to enter this line, activeinspector was not recognized by the auto complete and I don't know if there is another reference besides the ms word 11 reference that needed to be added.

I think the report would look great if I could just get it to past as it looks fine when I do it manually. But I don't know how the code will change the formatting.

 

[darbid]

I have almost no experience with reports but have a look at "DoCmd.OutputTo............" I think that you can publish it to an RTF.

If you get your report into word then you do not have to use copy/paste. Have a look at the other post I made and you will see that you can use the "content" of the word document.

I am not sure you need to call the editor if you simple add the "contents" of the word document to the MailItem.body of the email.

 

[tebule]

I also I found an example saving the report in rich text format and then reopening it, but I don't want to save it. I just want to copy it straight to the e-mail.

 

[tebule]

I am sorry I am confussed. How do I go to outlook from word using the content of the word document. What does the code look like. Is my code incorrect? Thank you for your time.

 

[tebule]

I am looking at your other post again and I see the .Body = Active Document.Content. If I have two word documents will it know that the one I created is the active document? Do I have to set the focus on it?

 

[darbid]

Looking at the other thread would help you, but

here you go - you will need to get word as an object just like you have outlook and then get the word document as an object.

objEmail.Body = objWordDoc.Content

 

[darbid]

If I have two word documents will it know that the one I created is the active document? Do I have to set the focus on it?

If immediately after your generate your word document you do this it should still be the active document.

 

[tebule]

Here is an example of code that shows what I was trying to do:

Got it from web page: http://www.databasedev.co.uk/send_email.html

'----------------------------CODE START----------------------------
Public Sub SendMail()
'Provides the Send Mail automation
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer

strSubject = "Latest Job Outcomes"
strEmailAddress = "[Mail Addresses Go Here]"
'strEmailAddress,replace [Mail Addresses Go Here] above with valid
'e-mail addresses
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")


'Count of unsent e-mails
intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _
, "[ysnSentByMailToStaff]=0")
'If count of unsent e-mails is zero then the procedure will not run
'If count of unsent e-mails is greater than zero, msgbox will prompt
'to send mail.

If intCount = 0 Then
MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
, vbInformation, "System Information"
Exit Sub
Else

rst.MoveFirst
Do Until rst.EOF

strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _
& " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _
& " course" & " has informed us of a new job." & Chr(10) & Chr(10) _
& "Below are the details that have been submitted by the student:" _
& Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _
& "Graham"

'EMAIL USER DETAILS & ATT REPORT
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False


rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

'Run update to update the sent mail check box
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblJobOutcomes " & _
"SET tblJobOutcomes.ysnSentByMailToStaff = -1 " & _
"WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))"
DoCmd.SetWarnings True
MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You"
End If
End Sub
---------------------------------------------------------------------------------

Don't know if I can get it to send a report in the body like that, but it is a starting point.

 

[darbid]

try it out, but you might find that it send it as an attachment.