ACCESS 2007 - Automatically Send and Email Using Outlook Upon a Specific Event

Question

I am trying to create an App in Microsoft Access 2007. How can I silently send an email out using Outlook 2007 upon a specific event without any user interaction. How should I approach this. If you can provide some VBA some it would be extremely nice, but if not, could you guide me in how to accomplish this?

I hadn tried anything yet. I am starting to try it out yet... — AKKAweb, Jul 28 '11 at 16:05

score 3 · Accepted Answer · answered Jul 28 '11 at 16:15

I was able to solve my problem with the following code:

Public Sub SendEmail()

    Email_Bcc = "email@domain.com"
    Email_Body = "Email body!!!!"
    Email_Subject = "Email Subject"

    On Error GoTo debugs

    Set Mail_Object = CreateObject("Outlook.Application")
    Set Mail_Single = Mail_Object.CreateItem(0)

    With Mail_Single
        .Subject = Email_Subject
        .To = Email_Send_To
        .cc = Email_Cc
        .BCC = Email_Bcc
        .Body = Email_Body
        .send
    End With

debugs:
    If Err.Description <> "" Then MsgBox Err.Description

End Sub

You really ought to clean up your code by closing the instance of Outlook before your subroutine exits. Note also that you may encounter security restrictions that prevent this code from running successfully. Google "Outlook Redemption" for one solution that I've used and that works. — David-W-Fenton, Jul 28 '11 at 22:06

score 1 · Answer 2 · answered Jul 21 '11 at 18:00

First declare a couple variables in the event that you want to send the email, or in a function you'd like the event to call.

Public Started As Boolean
Public oApp As Outlook.Application
Public oItem As Outlook.MailItem

Next, open or get Outlook if it's running.

On Error Resume Next
'Get Outlook if it's running
Set oApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
     'Outlook wasn't running, start it from code
     Set oApp = CreateObject("Outlook.Application")
    Started = True
End If

Now, do what you've got to do with your email.

 Set oItem = oApp.CreateItem(olMailItem)
 With oItem
    .To = "email@email.com"
    .Subject = "Your email, sirrah."
    .Body = "Please enjoy this complimentary email."
    'Send the email
    .Send
End With

Finally, close outlook if it wasn't running before and clean up.

Set oItem = Nothing
If Started Then
    oApp.Quit
End If

Is this Visual Basic Code? I tried to plug it in but it is giving me errors. I need VBA. — AKKAweb, Jul 28 '11 at 16:06
Sorry for the late reply. What errors do you get? In your VB editor you may need to go to tools->references and then add "Microsoft outlook object library". Again, sorry for the lateness of my reply, good luck. — Pete Leaning, Aug 11 '11 at 20:20

score 0 · Answer 3 · answered Jul 21 '11 at 13:53

0

You can do this "code-free" by using a macro and the "SendObject" action. Be sure to complete all the necessary arguments To, Subject, Message Text, and then set the Edit Message argument to 'No'.

You can then attach this macro to any event you wish, such as the OnClick event of a button.

answered Jul 21 '11 at 13:53

Jim Parker

365
5
14

I think I need more flexibility than that. However, I will research this SendObject to see how flexible it is. My problem is that the body of the email will change dependant on the issue. Basically, I will create a function that checks the DB at every run and if an item in the inventory list is lower required it will send out an email alert to a specified group of people. – AKKAweb Jul 28 '11 at 16:09

ACCESS 2007 - Automatically Send and Email Using Outlook Upon a Specific Event

3 Answers3

Linked