Sending emails with report output in body of email

[Beverly Darvill]

I am very very new to VBA programming.

Is it possible for coding to be written so that when I run a report with all
open actions grouped on a person and then each person receives their own
actions in an email with the report in the body of the email and for it to
retain the formatting of the report.

Thanks

 

[Tom van Stiphout]

On Thu, 26 Mar 2009 04:31:02 -0700, Beverly Darvill

Especially when you are very new to it, you work with what you are
given: DoCmd.SendObject.

-Tom.
Microsoft Access MVP

 

[Beverly Darvill]

Well that told me a lot of information!

On Thu, 26 Mar 2009 04:31:02 -0700, Beverly Darvill

Especially when you are very new to it, you work with what you are
given: DoCmd.SendObject.

-Tom.
Microsoft Access MVP

 

[Dale Fye]

Bev,

The short answer is, for an experienced developer, yes, it is possible. For
a newbe, I would recommend that you consider using the simple method Tom
mentiond (Docmd.SendObject).

This method provides you with the ability to send the report as either a
snapshot, rtf, html formatted attachment to the email.

To do what you have mentioned (I have not previously done this, so this is
speculation) I think I would start out by saving the report in an html format
(use the OutputTo method). Then, I would open that document and copy the
contents of that document as a variable. Then, I would use that variable as
the message parameter for the email. You might be able to do this latter
portion using SendObject, but more than likely would need to use Office
Automation to open Outlook, create a mail object, ...

Hope you like this answer better than Tom's.

I'm sure you could find someone in one of the Access newsgroups that would
do this for you, for a fee, but you gotta ask yourself, "is it worth it".
Especially when you have the SendObject method that works perfectly well.

If you are really good with HTML, you could probably build the text of the
document dynamically, and not use a report at all.

 

[Beverly Darvill]

Dale

I am able to do what you suggested and send as an attachement with no
problem - very easy macro to write - however we have trouble with people here
not opening attachments and that is why we want to (for want of a better
word) paste the format of the report into the body of an email, and also for
it to seperate out the actions and only send the appropriate actions to the
appropriate people i.e. Joe Bloggs only sees his actions whil Fred Smith sees
his without us have to go through approximately 100 possible actionees
individually.

Thanks

 

[Dale Fye]

Ok, Bev. Here is what I would do.

1. Create a Query that includes the email address of the people that you
need to send this message to. My guess is that it would look something like
(you get the idea)

SELECT E.EmpID, E.EmpEmail, Count(A.ActionID)
FROM tblEmployees as E
INNER JOIN tblActions as A
ON E.EmpID = A.EmpID
WHERE A.ActionStatus = 'Pending'
Group by E.EmpID, E.EmpEmail

Now, in your code, open a recordset with this query as the recordsource.

Set rs = currentdb.openrecordset("qryEmpWPendingActions")

Create a loop, and loop through each of these employees. Then create
another recordset that gets the info from tblActions that you want in your
message and create the text message (or at least the bulk of it from that
recordset). Finally, use SendObject to send this one email with the
information that is specific to them. Untested sample follows.

Dim strSQL as string
Dim strMsgStart as string
Dim strMsgDetails as string
Dim rsDetails as DAO.Recordset
strMsgStart = "Whatever you want to say up front" & vbcrlf & vbcrlf
While not rs.eof

strSQL = "SELECT * FROM tblActions " _
& "WHERE [EmpID] = " & rs("EmpID") _
& " AND [ActionStatus] = 'Pending' " _
& " ORDER BY [ActionSuspense]"
set rsDetails = currentdb.OpenRecordset(strsql, , dbfailonerror)
strMsgDetails = ""
While not rsDetails.Eof
strMsgDetails = strMsgDetails & vbcrlf _
& rs("ActionDesc")
rsDetails.Movenext
Wend
rsDetails.close

docmd.SendObject acSendNoObject, , , rs("EmpEmail"), , , _
"Pending Actions", _
strMsgStart & strMsgDetails
rs.movenext
Wend

rs.close
set rs = Nothing

 

[ryguy7272]

Take a look at this:
http://accesstips.wordpress.com/200...fill-an-access-listbox-with-all-report-names/

Good Luck,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Ok, Bev. Here is what I would do.

1. Create a Query that includes the email address of the people that you
need to send this message to. My guess is that it would look something like
(you get the idea)

SELECT E.EmpID, E.EmpEmail, Count(A.ActionID)
FROM tblEmployees as E
INNER JOIN tblActions as A
ON E.EmpID = A.EmpID
WHERE A.ActionStatus = 'Pending'
Group by E.EmpID, E.EmpEmail

Now, in your code, open a recordset with this query as the recordsource.

Set rs = currentdb.openrecordset("qryEmpWPendingActions")

Create a loop, and loop through each of these employees. Then create
another recordset that gets the info from tblActions that you want in your
message and create the text message (or at least the bulk of it from that
recordset). Finally, use SendObject to send this one email with the
information that is specific to them. Untested sample follows.

Dim strSQL as string
Dim strMsgStart as string
Dim strMsgDetails as string
Dim rsDetails as DAO.Recordset
strMsgStart = "Whatever you want to say up front" & vbcrlf & vbcrlf
While not rs.eof

strSQL = "SELECT * FROM tblActions " _
& "WHERE [EmpID] = " & rs("EmpID") _
& " AND [ActionStatus] = 'Pending' " _
& " ORDER BY [ActionSuspense]"
set rsDetails = currentdb.OpenRecordset(strsql, , dbfailonerror)
strMsgDetails = ""
While not rsDetails.Eof
strMsgDetails = strMsgDetails & vbcrlf _
& rs("ActionDesc")
rsDetails.Movenext
Wend
rsDetails.close

docmd.SendObject acSendNoObject, , , rs("EmpEmail"), , , _
"Pending Actions", _
strMsgStart & strMsgDetails
rs.movenext
Wend

rs.close
set rs = Nothing

Dale

I am able to do what you suggested and send as an attachement with no
problem - very easy macro to write - however we have trouble with people here
not opening attachments and that is why we want to (for want of a better
word) paste the format of the report into the body of an email, and also for
it to seperate out the actions and only send the appropriate actions to the
appropriate people i.e. Joe Bloggs only sees his actions whil Fred Smith sees
his without us have to go through approximately 100 possible actionees
individually.

Thanks

 

[Tony Toews [MVP]]

Is it possible for coding to be written so that when I run a report with all
open actions grouped on a person and then each person receives their own
actions in an email with the report in the body of the email and for it to
retain the formatting of the report.

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
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/