Report as email message rather than attachment

  • Thread starter Thread starter James Frater
  • Start date Start date
J

James Frater

Hello Everyone,

I'm currently using the DoCmd.SendOject acReport command to send a report as
an attachment by email, which is working beautifully.

However one of the users of our DB would like to be able to send the report
as the message body of the email. Firstly is that possible, and if it is
could someone point me in the direction, as I have no idea on this one.

Thank you in advance

Cheers

JAMES
 
Hi James

The body of an email can be text or (for most email clients) HTML.
SendObject allows only text, but the text must be in the form of a string,
not the output from a report, so there is no way to do this directly with
SendObject.

It should be possible to save a copy of the report to a temporary file as
text or as HTML using DoCmd.OutputTo. Then open the resulting file and read
the contents. If you are using text then the contents can be passed
directly to SendObject as the body of the message.

For HTML, you will need to automate your email client and create an HTML
formatted message (for Outlook, use the HTMLBody property of the MailItem
object).
 
Hi Graham,

Thanks for that, massively useful.

To keep things simple I'm going to stick with the text option.

I've had a quick go, but for what ever reason I can only get the file path
or either the name of the file into the body of the message rather than the
contents of the file.

I've either missed out a step, or am missing something completly.

My code so far is:

strWhere = "EV_ID=" & Ev_ID
strArgs = "Bookings Report for " & Me.EName
strFileName = Me.ST_ID.Column(1) & " " & Me.EName & " - Bookings Report"
strFileLocation = "G:\Sport Bookings\Reports\" & strFileName & ".rtf"

DoCmd.OpenReport "rptbookings", acViewPreview, , strWhere, , OpenArgs:=strArgs
Reports("rptbookings").Caption = strFileName

DoCmd.OutputTo acOutputReport, "rptbookings", acFormatRTF, strFileLocation
DoCmd.Close acReport, "rptbookings"

DoCmd.SendObject acSendNoObject, , , , , , , strFileName, , strFileLocation

Any wise words, would be very welcome.

Many thanks

JAMES

Graham Mandeno said:
Hi James

The body of an email can be text or (for most email clients) HTML.
SendObject allows only text, but the text must be in the form of a string,
not the output from a report, so there is no way to do this directly with
SendObject.

It should be possible to save a copy of the report to a temporary file as
text or as HTML using DoCmd.OutputTo. Then open the resulting file and read
the contents. If you are using text then the contents can be passed
directly to SendObject as the body of the message.

For HTML, you will need to automate your email client and create an HTML
formatted message (for Outlook, use the HTMLBody property of the MailItem
object).

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


James Frater said:
Hello Everyone,

I'm currently using the DoCmd.SendOject acReport command to send a report
as
an attachment by email, which is working beautifully.

However one of the users of our DB would like to be able to send the
report
as the message body of the email. Firstly is that possible, and if it is
could someone point me in the direction, as I have no idea on this one.

Thank you in advance

Cheers

JAMES
Click to expand...
Click to expand...
 
Hi James

First, to do it this way I think you will have to save the report as plain
text, not RTF. I don't think that SendObject can do rich text or HTML
message bodies.

Secondly, after saving the file, you must open it and read the contents,
line by line, appending each line to a string variable that then becomes the
message body.

Add the following declarations:

Dim strLine as String
Dim strMessage as String
Dim hFile as Long

Then after DoCmd.Close:

hFile = FreeFile
Open strFileLocation for input as #hFile
Do Until EOF(hFile)
Line Input #hFile, strLine
strMessage = strMessage & strLine & vbCrLf
Loop
Close #hFile

DoCmd.SendObject acSendNoObject, _
Subject:=strFileName, _
MessageText:=strMessage


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

James Frater said:
Hi Graham,

Thanks for that, massively useful.

To keep things simple I'm going to stick with the text option.

I've had a quick go, but for what ever reason I can only get the file path
or either the name of the file into the body of the message rather than
the
contents of the file.

I've either missed out a step, or am missing something completly.

My code so far is:

