E mail reports in access

squidlings
squidlings used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
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

Author

Commented:
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
Top Expert 2006
Commented:
I just finished helping with another question like yours. here's the link to it

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_22607458.html

basically you can use this as the button code. It creates the string of users that you want to send it to by opening a query up and grabbing names out of it. Can you build a query that includes just the e-mail addresses? If you can, plug it's name into the code below where indicated, then change the name of the rs!sendto <--- where sendto is the name of the field that holds the e-mail addresses.

Function MakeAddressString() As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from yourquery;") '<---your query goes here
If rs.EOF Then MakeAddressString = ""
rs.MoveFirst
Do Until rs.EOF
MakeAddressString = rs!sendto & MakeAddressString & ";"
rs.MoveNext
Loop
If Right(MakeAddressString, 1) = ";" Then MakeAddressString = Left(MakeAddressString, Len(MakeAddressString) - 1)
rs.Close
Set rs = Nothing
DoCmd.SendObject , "YourReport", "WHAT FORMAT???", MakeAddressString, "", "", "", True, ""
End Function
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2006

Commented:
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

Author

Commented:
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
Top Expert 2006

Commented:
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

Author

Commented:
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 :)
Top Expert 2006

Commented:
take the quotes off of acformatxls

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

Author

Commented:
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 :)

Author

Commented:
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!!
Top Expert 2006

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial