email form Access 2000

[Jim Bunton]

Is it possible to automate sending an email based on a report to a list of
emails addresses in a table column?

--
Jim Bunton

[Tom Wickerath]

Hi Jim,

I'm not sure what you mean by "sending an email based on a report" but,
yes, it is fairly easy to send a message to one or more receipients. The
standard method involves using SendObject. This allows you to send an object
in an Access application (or no object at all). An example would include
sending a report. I like to send reports that are saved in the Microsoft
Access Snapshot format. This requires that the receipient has the Snapshot
Viewer installed on their PC. SendObject works with Outlook and Outlook
Express.

If you are using Outlook, here is one method that allows you to send any
file attachment(s) (ie. not limited to objects in Access, like SendObject
is). However this code does not work with Outlook Express:

********Begin Code********************

Option Compare Database
Option Explicit
Public Function SendMail(strRecipients As String, strSubject As String, _
Optional strBody As String, Optional strFilePath As String, _
Optional strFileExtension As String) As String
On Error GoTo ProcError
' Written by Tom Wickerath, May 7, 2006.
' Inputs:
' strRecipients: Required. Semicolon delimited string of recipients.
' strSubject: Required. Message subject.
' strBody: Optional. Body of the message.
' strFilePath: Optional. Valid path containing files to attach.
' strFileExtension: Optional. Allows one to send a particular file type.
'
' Returns a string to indicate success or failure.
'
' Notes:
' 1.) An invalid path will result in a message with no attached files.
' 2.) This is late bound code. It does not require a reference to the
' "Microsoft Outlook X.X Object Library". However, there is also no
' Intellisence to help you with editing the VBA code.
'
' Usage examples from Immediate Window:
‘ ?SendMail("m...@1.net;y...@2.com", "Testing...", _
"This is a test of the emergency broadcast system.")
'
' Message with all .snp files in the C:\Temp folder:
' ?SendMail("m...@1.net;y...@2.com", "Reports",,"C:\Temp", "*.snp")
'
' Message with all files in the C:\Temp folder:
' ?SendMail("m...@1.net;y...@2.com", "Reports","My message","C:\Temp")
Dim myObject As Object
Dim myItem As Object
Dim strFullPath As String
Dim strFileName As String
Dim strAttachPath As Variant
Dim intAttachments As Integer
Set myObject = CreateObject("Outlook.Application")
Set myItem = myObject.CreateItem(0)
With myItem
.Subject = strSubject
.To = strRecipients
If Len(Trim(strBody)) > 0 Then
.Body = strBody
End If
If Len(Trim(strFileExtension)) = 0 Then
strFileExtension = "*.*"
End If
If Len(strFilePath) > 0 Then
strFullPath = strFilePath & "\" & strFileExtension
If Len(Trim(strFullPath)) > 0 Then 'An optional path was
included
strFileName = Dir(strFullPath)
Do Until strFileName = ""
intAttachments = intAttachments + 1
strAttachPath = (strFilePath & "\" & strFileName)
.Attachments.Add (strAttachPath)
' Debug.Print strAttachPath
strFileName = Dir()
Loop
End If
End If
.Send
SendMail = "Message placed in outbox with " _
& intAttachments & " file attachment(s)."
End With
ExitProc:
‘Cleanup
On Error Resume Next
Set myItem = Nothing
Set myObject = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in SendMail Function..."
SendMail = "A problem was encountered attempting to automate Outlook."
Resume ExitProc
Resume
End Function

*********End Code************************

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Jim Bunton" wrote:

> Is it possible to automate sending an email based on a report to a list of
> emails addresses in a table column?
>

> Jim Bunton

[Tom Wickerath]

PS. Here are some past threads that I have been involved in, which show
examples of using the SendObject method:

http://groups.google.com/groups?as_q=docmd.sendobject&num=50&scoring=r&as_epq=&as_oq=&as_eq=&as_ugroup=microsoft.public.access.*&as_usubject=&as_uauthors=Tom+Wickerath&lr=&as_drrb=q&as_qdr=&as_mind=1&as_minm=1&as_miny=2005&as_maxd=25&as_maxm=2&as_maxy=2007&safe=off

[Jim Bunton]

Hi Tim,
Thanks for replying to my posting. There is quite a bit to digest!

Thanks particularly for the code snippets - I did have an idea that Outlook
(v outlook express) offered more flexibility.

I shall read, digest and give the options a go.

Thanks again - good thorough response - much appreciated.

Jim

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:5F64F805-349A-434B...@microsoft.com...

[Tom Wickerath]

Hi Jim,

You're welcome. Here is a bit more for you to digest! The procedure shown
below can be used to create a concatenated list of e-mail addresses. You can
see how I am passing the result at the end to my SendMail function:

**********Begin Code*********************

Option Compare Database
Option Explicit

'This procedure is used to create a concatenated list of user e-mail
addresses.

Sub AllUsers()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strUsers As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT EmailAddress FROM tblPeople " _
& "WHERE EmailAddress Is Not Null AND
blnActivePerson<>0")

With rs
Do Until rs.EOF
strUsers = strUsers & rs!EmailAddress & ";"
rs.MoveNext
Loop
End With

' Debug.Print strUsers

SendMail strUsers, "This is a test message"

ExitProc:
'Cleanup
On Error Resume Next

rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _

vbCritical, "Error in AllUsers event procedure..."
Resume ExitProc
End Sub

**********End Code**********************

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Jim Bunton]

