emailing from a form

  • Thread starter Thread starter Brendan Mather
  • Start date Start date
B

Brendan Mather

I have a table called Contact_Info. I have a form that allows me to show
all the contacts from a specified city and sector. Once these contacts
appear on my new form I would like to be able to press a button and it would
take their email addresses, from the 'email' field, and enter them into
Outlook in the "TO:" box so that they can all be emailed the same document.
Any help would be muc appreciated, thank you,


Brendan
 
Brendan,

Here is some code that will loop through your form's recordset and send an
email to each contact. You will need to have references set to the
Microsoft Outlook xx.x Object Library and Microsoft DAO x.xx Object Library.
In addition, the email address for each of your contacts *must be* a text
field, not a hyperlink.

Dim oApp As Outlook.Application
Dim objNewMail As Outlook.MailItem
Dim rs as DAO.Recordset

Set rs = me.RecordsetClone
Set oApp = New Outlook.Application

rs.MoveFirst
Do While Not rs.EOF
Set objNewMail = oApp.CreateItem(olMailItem)
With objNewMail
.To = rs!EmailAddress
.Subject = Me.txtSubject
.Body = "Your text message here."
.Attachments "Filename.xxx"
.Save
.Send
End With
rs.MoveNext
Loop
 
Thanks Cheryl,

Works Great, however, I'd like to be able to put all the email addresses in
the same email message rather than open up a separate email for each
contact. They'll all be getting the same message so it's much easier if
they all get emailed at once. Or is it possible to grab info from an Access
2000 table and somehow create an Outlook Distribution List from it?

Thanks,
Brendan
 
The loop will have to be relocated, of course, and you will need to add a
recipients object. See code below.

As you explore new ways to use Automation with Outlook, do be sure to add
the following link - a must - to your favorites:
http://www.slipstick.com/dev/index.htm

Dim oApp As Outlook.Application
Dim objNewMail As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
Set oApp = New Outlook.Application


Set objNewMail = oApp.CreateItem(olMailItem)
With objNewMail
rs.MoveFirst
Do While Not rs.EOF
If Len(Trim(rs!Email)) > 0 Then
Set objOutlookRecip = .Recipients.Add(rs!Email)
objOutlookRecip.Type = olTo
End If
rs.MoveNext
Loop
.Subject = "Test subject"
.Body = "Your text message here."
.Attachments "Filename.xxx"
.Save
.Send
End With
 
This works well, Thank you. But when I use this method it prompts a
security message from Outlook. And the user has to click OK for every email
address that is inputted, is there anyway to avoid this?

Thanks,

Brendan
 
Anytime you use Automation to send emails from an external program, you are
going to see the security prompt. With my applications, I use the free
ExpressClick Yes utility mentioned below along with code provided at their
website to turn on the utility and then turn it off when the program is
finished sending emails.

The most complete answer regarding options/work-arounds has been provided by
Outlook MVP Sue Mosher and is as follows:

BEGIN QUOTED MATERIAL:

"The security dialogs that pop up when an application tries to access
certain Outlook properties and methods are designed to inhibit the spread of
viruses via Outlook; see
http://www.slipstick.com/outlook/esecup.htm#autosec. If you are a standalone
user, Outlook provides no way to suppress this behavior. However, you can
use a free tool called Express ClickYes
(http://www.express-soft.com/mailmate/clickyes.html) to click the security
dialog buttons automatically. Beware that this means if a virus tries to
send mail using Outlook or gain access to your address book, it will
succeed.

"If you're the administrator in an Exchange Server environment, you can
reduce the impact of the security prompts with administrative tools. See
http://www.slipstick.com/outlook/esecup/admin.htm

"If it's an application you wrote yourself, you can use one of these
approaches to redo the program:

-- Use Extended MAPI (see http://www.slipstick.com/dev/mapi.htm) and C++
or Delphi; this is the most secure method and the only one that Microsoft
recommendeds.

-- Use Redemption (http://www.dimastr.com/redemption/), a third-party
COM library that wraps around Extended MAPI but parallels the Outlook Object
Model

-- Use SendKeys to "click" the buttons on the security dialogs that your
application may trigger. See
http://www.slipstick.com/outlook/esecup.htm#autosec for a link to sample
code.

-- Program the free Express ClickYes
(http://www.express-soft.com/mailmate/clickyes.html) tool to start suspended
and turn it on only when your program needs to have the buttons clicked
automatically."

--
Sue Mosher, Outlook MVP
Outlook and Exchange solutions at http://www.slipstick.com
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers
http://www.slipstick.com/books/jumpstart.htm

END OF QUOTED MATERIAL
 
Back
Top