asked on

E mail reports in access

I am creating a form in MS access, I'd like to put a button on the form which will e-mail a report to the "relevant people."

I found a button in the tool box and if you click on reports there is an option for "Mail Report"

This will only attach the report to an e-mail, then you have to type in the "relevant people" on the e-mail then press send.

I'd like to and I presume it'll need some code (not my strong point) automate this more, i.e. (because I'm lazy)

1) Click the mail to button on the form.

2) Outlook (default e-mail) opens a fresh e-mail with the report attached, also I'd like the "relevant people" to be automatically written into the e-mail so all you have to do is press send on the e-mail.

Thanking you for your help in advance.

jefftwilley

You'll need a few things before you can be lazy

the relevent peoples should be in a table somewhere...and their e-mail addys

you'll need a way to select the group of "relevent" people

the rest is pretty easy. This is an e-mail model that uses outlook and can be automated like you want. The process goes...

you push the button
Code behind the button magically creates a string of e-mail addresses which you feed to this function
Code also outputs your report to a c:\temp directory that can also be fed to the function as the attachment.
You can fill in subject and body for the e-mail there as well.

Function sendmail()
Dim olkApp As Outlook.Application
Dim olkNameSpace As Outlook.NameSpace
Dim objMailItem As Outlook.MailItem
Set olkApp = New Outlook.Application
Set olkNameSpace = olkApp.GetNamespace("MAPI")
Set objMailItem = olkApp.CreateItem(olMailItem)
With objMailItem
.To = "testguy@test.com; sam@test.com"
'.CC = "Enter E-Mail Address Here"
.Subject = "Email Subject here"
.Body = "EMailBody text here"
.Attachments.Add "c:\yourfile.doc"
.Display 'change this to "send" if you don't want to preview the e-mail
End With
Set objMailItem = Nothing
Set olkNameSpace = Nothing
Set olkApp = Nothing
End Function

squidlings

ASKER

Hi Again...

I think I'm a little out of my depth on this.

Let me explain that I've never added any VB code to access before, I really want to learn, but in this instance I think I might fail until I understand a little more about VB.

I didn't really know where to put this code you supplied me with, so I created a command button > reports > Mail Report.

Then I right clicked the button, went into properties > Events > On Click, opened the code editor.

It already had some code in (created when I inserted the command button to mail the report) So basically I pasted your code underneath their code. It's have an effect, I was hoping it would insert "testguy@test.com" etc

I presume, I need to alter your code so it points to the correct places to pick up the report I'd like to send??

Please can add some comments to your code of what I need to alter to make it work or tell me the correct place to use this code, i.e. not in the OnClick event handler.

This is what VB coder looks like,

PS ( If your next posting doesn't help, I'll give you the points anyway :) )

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click

Dim stDocName As String

stDocName = "Cancelling or Redating Cheque request Report"
DoCmd.SendObject acReport, stDocName

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub
Function sendmail()
Dim olkApp As Outlook.Application
Dim olkNameSpace As Outlook.NameSpace
Dim objMailItem As Outlook.MailItem
Set olkApp = New Outlook.Application
Set olkNameSpace = olkApp.GetNamespace("MAPI")
Set objMailItem = olkApp.CreateItem(olMailItem)
With objMailItem
.To = "testguy@test.com; sam@test.com"
'.CC = "Enter E-Mail Address Here"
.Subject = "Email Subject here"
.Body = "EMailBody text here"
.Attachments.Add "c:\yourfile.doc"
.Display 'change this to "send" if you don't want to preview the e-mail
End With
Set objMailItem = Nothing
Set olkNameSpace = Nothing
Set olkApp = Nothing
End Function

ASKER CERTIFIED SOLUTION

jefftwilley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jefftwilley

actually I made a couple of changes for you..using your button's name.

Private Sub Command23_Click()
dim strTo as string
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from yourquery;") '<---your query goes here
If rs.EOF Then strTo = ""
rs.MoveFirst
Do Until rs.EOF
strTo = rs!sendto & strTo & ";"
rs.MoveNext
Loop
If Right(strTo, 1) = ";" Then strTo = Left(strTo, Len(strTo) - 1)
rs.Close
Set rs = Nothing
DoCmd.SendObject , "Cancelling or Redating Cheque request Report"
, "WHAT FORMAT???", strTo, "", "", "", True, ""
end sub

you should be able to paste that in over your existing button's code. But you still need the query and the right field name.
J

squidlings

ASKER

Hi, don't know if you'll pick this up, but I think I'm nearly there...

I'm getting a syntex....

Private Sub Command23_Click()
Dim strTo As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * Test E-Mail QRY;") '<---your query goes here
If rs.EOF Then strTo = ""
rs.MoveFirst
Do Until rs.EOF

strTo = rs!Email Address & strTo & ";" <<<<< SYNTEX - I've entered the name of the field the emails are stored? Error

rs.MoveNext
Loop
If Right(strTo, 1) = ";" Then strTo = Left(strTo, Len(strTo) - 1)
rs.Close
Set rs = Nothing
DoCmd.SendObject , "Cancelling or Redating Cheque request Report"
, "WHAT FORMAT???", strTo, "", "", "", True, ""
End Sub

jefftwilley

ok a couple of things. since your query name has spaces, you need to surround it in brackets. That's a standard Access thing. Good practice is that you use a standard naming convention like
qryTestEMail
Notice there's no spaces and no symbol type characters in the name...those Kill VBA. So

Set rs = CurrentDb.OpenRecordset("select * [Test E-Mail QRY];") '<---your query goes here

same for the field names. use a standard convention you have Email Address now. In order to help you remember what type of field it is...and to keep from having to worry about brackets every time

sEMailAddress s for string (can be t for text, etc...but you get the idea)

But this should get you there

strTo = rs![Email Address] & strTo & ";"

NOTE. You still don't have an output format in the SendObject command. If you want it to default, just leave the "" in place and remove the text in there.

J

squidlings

ASKER

So close, I can see it working, when I hover my mouse over strTo, it brings up all the e-mail mail address.

Still getting an error on the last line

Private Sub Command24_Click()
Dim strTo As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from [emailselectqry];")
If rs.EOF Then strTo = ""
rs.MoveFirst
Do Until rs.EOF
strTo = rs!EmailAddress & strTo & ";"
rs.MoveNext
Loop
If Right(strTo, 1) = ";" Then strTo = Left(strTo, Len(strTo) - 1)
rs.Close
Set rs = Nothing

It doesn't like the lines below...? (error Message : Run Time 2498 - An expression you entered is the wrong data type for one of the arguments.)
DoCmd.SendObject , "[Cancelling or Redating Cheque request Report]", "acformatxls", strTo, "", "", "", True, ""
End Sub

Any ideas, I'm so grateful, I didn't think I'd even get a little bit running, you fill me with hope :)

jefftwilley

take the quotes off of acformatxls

DoCmd.SendObject , "[Cancelling or Redating Cheque request Report]", acFormatXLS, strTo, "", "", "", True, ""

squidlings

ASKER

Hi,

Getting closer now, everything appears to be working appart from the ";" in between the e-mail addresses, so I have what looks like.

Tom@Jerry.comMicky@mouse.comdonald@duck.com

Where as it needs be...

Tom@Jerry.com; Micky@mouse.com; donald@duck.com

Oh yeah, I've had to change it around a tad because it wouldn't pick up the name of the report the way it was...

Private Sub Command24_Click()
Dim strTo As String
Dim rs As DAO.Recordset
Dim stDocName As String
Set rs = CurrentDb.OpenRecordset("select * from [emailselectqry];")
If rs.EOF Then strTo = ""
rs.MoveFirst
Do Until rs.EOF
strTo = rs!EmailAddress & strTo & ";"
rs.MoveNext
Loop
If Right(strTo, 1) = ";" Then strTo = Left(strTo, Len(strTo) - 1)
rs.Close
Set rs = Nothing

stDocName = "Cancelling or Redating Cheque request Report"
DoCmd.SendObject acReport, stDocName, "", strTo, "", "", "CRC"
End Sub

Thank you so much more this, I can't beleive you helped me nearly getting this done, my first bit of code :) I'm well made up, I know it's your bit of code, but I like to think it's my bit of code now :)

squidlings

ASKER

Oh yeah, I wasn't sure if you'd pick this up, so I've posted a new question, if you search for me again, there are another 500 points up for grabs!!

jefftwilley

rivate Sub Command24_Click()
Dim strTo As String
Dim rs As DAO.Recordset
Dim stDocName As String
Set rs = CurrentDb.OpenRecordset("select * from [emailselectqry];")
If rs.EOF Then strTo = ""
rs.MoveFirst
Do Until rs.EOF
if nz(rs!EmailAddress,"")="" then goto NextAddress
strTo = strTo & rs!EmailAddress & ";"
NextAddress:
rs.MoveNext
Loop
If Right(strTo, 1) = ";" Then strTo = Left(strTo, Len(strTo) - 1)
rs.Close
Set rs = Nothing

stDocName = "Cancelling or Redating Cheque request Report"
DoCmd.SendObject acReport, stDocName, "", strTo, "", "", "CRC"
End Sub