Send Mail From Access

P

PaleRider

Hi,

Is there a way to send the results of a report to multiple email recipients
from Access 2003. I already have a table with email addresses and I have
looked at some examples in this forum, but I don't know how to use the code
examples and make it work for my situation. Can someone help me with how to
code things to send email to Outlook.

I have a report "Personnel" that gets data from a query "qryPersonnel". I
then have a email table with the following fields:

(tblMyMail)
Email_ID = PK
Email_Address = Text
Email_Subject = Text
Date_Sent = DATE/TIME

I have the following Report with these fields:

(rptPersonnel)
Full_Name
Company_Name
Contact_Name
Void_Date
DaysLeft

If you require more information let me know. Thank you in advance.

PR
 
B

babyatx13 via AccessMonster.com

I don't know if this will help, but if you are emailing from outlook you will
need this code so your Access VBA Code will work.
Put this code in Outlook VB Editor you will find it under Tools then Macro

K Board


Option Explicit

' Code: Send E-mail without Security Warnings
' OUTLOOK 2003 VBA CODE FOR 'ThisOutlookSession' MODULE
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 07/05/2005
' Updated v1.3 - 11/11/2005
'
' Please read the full tutorial here:
' http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning

'
' Please leave the copyright notices in place - Thank you.

Private Sub Application_Startup()

'IGNORE - This forces the VBA project to open and be accessible using
automation
' at any point after startup

End Sub

' FnSendMailSafe
' --------------
' Simply sends an e-mail using Outlook/Simple MAPI.
' Calling this function by Automation will prevent the warnings
' 'A program is trying to send a mesage on your behalf...'
' Also features optional HTML message body and attachments by file path.
'
' The To/CC/BCC/Attachments function parameters can contain multiple items by
seperating
' them by a semicolon. (e.g. for the strTo parameter, '(e-mail address removed);
(e-mail address removed)' is
' acceptable for sending to multiple recipients.
'
' Read more here:
' http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning

'
Public Function FnSendMailSafe(strTo As String, _
strCC As String, _
strBCC As String, _
strSubject As String, _
strMessageBody As String, _
Optional strAttachments As String) As Boolean

' (c) 2005 Wayne Phillips - Written 07/05/2005
' http://www.everythingaccess.com
'
' You are free to use this code within your application(s)
' as long as the copyright notice and this message remains intact.

On Error GoTo ErrorHandler:

Dim MAPISession As Outlook.NameSpace
Dim MAPIFolder As Outlook.MAPIFolder
Dim MAPIMailItem As Outlook.MailItem
Dim oRecipient As Outlook.Recipient
Dim TempArray() As String
Dim varArrayItem As Variant
Dim blnSuccessful As Boolean

'Get the MAPI NameSpace object
Set MAPISession = Application.Session

If Not MAPISession Is Nothing Then
'Logon to the MAPI session
MAPISession.Logon , , True, False

'Create a pointer to the Outbox folder
Set MAPIFolder = MAPISession.GetDefaultFolder(olFolderOutbox)
If Not MAPIFolder Is Nothing Then

'Create a new mail item in the "Outbox" folder
Set MAPIMailItem = MAPIFolder.Items.Add(olMailItem)
If Not MAPIMailItem Is Nothing Then

With MAPIMailItem

