How to send email automatically from Access 1

[jeep2001]

Using Access 2003, and Outlook 2003. I am looping thru a recordset, I want to send an email automatically if a condidtion exists. I get the email formatted properly, but
it simply displays, its not being sent using the sendobject.

strSubj = "6 month notification"
strRecipient = "Smith, Tom (Exchange)"
strBody = "The following ID " & rs!id & Date - rs!DateOn) & " days old"

DoCmd.SendObject acSendNoObject, , , trRecipient, , , strSubj, strBody, True

When I use outlook objects I am being prompted and need to press the enter key.

How can I get around this. The email looks ok, just wont send automatically.

Thanks

 

[PHV]

DoCmd.SendObject acSendNoObject, , , trRecipient, , , strSubj, strBody, False

Do a google search for outlook object model guard

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[perrymans]

Here is what I use, but you'll need the ClickYes program discussed frequently to get around that annoying pop-up.

Private Sub cmdSendMail_Click()
On Error GoTo cmdSendMail_Err

    Dim myOlApp As Object
    Dim myNameSpace As Object
    Dim myFolder As Object
    Dim myItem As Object
    Dim myAttachments, myRecipient As Object
    Dim recipient As String
    Dim file_name As String
    Dim mySubject As Object
    Dim dbs As Object
    Dim rst As Object
    Dim strSQL As String
    
    strSQL = "SelQ_EmailChapter1"
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveFirst
    While Not rst.EOF
        recipient = rst!Email
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItem(olMailItem)
        Set myAttachments = myItem.Attachments
        Set myRecipient = myItem.Recipients.Add(recipient)
        myItem.Subject = Me.txtSubject '"Message Subject String Here"
        myItem.Body = Me.txtBody '"Put Message Body Text Here"
        myItem.Display
        myItem.Send
        rst.MoveNext
    Wend
    Set myRecipient = Nothing
    Set myAttachments = Nothing
    Set myItem = Nothing
    Set myOlApp = Nothing
    Set rst = Nothing

cmdSendMail_Exit:
    Exit Sub

cmdSendMail_Err:
    MsgBox Err.Description
    Resume cmdSendMail_Exit

End Sub

 

[jeep2001]

Hi

Thanks for the quick response...now it is behaving exactly like if I used OUTLOOK objects.

I get the following message.
A program is trying to automatically send email on your behalf. Do you want to allow? YES NO CANCEL.

Do I need to do a sendkeys here ????

 

[perrymans]

That is the protection in Outlook, it can not be bypassed. Your best bet is to try using the BCC field so it doesn't take as long, or get the ClickYes program that will hit YES for you. It will still take a lonf time though.

http://www.snapfiles.com/get/clickyes.html

Sean.

 

[lewds]

Hi...

Here is a way using Objects

Be sure to add a reference to the Outlook Object in the Tools menu under References


Sub OutlookSend()

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Recipient = MailUser 'string ie.."johnDoe@yahoo.com"

With MailOutLook
.To = Recipient
.Subject = msgSubject 'string ie.."Report"
.Body = msgBody 'string ie.."Your new report"
.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
.Send
End With

Set MailOutLook = Nothing
Set appOutLook = Nothing
MailUser = ""
msgSubject = ""
msgBody = ""
Exit Sub

End Sub

I got this code here, I think, can't remember who to give credit to. I have been using this for almost a year with no problems.

 

[jeep2001]

THanks for the advise, I just downloaded the click yes..

Thanks

 

[jeep2001]

Hey all....Works perfectly.

Thanks

 

[Chew407]

Hello.

Using the method perrymans (Sean) suggested, how do you set the attachment to send with the email?

Set myAttachments = myItem.Attachments

Do I use the location of the file here (ie. C:\My Attachments\email) and what is the proper syntax?

Thanks

Chew

 

[PHV]

Use the Add method of the Attachments collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[perrymans]

Now try this on for size, no pop-up security at all!!!

There is a COM add-in called Redemption that allows you to bypass that security pop-up, even avoiding the need for programs such as ClickYes (which you have to wait while it clicks all those buttons, 5 seconds for each e-mail).

Redemption is free for non-commercial use and can be downloaded from:

http://www.dimastr.com/redemption

Now, here is my earlier code, altered now, to avoid the Outlook security. I added two extra variables to reference the SafeOutlook Library (don't forget to add it to you references). Then I create the message object, save it as a the SafeMailItem from Redemption, THEN add the recipients as SafeRecipients. See below and enjoy!!!

Sean Perryman

Private Sub cmdSendMail_Click()
On Error GoTo cmdSendMail_Err

    Dim myOlApp As Object
    Dim myNameSpace As Object
    Dim myFolder As Object
    Dim myItem As Object
    Dim myAttachments, myRecipient As Object
    Dim recipient As String
    Dim file_name As String
    Dim mySubject As Object
    Dim dbs As Object
    Dim rst As Object
    Dim strSQL As String
    
    'These two are the additional references
    Dim sMail As Redemption.SafeMailItem
    Dim objMe As Redemption.SafeRecipient
    
    strSQL = "SelQ_EmailAll"
        
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveFirst
    While Not rst.EOF
        recipient = rst!Email
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItem(olMailItem)
        Set myAttachments = myItem.Attachments
        myItem.Subject = Me.txtSubject '"Message Subject String Here"
        myItem.Body = Me.txtBody '"Put Message Body Text Here"
        'create the SafeMailItem with Redemption
        Set sMail = CreateObject("Redemption.SafeMailItem")
        myItem.Save
        sMail.Item = myItem
        Set objMe = sMail.Recipients.Add(recipient)
        sMail.Send
        
        rst.MoveNext

    Wend

    'Set variables back to nothing
    Set myRecipient = Nothing
    Set myAttachments = Nothing
    Set myItem = Nothing
    Set myOlApp = Nothing
    Set sMail = Nothing
    Set objMe = Nothing
    Set rst = Nothing

cmdSendMail_Exit:
    Exit Sub

cmdSendMail_Err:
    MsgBox Err.Description
    Resume cmdSendMail_Exit

End Sub