strWhere = "EV_ID=" & Ev_ID
strArgs = "Bookings Report for " & Me.EName
strFileName = Me.ST_ID.Column(1) & " " & Me.EName & " - Bookings Report"
strFileLocation = "G:\Sport Bookings\Reports\" & strFileName & ".rtf"

DoCmd.OpenReport "rptbookings", acViewPreview, , strWhere, ,
OpenArgs:=strArgs
Reports("rptbookings").Caption = strFileName

DoCmd.OutputTo acOutputReport, "rptbookings", acFormatRTF, strFileLocation
DoCmd.Close acReport, "rptbookings"

DoCmd.SendObject acSendNoObject, , , , , , , strFileName, ,
strFileLocation

Any wise words, would be very welcome.

Many thanks

JAMES

Graham Mandeno said:
Hi James

The body of an email can be text or (for most email clients) HTML.
SendObject allows only text, but the text must be in the form of a
string,
not the output from a report, so there is no way to do this directly with
SendObject.

It should be possible to save a copy of the report to a temporary file as
text or as HTML using DoCmd.OutputTo. Then open the resulting file and
read
the contents. If you are using text then the contents can be passed
directly to SendObject as the body of the message.

For HTML, you will need to automate your email client and create an HTML
formatted message (for Outlook, use the HTMLBody property of the MailItem
object).

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


James Frater said:
Hello Everyone,

I'm currently using the DoCmd.SendOject acReport command to send a
report
as
an attachment by email, which is working beautifully.

However one of the users of our DB would like to be able to send the
report
as the message body of the email. Firstly is that possible, and if it
is
could someone point me in the direction, as I have no idea on this one.

Thank you in advance

Cheers

JAMES
Click to expand...
Click to expand...
Click to expand...
 
James Frater said:
I'm currently using the DoCmd.SendOject acReport command to send a report as
an attachment by email, which is working beautifully.

However one of the users of our DB would like to be able to send the report
as the message body of the email. Firstly is that possible, and if it is
could someone point me in the direction, as I have no idea on this one.
Click to expand...

To create a formatted document to send as an email you will need to
use VBA code to create a largish string. This string will then be
passed to the SendObject command or other method as the body of the
email. For more details including sample air code see my Tips page
on this topic at http://www.granite.ab.ca/access/email/formatted.htm.

Tony
 
Graham,

Combined with the Tony Towes advice who also replied to this thread, this
works brilliantly.

Thank you so much for you patience and expertise on this.

JAMES

Graham Mandeno said:
Hi James

First, to do it this way I think you will have to save the report as plain
text, not RTF. I don't think that SendObject can do rich text or HTML
message bodies.

Secondly, after saving the file, you must open it and read the contents,
line by line, appending each line to a string variable that then becomes the
message body.

Add the following declarations:

Dim strLine as String
Dim strMessage as String
Dim hFile as Long

Then after DoCmd.Close:

hFile = FreeFile
Open strFileLocation for input as #hFile
Do Until EOF(hFile)
Line Input #hFile, strLine
strMessage = strMessage & strLine & vbCrLf
Loop
Close #hFile

DoCmd.SendObject acSendNoObject, _
Subject:=strFileName, _
MessageText:=strMessage


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

James Frater said:
Hi Graham,

Thanks for that, massively useful.

To keep things simple I'm going to stick with the text option.

I've had a quick go, but for what ever reason I can only get the file path
or either the name of the file into the body of the message rather than
the
contents of the file.

I've either missed out a step, or am missing something completly.

My code so far is:

strWhere = "EV_ID=" & Ev_ID
strArgs = "Bookings Report for " & Me.EName
strFileName = Me.ST_ID.Column(1) & " " & Me.EName & " - Bookings Report"
strFileLocation = "G:\Sport Bookings\Reports\" & strFileName & ".rtf"

DoCmd.OpenReport "rptbookings", acViewPreview, , strWhere, ,
OpenArgs:=strArgs
Reports("rptbookings").Caption = strFileName

DoCmd.OutputTo acOutputReport, "rptbookings", acFormatRTF, strFileLocation
DoCmd.Close acReport, "rptbookings"

DoCmd.SendObject acSendNoObject, , , , , , , strFileName, ,
strFileLocation

Any wise words, would be very welcome.

Many thanks

JAMES

Graham Mandeno said:
Hi James

The body of an email can be text or (for most email clients) HTML.
SendObject allows only text, but the text must be in the form of a
string,
not the output from a report, so there is no way to do this directly with
SendObject.

It should be possible to save a copy of the report to a temporary file as
text or as HTML using DoCmd.OutputTo. Then open the resulting file and
read
the contents. If you are using text then the contents can be passed
directly to SendObject as the body of the message.

For HTML, you will need to automate your email client and create an HTML
formatted message (for Outlook, use the HTMLBody property of the MailItem
object).

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hello Everyone,

I'm currently using the DoCmd.SendOject acReport command to send a
report
as
an attachment by email, which is working beautifully.

However one of the users of our DB would like to be able to send the
report
as the message body of the email. Firstly is that possible, and if it
is
could someone point me in the direction, as I have no idea on this one.

Thank you in advance

Cheers

JAMES
Click to expand...
Click to expand...
Click to expand...
 
Hi James

I'm glad you got it working. Thanks for the feedback!

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

James Frater said:
Graham,

Combined with the Tony Towes advice who also replied to this thread, this
works brilliantly.

Thank you so much for you patience and expertise on this.

JAMES

Graham Mandeno said:
Hi James

First, to do it this way I think you will have to save the report as
plain
text, not RTF. I don't think that SendObject can do rich text or HTML
message bodies.

Secondly, after saving the file, you must open it and read the contents,
line by line, appending each line to a string variable that then becomes
the
message body.

Add the following declarations:

Dim strLine as String
Dim strMessage as String
Dim hFile as Long

Then after DoCmd.Close:

hFile = FreeFile
Open strFileLocation for input as #hFile
Do Until EOF(hFile)
Line Input #hFile, strLine
strMessage = strMessage & strLine & vbCrLf
Loop
Close #hFile

DoCmd.SendObject acSendNoObject, _
Subject:=strFileName, _
MessageText:=strMessage


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

James Frater said:
Hi Graham,

Thanks for that, massively useful.

To keep things simple I'm going to stick with the text option.

I've had a quick go, but for what ever reason I can only get the file
path
or either the name of the file into the body of the message rather than
the
contents of the file.

I've either missed out a step, or am missing something completly.

My code so far is:

strWhere = "EV_ID=" & Ev_ID
strArgs = "Bookings Report for " & Me.EName
strFileName = Me.ST_ID.Column(1) & " " & Me.EName & " - Bookings
Report"
strFileLocation = "G:\Sport Bookings\Reports\" & strFileName & ".rtf"

DoCmd.OpenReport "rptbookings", acViewPreview, , strWhere, ,
OpenArgs:=strArgs
Reports("rptbookings").Caption = strFileName

DoCmd.OutputTo acOutputReport, "rptbookings", acFormatRTF,
strFileLocation
DoCmd.Close acReport, "rptbookings"

DoCmd.SendObject acSendNoObject, , , , , , , strFileName, ,
strFileLocation

Any wise words, would be very welcome.

Many thanks

JAMES

:

Hi James

The body of an email can be text or (for most email clients) HTML.
SendObject allows only text, but the text must be in the form of a
string,
not the output from a report, so there is no way to do this directly
with
SendObject.

It should be possible to save a copy of the report to a temporary file
as
text or as HTML using DoCmd.OutputTo. Then open the resulting file
and
read
the contents. If you are using text then the contents can be passed
directly to SendObject as the body of the message.

For HTML, you will need to automate your email client and create an
HTML
formatted message (for Outlook, use the HTMLBody property of the
MailItem
object).

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


message
Hello Everyone,

I'm currently using the DoCmd.SendOject acReport command to send a
report
as
an attachment by email, which is working beautifully.

However one of the users of our DB would like to be able to send the
report
as the message body of the email. Firstly is that possible, and if
it
is
could someone point me in the direction, as I have no idea on this
one.

Thank you in advance

Cheers

JAMES
Click to expand...
Click to expand...
Click to expand...
 
Back
Top