J
Jim Bunton
Is it possible to automate sending an email based on a report to a list of
emails addresses in a table column?
emails addresses in a table column?
G
Guest
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("(e-mail address removed);[email protected]", "Testing...", _
"This is a test of the emergency broadcast system.")
'
' Message with all .snp files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "Reports",,"C:\Temp", "*.snp")
'
' Message with all files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "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
__________________________________________
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("(e-mail address removed);[email protected]", "Testing...", _
"This is a test of the emergency broadcast system.")
'
' Message with all .snp files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "Reports",,"C:\Temp", "*.snp")
'
' Message with all files in the C:\Temp folder:
' ?SendMail("(e-mail address removed);[email protected]", "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
__________________________________________
G
Guest
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_...05&as_maxd=25&as_maxm=2&as_maxy=2007&safe=off
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
examples of using the SendObject method:
http://groups.google.com/groups?as_...05&as_maxd=25&as_maxm=2&as_maxy=2007&safe=off
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
J
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
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
G
Guest
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
__________________________________________
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
__________________________________________
J
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
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
G
Guest
Hi Jim,
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
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
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
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Jim Bunton said:
J
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
ossible 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
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
ossible cause of problems - I have been using Casper Xp by futuresystems 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 said:
J
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
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 said: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
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 said:
G
Guest
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.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
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.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Jim Bunton said: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 said: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
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