I was there this morning before it was trashed. Below is
everything referring to emailing. You will need to copy
and paste this somewhere. Watch out for wordrap.
Good luck
Jim
Sending an email from Access
97 / 2000 / 2002
http://www22.brinkster.com/accessory/modules/005.shtml
Make sure you have tools/reference Microsoft outlook 9.0
ovject library checked
For a long time now, there has been a very easy way to
send e-mails from within Access - just use the
DoCmd.SendObject command without specifying an object to
send! This worked (note the past tense) fine if you just
wanted to send basic e-mails using your default, MAPI-
compliant mail software.
However, with the introduction of Access 2000, this
solution became very buggy - not only would it not always
work, sometimes it would fail without any indication which
could be a bit of a problem if you were sending mails
without editing them first. For full details of the
spurious nature of this bug, check out this Microsoft
KnowledgeBase article. And bless 'em, our friends at
Redmond even went so far as to post a (pretty rudimentary)
alternative piece of code, which completists can read
here.
Game over - you don't need to read any further, right?
Wrong! As I've said, the alternative code supplied by
Microsoft is fairly basic but, to be fair, it did form my
starting point for the following piece of code. Basically,
my e-mail function, posted here for your delectation, uses
Outlook to not only send an e-mail but to do so specifying
all the following parameters:
· The sender - handy if you have more than one
Service set up in Outlook
· The recipient(s) - addressee(s), CC and BCC
· The subject
· The message text
· Attachment path
· Voting options
· Urgency
· Edit before sending - true or false
The sender, voting, urgency and attachment path are all
features not unsupported by the old SendObject method of e-
mailing, so in many ways the Access 2000 bug has been
beneficial.
To use this code, you must first set a reference to the
Outlook Object Library (in Access 2000 you can do this
from the Tools, References menu option in the Visual Basic
Editor) - the file you're looking to reference is an OLB
file, e.g. msoutl9.olb for Outlook 2000.
Here's an example of how to call this function. In this
example, an e-mail will be sent to Jo Smith and Estelle
Jones, CC'ing Bob Downes, with a short subject and title,
and an attachment. The message will be sent with high
priority, and Yes/No/Maybe voting buttons. Finally, we'll
edit the message before it's sent.
fctnOutlook , "Jo Smith;Estelle Jones", "Bob
Downes", , "Test this function", "Do you like this neat
code?", "c:\temp\att.txt", "Yes;No;Maybe", 2, True
Note that multiple names in the same parameter are
separated by semi-colons, as are the options in the voting
buttons string.
Finally, you'll see that when you use this code you get
prompted to confirm that it's okay for your program to
send an e-mail using Outlook. This is a virus-protection
method introduced by Microsoft (read their KnowledgeBase
article on this for more info) to prevent script-kiddies
writing malicious code to send self-propagating mails to
everyone in your address book. Using straight Outlook this
is largely unavoidable, so you'll have to get used to
clicking the "Yes" button (unless you want to consider
Outlook Redemption as a freeware solution to this
inconvenience, although that means getting away from
straight Outlook). At least you get an option to allow
your program access to Outlook for up to 10 minutes, which
can be handy if you're about to generate a whole load of e-
mails. Anyway, here's the code for you to cut and paste.
Function fctnOutlook(Optional FromAddr, Optional Addr,
Optional CC, Optional BCC, _
Optional Subject, Optional MessageText, Optional
AttachmentPath, Optional Vote As String = vbNullString, _
Optional Urgency As Byte = 1, Optional EditMessage
As Boolean = True)
' Code sample from Accessory
http://www22.brinkster.com/accessory
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
If Not IsMissing(FromAddr) Then
.SentOnBehalfOfName = FromAddr
End If
If Not IsMissing(Addr) Then
Set objOutlookRecip = .Recipients.Add(Addr)
objOutlookRecip.Type = olTo
End If
If Not IsMissing(CC) Then
Set objOutlookRecip = .Recipients.Add(CC)
objOutlookRecip.Type = olCC
End If
If Not IsMissing(BCC) Then
Set objOutlookRecip = .Recipients.Add(BCC)
objOutlookRecip.Type = olBCC
End If
If Not IsMissing(Subject) Then
.Subject = Subject
End If
If Not IsMissing(MessageText) Then
.Body = MessageText
End If
If Not IsMissing(AttachmentPath) Then
'Check file exists before attaching!
If Len(Dir(AttachmentPath)) > 0 Then
Set objOutlookAttach = .Attachments.Add
(AttachmentPath)
Else
MsgBox "Attachment not found.", vbExclamation
End If
End If
If IsNull(Vote) = False Then
.VotingOptions = Vote
End If
Select Case Urgency
Case 2
.Importance = olImportanceHigh
Case 0
.Importance = olImportanceLow
Case Else
.Importance = olImportanceNormal
End Select
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
If EditMessage Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
End Function
