sending email from Access

  • Thread starter Thread starter RickB
  • Start date Start date
R

RickB

I have followed Microsoft's article titled:
How to use a recordset to send Outlook e-mail to multiple recipients in
Microsoft Access
Located at:
http://support.microsoft.com/?id=318881

I would like to modify this whereas the user does not get the security
warning that a program is trying to automatically send an email. This
wouldn't be too bad if it only appeared once but there are hundreds of names
in the table and this message appears for each record in the table.

Does anyone have any suggestions?

-rick
 
Rick,
The code from the link it is issuing a .Send for each row in the recordset.
This acts to Outlook as separate requests, so you will be prompted for each.
As far as I know the Outlook security settings cannot be overridden easily.
If they can be, it is definitely not recommended to do so.
If you are sending hundreds of individual e-mails, as opposed to 1 email
with hundreds of recipients then I would suggest you abandon the use of
Outlook and go to a more simplified SMTP client. If you need to send the
same e-mail to hundreds of people you should research the use of Groups as
the recipient and let the server handle it. Or rewrite this to concatenate
the recipients into the TO: and issue a Single Send.

Andrew
 
I understand. You you be able to offer help as to how I could contatenate
the recipients to the To or Bcc fields and create a single send email?

Thank you,

-rick
 
Rick,
You need to change the logic to have the loop for the Recordset to be
where the Recipients are added. I'm sure there is a limit to the number of
Recipients Outlook will process, so you will need to allow for that in your
overall logic.

Andrew
See the modified code below;

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipients to the e-mail message.
==> ' Loop through Recordset here
==> Do Until MyRS.EOF
==> TheAddress = MyRS![EmailAddress]
==> Set objOutlookRecip = .Recipients.Add(TheAddress)
==> objOutlookRecip.Type = olTo
==> MyRS.MoveNext
==> Loop
'********************************************
'Commented Out for Testing without Form
' Add the Cc recipients to the e-mail message.
'If (IsNull(Forms!frmMail!CCAddress)) Then
'Else
' Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
' objOutlookRecip.Type = olCC
'End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = "TEST Multiple Sends" 'Forms!frmMail!Subject
.Body = " TEST Message" 'Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With

Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
 
Back
Top