'Create the recipients TO
TempArray = Split(strTo, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olTo
Set oRecipient = Nothing

Next varArrayItem

'Create the recipients CC
TempArray = Split(strCC, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olCC
Set oRecipient = Nothing

Next varArrayItem

'Create the recipients BCC
TempArray = Split(strBCC, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olBCC
Set oRecipient = Nothing

Next varArrayItem

'Set the message SUBJECT
.Subject = strSubject

'Set the message BODY (HTML or plain text)
If StrComp(Left(strMessageBody, 6), "<HTML>",
vbTextCompare) = 0 Then
.HTMLBody = strMessageBody
Else
.Body = strMessageBody
End If

'Add any specified attachments
TempArray = Split(strAttachments, ";")
For Each varArrayItem In TempArray

.Attachments.Add CStr(Trim(varArrayItem))

Next varArrayItem
.Send 'No return value since the message will remain in
the outbox if it fails to send
Set MAPIMailItem = Nothing
End With
End If
Set MAPIFolder = Nothing
End If
MAPISession.Logoff
End If
'If we got to here, then we shall assume everything went ok.
blnSuccessful = True

ExitRoutine:
Set MAPISession = Nothing
FnSendMailSafe = blnSuccessful

Exit Function

ErrorHandler:
MsgBox "An error has occured in the user defined Outlook VBA function
FnSendMailSafe()" & vbCrLf & vbCrLf & _
"Error Number: " & CStr(Err.Number) & vbCrLf & _
"Error Description: " & Err.Description, vbApplicationModal +
vbCritical
Resume ExitRoutine

End Function
 
B

babyatx13 via AccessMonster.com

Then Put this code in access.

K Board


'This is the procedure that calls the exposed Outlook VBA function...
Public Function FnSafeSendEmail(strTo As String, _
strSubject As String, _
strMessageBody As String, _
Optional strAttachmentPaths As String, _
Optional strCC As String, _
Optional strBCC As String) As Boolean

Dim objOutlook As Object ' Note: Must be late-binding.
Dim objNameSpace As Object
Dim objExplorer As Object
Dim blnSuccessful As Boolean
Dim blnNewInstance As Boolean

'Is an instance of Outlook already open that we can bind to?
On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
On Error GoTo 0

If objOutlook Is Nothing Then

'Outlook isn't already running - create a new instance...
Set objOutlook = CreateObject("Outlook.Application")
blnNewInstance = True

'We need to instantiate the Visual Basic environment... (messy)
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)

objExplorer.CommandBars.FindControl(, 1695).Execute

objExplorer.Close

Set objNameSpace = Nothing
Set objExplorer = Nothing

End If

blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
strSubject, strMessageBody, _
strAttachmentPaths)

If blnNewInstance = True Then objOutlook.Quit
Set objOutlook = Nothing

FnSafeSendEmail = blnSuccessful

End Function


I don't know if this will help, but if you are emailing from outlook you will
need this code so your Access VBA Code will work.
Put this code in Outlook VB Editor you will find it under Tools then Macro

K Board

Option Explicit

' Code: Send E-mail without Security Warnings
' OUTLOOK 2003 VBA CODE FOR 'ThisOutlookSession' MODULE
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 07/05/2005
' Updated v1.3 - 11/11/2005
'
' Please read the full tutorial here:
' http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning

'
' Please leave the copyright notices in place - Thank you.

Private Sub Application_Startup()

'IGNORE - This forces the VBA project to open and be accessible using
automation
' at any point after startup

End Sub

' FnSendMailSafe
' --------------
' Simply sends an e-mail using Outlook/Simple MAPI.
' Calling this function by Automation will prevent the warnings
' 'A program is trying to send a mesage on your behalf...'
' Also features optional HTML message body and attachments by file path.
'
' The To/CC/BCC/Attachments function parameters can contain multiple items by
seperating
' them by a semicolon. (e.g. for the strTo parameter, '(e-mail address removed);
(e-mail address removed)' is
' acceptable for sending to multiple recipients.
'
' Read more here:
' http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning

'
Public Function FnSendMailSafe(strTo As String, _
strCC As String, _
strBCC As String, _
strSubject As String, _
strMessageBody As String, _
Optional strAttachments As String) As Boolean

' (c) 2005 Wayne Phillips - Written 07/05/2005
' http://www.everythingaccess.com
'
' You are free to use this code within your application(s)
' as long as the copyright notice and this message remains intact.

On Error GoTo ErrorHandler:

Dim MAPISession As Outlook.NameSpace
Dim MAPIFolder As Outlook.MAPIFolder
Dim MAPIMailItem As Outlook.MailItem
Dim oRecipient As Outlook.Recipient
Dim TempArray() As String
Dim varArrayItem As Variant
Dim blnSuccessful As Boolean

'Get the MAPI NameSpace object
Set MAPISession = Application.Session

If Not MAPISession Is Nothing Then
'Logon to the MAPI session
MAPISession.Logon , , True, False

'Create a pointer to the Outbox folder
Set MAPIFolder = MAPISession.GetDefaultFolder(olFolderOutbox)
If Not MAPIFolder Is Nothing Then

'Create a new mail item in the "Outbox" folder
Set MAPIMailItem = MAPIFolder.Items.Add(olMailItem)
If Not MAPIMailItem Is Nothing Then

With MAPIMailItem

'Create the recipients TO
TempArray = Split(strTo, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olTo
Set oRecipient = Nothing

Next varArrayItem

'Create the recipients CC
TempArray = Split(strCC, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olCC
Set oRecipient = Nothing

Next varArrayItem

'Create the recipients BCC
TempArray = Split(strBCC, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olBCC
Set oRecipient = Nothing

Next varArrayItem

'Set the message SUBJECT
.Subject = strSubject

'Set the message BODY (HTML or plain text)
If StrComp(Left(strMessageBody, 6), "<HTML>",
vbTextCompare) = 0 Then
.HTMLBody = strMessageBody
Else
.Body = strMessageBody
End If

'Add any specified attachments
TempArray = Split(strAttachments, ";")
For Each varArrayItem In TempArray

.Attachments.Add CStr(Trim(varArrayItem))

Next varArrayItem
.Send 'No return value since the message will remain in
the outbox if it fails to send
Set MAPIMailItem = Nothing
End With
End If
Set MAPIFolder = Nothing
End If
MAPISession.Logoff
End If
'If we got to here, then we shall assume everything went ok.
blnSuccessful = True

ExitRoutine:
Set MAPISession = Nothing
FnSendMailSafe = blnSuccessful

Exit Function

ErrorHandler:
MsgBox "An error has occured in the user defined Outlook VBA function
FnSendMailSafe()" & vbCrLf & vbCrLf & _
"Error Number: " & CStr(Err.Number) & vbCrLf & _
"Error Description: " & Err.Description, vbApplicationModal +
vbCritical
Resume ExitRoutine

End Function
[quoted text clipped - 25 lines]
 
B

babyatx13 via AccessMonster.com

Then Put this code in Access.

K Board


'This is the procedure that calls the exposed Outlook VBA function...
Public Function FnSafeSendEmail(strTo As String, _
strSubject As String, _
strMessageBody As String, _
Optional strAttachmentPaths As String, _
Optional strCC As String, _
Optional strBCC As String) As Boolean

Dim objOutlook As Object ' Note: Must be late-binding.
Dim objNameSpace As Object
Dim objExplorer As Object
Dim blnSuccessful As Boolean
Dim blnNewInstance As Boolean

'Is an instance of Outlook already open that we can bind to?
On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
On Error GoTo 0

If objOutlook Is Nothing Then

'Outlook isn't already running - create a new instance...
Set objOutlook = CreateObject("Outlook.Application")
blnNewInstance = True

'We need to instantiate the Visual Basic environment... (messy)
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)

objExplorer.CommandBars.FindControl(, 1695).Execute

objExplorer.Close

Set objNameSpace = Nothing
Set objExplorer = Nothing

End If

blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
strSubject, strMessageBody, _
strAttachmentPaths)

If blnNewInstance = True Then objOutlook.Quit
Set objOutlook = Nothing

FnSafeSendEmail = blnSuccessful

End Function


I don't know if this will help, but if you are emailing from outlook you will
need this code so your Access VBA Code will work.
Put this code in Outlook VB Editor you will find it under Tools then Macro

K Board

Option Explicit

' Code: Send E-mail without Security Warnings
' OUTLOOK 2003 VBA CODE FOR 'ThisOutlookSession' MODULE
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 07/05/2005
' Updated v1.3 - 11/11/2005
'
' Please read the full tutorial here:
' http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning

'
' Please leave the copyright notices in place - Thank you.

Private Sub Application_Startup()

'IGNORE - This forces the VBA project to open and be accessible using
automation
' at any point after startup

End Sub

' FnSendMailSafe
' --------------
' Simply sends an e-mail using Outlook/Simple MAPI.
' Calling this function by Automation will prevent the warnings
' 'A program is trying to send a mesage on your behalf...'
' Also features optional HTML message body and attachments by file path.
'
' The To/CC/BCC/Attachments function parameters can contain multiple items by
seperating
' them by a semicolon. (e.g. for the strTo parameter, '(e-mail address removed);
(e-mail address removed)' is
' acceptable for sending to multiple recipients.
'
' Read more here:
' http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning

'
Public Function FnSendMailSafe(strTo As String, _
strCC As String, _
strBCC As String, _
strSubject As String, _
strMessageBody As String, _
Optional strAttachments As String) As Boolean

' (c) 2005 Wayne Phillips - Written 07/05/2005
' http://www.everythingaccess.com
'
' You are free to use this code within your application(s)
' as long as the copyright notice and this message remains intact.

On Error GoTo ErrorHandler:

Dim MAPISession As Outlook.NameSpace
Dim MAPIFolder As Outlook.MAPIFolder
Dim MAPIMailItem As Outlook.MailItem
Dim oRecipient As Outlook.Recipient
Dim TempArray() As String
Dim varArrayItem As Variant
Dim blnSuccessful As Boolean

'Get the MAPI NameSpace object
Set MAPISession = Application.Session

If Not MAPISession Is Nothing Then
'Logon to the MAPI session
MAPISession.Logon , , True, False

'Create a pointer to the Outbox folder
Set MAPIFolder = MAPISession.GetDefaultFolder(olFolderOutbox)
If Not MAPIFolder Is Nothing Then

'Create a new mail item in the "Outbox" folder
Set MAPIMailItem = MAPIFolder.Items.Add(olMailItem)
If Not MAPIMailItem Is Nothing Then

With MAPIMailItem

'Create the recipients TO
TempArray = Split(strTo, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olTo
Set oRecipient = Nothing

Next varArrayItem

'Create the recipients CC
TempArray = Split(strCC, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olCC
Set oRecipient = Nothing

Next varArrayItem

'Create the recipients BCC
TempArray = Split(strBCC, ";")
For Each varArrayItem In TempArray

Set oRecipient = .Recipients.Add(CStr(Trim
(varArrayItem)))
oRecipient.Type = olBCC
Set oRecipient = Nothing

Next varArrayItem

'Set the message SUBJECT
.Subject = strSubject

'Set the message BODY (HTML or plain text)
If StrComp(Left(strMessageBody, 6), "<HTML>",
vbTextCompare) = 0 Then
.HTMLBody = strMessageBody
Else
.Body = strMessageBody
End If

'Add any specified attachments
TempArray = Split(strAttachments, ";")
For Each varArrayItem In TempArray

.Attachments.Add CStr(Trim(varArrayItem))

Next varArrayItem
.Send 'No return value since the message will remain in
the outbox if it fails to send
Set MAPIMailItem = Nothing
End With
End If
Set MAPIFolder = Nothing
End If
MAPISession.Logoff
End If
'If we got to here, then we shall assume everything went ok.
blnSuccessful = True

ExitRoutine:
Set MAPISession = Nothing
FnSendMailSafe = blnSuccessful

Exit Function

ErrorHandler:
MsgBox "An error has occured in the user defined Outlook VBA function
FnSendMailSafe()" & vbCrLf & vbCrLf & _
"Error Number: " & CStr(Err.Number) & vbCrLf & _
"Error Description: " & Err.Description, vbApplicationModal +
vbCritical
Resume ExitRoutine

End Function
[quoted text clipped - 25 lines]
 
B

babyatx13 via AccessMonster.com

sorry posted twice
Then Put this code in access.

K Board

'This is the procedure that calls the exposed Outlook VBA function...
Public Function FnSafeSendEmail(strTo As String, _
strSubject As String, _
strMessageBody As String, _
Optional strAttachmentPaths As String, _
Optional strCC As String, _
Optional strBCC As String) As Boolean

Dim objOutlook As Object ' Note: Must be late-binding.
Dim objNameSpace As Object
Dim objExplorer As Object
Dim blnSuccessful As Boolean
Dim blnNewInstance As Boolean

'Is an instance of Outlook already open that we can bind to?
On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
On Error GoTo 0

If objOutlook Is Nothing Then

'Outlook isn't already running - create a new instance...
Set objOutlook = CreateObject("Outlook.Application")
blnNewInstance = True

'We need to instantiate the Visual Basic environment... (messy)
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)

objExplorer.CommandBars.FindControl(, 1695).Execute

objExplorer.Close

Set objNameSpace = Nothing
Set objExplorer = Nothing

End If

blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
strSubject, strMessageBody, _
strAttachmentPaths)

If blnNewInstance = True Then objOutlook.Quit
Set objOutlook = Nothing

FnSafeSendEmail = blnSuccessful

End Function
I don't know if this will help, but if you are emailing from outlook you will
need this code so your Access VBA Code will work.
[quoted text clipped - 165 lines]
 
A

Arvin Meyer [MVP]

PaleRider said:
Hi,

Is there a way to send the results of a report to multiple email
recipients
from Access 2003. I already have a table with email addresses and I have
looked at some examples in this forum, but I don't know how to use the
code
examples and make it work for my situation. Can someone help me with how
to
code things to send email to Outlook.

http://www.datastrat.com/Code/MultipleEmail.txt
 
P

PaleRider

Arvin,

I actually found your code at this web address more suitable for me.

http://www.datastrat.com/DataStrat2.html

With some tweaking it works great with no error messages from Outlook. One
question though, is there a way to just send the output from the Report
without attaching anything to the email.

If not, then is it possible to change the name of the attached file to
something other than Senate.

-PR
 
A

Arvin Meyer [MVP]

PaleRider said:
Arvin,

I actually found your code at this web address more suitable for me.

http://www.datastrat.com/DataStrat2.html

With some tweaking it works great with no error messages from Outlook.
One
question though, is there a way to just send the output from the Report
without attaching anything to the email.

Please do not actually send the emails because the list is old and the
Senators are getting tired of junk mail.

Not with the code in the SendObject method. Also remember that this sends
the output as an HTML file. You may want to change it to a snapshot format
if they don't have Access, or with Access 2007, you can also use a PDF
format.
If not, then is it possible to change the name of the attached file to
something other than Senate.

Absolutely. The list box on the left lists all the reports, just choose
what's in your database (and delete the Senate report). Also remember that
by locating and changing both the query and the name of the query in your
code, it will use your data, not the existing data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top