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
- 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", , "My subject here", "My message here", "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 white paper 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) 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