Report as email message rather than attachment

[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

 

[Graham Mandeno]

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).

 

[James Frater]

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

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

 

[Graham Mandeno]

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

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

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

 

[Tony Toews [MVP]]

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.

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

 

[James Frater]

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

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

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


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

 

[Graham Mandeno]

Hi James

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

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

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

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