Tom -
Thanks for the additional info.
Ironically! I'm having an issue with the 'OutLook' approach

*** code ***

Dim myObject As Object
Dim myItem As Object
Dim strFullPath As String
Dim strFileName As String
Dim strAttachPath As Variant
Dim intAttachments As Integer

Set myObject = CreateObject("Outlook.Application")

*** End Code ***
gives error:-
429: ActiveX component can't create object
Posted here and in public.Outlook > no responses!

So
I decided that I would have a go with OutLookExpress & SendObject
[I'm happy to create the messageTxt string in code rather than my original
idea to use a report]
this worked fine but then I hit a snag! See posting "SendObject" at 15:56 in
this group

Jim

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message

news:733B3B7F-3BAF-4C25...@microsoft.com...

[Tom Wickerath]

Hi Jim,

> 429: ActiveX component can't create object

Do you have Outlook installed on the computer that you are trying to run
this code from? If you do, then I suspect that you have some other issue
going on, like perhaps Outlook is not properly registered. Do you have a
different PC available, which has Outlook installed, that you can test your
code on?

See if this information helps:

You receive run-time error 429 when you automate Office applications
http://support.microsoft.com/?id=828550

INFO: Troubleshooting Error 429 When Automating Office Applications
http://support.microsoft.com/?id=244264

[Jim Bunton]

Hi Tom,
Thanks again! - both outlook and word failed on createobject.
Word worked after Run . . . winword /RegServer
Run . . . Outlook /regserver failed
Solution - Repair Office 2000
Set MyWordObject = CreateObject("outlook.Application") NOW WORKS

[Note:possible cause of problems - I have been using Casper Xp by future
systems to substantially restructure my discs in order to partiton the 'as
delivered' system disc C:]

HOWEVER
this code below using sendobject still fails to run as anticipated
(correctly?!)

** code **
. . . . .
For nRecepiants = 1 To 3
'will assign new values to sendTo
DoCmd.SendObject objectType, objectName, outputformat, sendTo,
sendCc, sendBcc, sendSubject, sendMessage, editmessage, templateFileName
Next 'nRecepiants
MsgBox "Emails sent", vbOKOnly, "End"
** End Code **

First time through the loop an email is placed in Outlook's outbox
second and third time nothing happens!

Run the function again - nothing happens (no email generated no error
generated)

Close the Access application - same result (email generated on first pass
through the loop but subsequently bo emails generated)

Jim

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message

news:3E598EFF-3F1E-43B8...@microsoft.com...

[Jim Bunton]

The problem with SendObject:
Only sends once In Access 2000

Resolution:- Install Service Pack 3 for Office
Details at
http://support.microsoft.com/kb/260819/en-us

"Jim Bunton" <jBu...@BlueYonder.co.uk> wrote in message
news:hpdFh.701$1E3...@fe3.news.blueyonder.co.uk...

[Tom Wickerath]

Hi Jim,

I'm glad you found this SP-3 resolution. Actually, there is a *VERY* nasty
bug that could have bitten you in Access 2000 without having SP-3 installed.
If you are interested, check out this KB article:
http://support.microsoft.com/?id=304548

Now would be a good time to ensure that you have the latest service pack for
your operating system, and the JET database engine (SP-8). Use this KB
article as a guide:

How to keep a Jet 4.0 database in top working condition in Access 2000
http://support.microsoft.com/?id=300216

Follow the first two links under the "Best Practices" section.