Report to email body

[hortizjr59]

I obtained to code below from this forum made a few changes to it and it works great, does exactlyu what I want it to do; outputs the current record into the body of an email. Well it also prints the report each and every time. I have reviewed the code below and although I am only a novice at this I cannot locate where the code is telling the report to output to the printer. Any help will be appreciated.

Private Sub Command135_Click()
Dim strline, strHTML
Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem
Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

stDocName = "ReportEmailNoticeofAssigned"
stFilename = "C:\Documents and Settings\hortiz\My Documents\Hector\myreport.html"
strWhere = "[Improvement No] = " & Me.[Improvement No]

DoCmd.OpenReport stDocName, acNormal, , strWhere
DoCmd.OpenReport "ReportEmailNoticeofAssigned", acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, stDocName, acFormatHTML, stFilename
DoCmd.Close acReport, stDocName

Open "C:\Documents and Settings\hortiz\My Documents\Hector\myreport.html" For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
' If OL2002 set the BodyFormat
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.Display
End Sub


Thanks:

Stumped

 

[GinaWhipp]

acNormal prints the report. Actually, why do you need that line at all? You could just delete it...

DoCmd.OpenReport stDocName, acNormal, , strWhere
DoCmd.OpenReport "ReportEmailNoticeofAssigned", acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, stDocName, acFormatHTML, stFilename
DoCmd.Close acReport, stDocName

 

[hortizjr59]

Good Morning Gina

Thanks for your reply;

I am a novice at this at best and you are very correct; I deleted the line you mentioned and now all works just fine.

Have a wonderful day.

H.O.

 

[GinaWhipp]

Glad to help! You're welcome...

 

[hortizjr59]

Well since you are glas to help; here is a new and what I hope to be a final issue I have.

I am using the code below to open a report based on the report number clicked on. The report numbers are listed on a form named Main_Dash. I am trying to alter my code to close the Main_Dash form then open the report number clicked on. When I enter a command to close the form; the form closes but the record number clicked on does not open.
Private Sub Improvement_No_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to view
MsgBox "Select a record to view"
Else
strWhere = "[Improvement No] = " & Me.[Improvement No]
DoCmd.OpenForm " Form View", acNormal, , strWhere
End If
End Sub
Can anyone help with this?
H.O.

 

[GinaWhipp]

That is true because you are getting the Improvement_No from the form. How about just making the Visible = False. Then you can close it in the On_Unload event of the report and no one is the wiser...

Private Sub Improvement_No_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to view
MsgBox "Select a record to view"
Else
strWhere = "[Improvement No] = " & Me.[Improvement No]
Forms!Main_Dash.Visible = False 'Replaced your line with this one
End If
End Sub

 

[hortizjr59]

You are a genius; it worked but it didn't do what I wanted to happen. My Main Dash form has a log on it that list a records status; when the status changes to Verified it should be removed from the log. I tried a refresh on focus for the main dash form but that didn't work. So I figured if I close the main dash form, go to the record, update date it, then click the exit button which runs a macro to save the record, refresh my table and open the the main dash form I should see a change in my log. But the log only changes when I completely close the main dash form then reopen.
Since my main dash form runs off a query maybe I have to update the query??
Hmmmm
Thanks;
H.O.

 

[GinaWhipp]

Okay, let's see if I can explain this clearly...

When you open your form (and the RecordSource) you are creating a *snapshot* of the data at that very second. So, it makes perfect sense that you have to close to get the data refreshed. Mind you the same holds true for a report. The report will not change unless closed and reopened.

Now, if you are changing the date on the form and then opening the report you can put a DoCmd save record prior to the report opening which will commit that change to the table prior to opening the report and it should open with the new date. Howver, if you want to change the date while the report is open then nope, it's not going to change.

 

[hortizjr59]

Good Morning Gina

Thanks so much for your help. I understand exactly. My thoughts were that since the data for my form is coming from a query there must be a way for me to update my query before I re-open the main dash form. Then; I think(?) the records should do what I want them to do. I will work on this a little more. I am starting to get a little better at access (fun stuff).

One quick question; IS Accessc 2007 allowed to be used in a multi user
mode?

H.O.

 

[GinaWhipp]

H.O.

Will get to the other questions later (on a Client site) but as for Multi-User...

http://www.kallal.ca/Articles/split/index.htm
http://allenbrowne.com/ser-01.html

You may also want to take a look at MVP Tom Wickerath's article…
http://www.accessmvp.com:80/TWickerath/articles/multiuser.htm

To handle the update of multiple front ends see…
http://www.autofeupdater.com/

If each user needs Access you can install the Runtime…
Access 2007
http://www.microsoft.com/downloads/...d9-9dc6-4b38-9fa6-2c745a175aed&displaylang=en
Access 2010
http://www.microsoft.com/downloads/...cd-5250-4df6-bfd1-6ced700a6715&displaylang=en

 

[hortizjr59]

Hi Gina:

Thanks so much for your help. I resolved my issue. The code below now works, yea. Now when I update the record and I close it with the update my log on the main dash gets update as well.

Private Sub Improvement_No_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to view
MsgBox "Select a record to view"
Else
strWhere = "[Improvement No] = " & Me.[Improvement No]
DoCmd.OpenForm " Form View", acNormal, , strWhere
End If
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Form Main Dash"
DoCmd.SelectObject acForm, stDocName, False
DoCmd.RunCommand acCmdClose
End Sub

Hector O

 

[GinaWhipp]

Glad you got it working and thanks for posting your sooution! It is bound to help someone else...

 

[hortizjr59]

Thanks for all your help.

My next step is to add some security to the database. I saw something on your web-sitethat will help.

Have a fun weekend.

Hector

 

[GinaWhipp]

Enjoy your weekend and have fun surfing!