Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 3rd, 2017, 09:07 AM
Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Auto Email Upon Update

I'm having an issue with this code. I'm trying to have this form open Outlook, create a new auto-generated email to a customer so I can send it.

My database stores the customer's name and email address in a table. I created a query from this table and even a form which has all the customer information. I also did the same for the designers. The queries are "qryCustomersExtended" and "qryDesignersExtended". I want outlook to pull email information from there or from the tables, which ever is easier.

I want it to update when the "Status" of a project is changed. The statuses that a project can be are stored in "tblStatus". My code I have so far is as follows:


Code:
Private Sub Status_AfterUpdate()

' With Error goes to Exit_function
Dim oOutlook As Object 'Start MS Outlook
Dim oEmailItem As Object 'New email in Outlook
        
Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.CreateObject(0)

        With oEmailItem
            .To = rs!qryCustomersExtended.EmailAddress
            .CC = rs!qryDivisionDirExtended.EmailAddress
            .Subject = "Project Update for " & rs!qryProjectsExctended.ProjectName
            .Body = "Project Name: " & rs!qryProjectsExctended.ProjectName & vbCr & _
                    "Designer: " & rs!qryDesignersExtended.ContactName & vbCr & _
                    "Project Status: " & rs!tblStatus.StatusTxt & vbCr & _
                    "This email was auto generated from the Project Database. Please do not reply. "
            .BodyFormat = olFormatPlain ' send plain text message
        End With
        
        Set oEmailItem = Nothing
        Set oOutlook = Nothing
        rs.MoveNext
End Sub
When I run it, I get a runtime error and it seems to have a problem with the field:

Code:
Set oEmailItem = oOutlook.CreateObject(0)

Any help would be appreicated.
 
Old March 4th, 2017, 10:07 AM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

What I can readily see is spelling error: qryProjectsExctended, is that not supposed to be Extended?
__________________
Nostalgia 4 Infinity
 
Old March 13th, 2017, 07:12 AM
Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Zakalwe View Post
What I can readily see is spelling error: qryProjectsExctended, is that not supposed to be Extended?
Thanks for that spot. I fixed it but I still get:

Run-time error '-2147221005 (800401f3)': The operation failed.

on the same bit of code.
 
Old March 13th, 2017, 08:38 AM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Mind posting the fixed code?
__________________
Nostalgia 4 Infinity
 
Old March 13th, 2017, 09:26 AM
Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Zakalwe View Post
Mind posting the fixed code?
Here you go:

Code:
Private Sub Status_AfterUpdate()

' With Error goes to Exit_function
Dim oOutlook As Object 'Start MS Outlook
Dim oEmailItem As Object 'New email in Outlook
        
Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.CreateObject(0)

        With oEmailItem
            .To = rs!qryCustomersExtended.EmailAddress
            .CC = rs!qryDivisionDirExtended.EmailAddress
            .Subject = "Project Update for " & rs!qryProjectsExtended.ProjectName
            .Body = "Project Name: " & rs!qryProjectsExtended.ProjectName & vbCr & _
                    "Designer: " & rs!qryDesignersExtended.ContactName & vbCr & _
                    "Project Status: " & rs!tblStatus.StatusTxt & vbCr & _
                    "This email was auto generated from the Project Database. Please do not reply. "
            .BodyFormat = olFormatPlain ' send plain text message
            .Display
        End With
        
        Set oEmailItem = Nothing
        Set oOutlook = Nothing
        rs.MoveNext
End Sub
 
Old March 13th, 2017, 11:18 AM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

I'm Not Sure if you are getting my text, browser is not updating, but I am getting updates via mailbox:

I'm going to use code from this site to cut the amount of work, very useful site: http://codekabinett.com/rdumps.php?L...ss-vba-outlook

I am combining their code and your code.
You'll obviously need to change the query to match yours, or completely remove that SQL and use whatever method you use to get the record set. I


Code:
Sub SendSerialEmail()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb 'Or what ever database you want to explicitly call.
Set rs = db.OpenRecordset("SELECT Firstname, LastName, EmailAddress, IsVIP " & " FROM qryCustomer_SubscribedToNewsletter")
    
    Do Until rs.EOF
        mySendMail rs 'Passing the current resord set as an Argument to the eMail Sub
        rs.MoveNext
    Loop
    
    rs.Close

    Set rs = Nothing
    Set db = Nothing
End Sub
Code:
Sub mySendMail(rsPar As DAO.Recordset)

Dim oOutlook As Object 'Start MS Outlook
Dim oEmailItem As Object 'New email Item in Outlook
        
Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.CreateItem(0)

With oEmailItem
    .To = rsPar.qryCustomersExtended.EmailAddress
    .CC = rsPar.qryDivisionDirExtended.EmailAddress
    .Subject = "Project Update for " & rsPar.qryProjectsExtended.ProjectName
    .Body = "Project Name: " & rsPar.qryProjectsExtended.ProjectName & vbCr & _
            "Designer: " & rsPar.qryDesignersExtended.ContactName & vbCr & _
            "Project Status: " & rsPar.tblStatus.StatusTxt & vbCr & _
            "This email was auto generated from the Project Database. Please do not reply. "
    .Display 'Change to .Send once all is working
End With

Set oEmailItem = Nothing
Set oOutlook = Nothing
End Sub
Preferably, you want to totally make all email inputs like your to, cc & body parameters.
this is to give you flexibility in the event you want to use the email code in other projects.
The starting point would be the body, that way you are able to supply any body you want.
__________________
Nostalgia 4 Infinity

Last edited by Zakalwe; March 13th, 2017 at 01:07 PM.. Reason: Board not updating recent posts.
 
Old March 13th, 2017, 11:32 AM
Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Zakalwe View Post
Please change create object to create item, for the mail item.
Great that fixed one problem, however I am still getting an error. It says:

"Run-time error '424': Object Required"

Code:
Private Sub Status_AfterUpdate()

' With Error goes to Exit_function
Dim oOutlook As Object 'Start MS Outlook
Dim oEmailItem As Object 'New email in Outlook
        
Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.CreateItem(0)

        With oEmailItem
            .To = rs!qryCustomersExtended.EmailAddress
            .CC = rs!qryDivisionDirExtended.EmailAddress
            .Subject = "Project Update for " & rs!qryProjectsExtended.ProjectName
            .Body = "Project Name: " & rs!qryProjectsExtended.ProjectName & vbCr & _
                    "Designer: " & rs!qryDesignersExtended.ContactName & vbCr & _
                    "Project Status: " & rs!tblStatus.StatusTxt & vbCr & _
                    "This email was auto generated from the Project Database. Please do not reply. "
            .BodyFormat = olFormatPlain ' send plain text message
            .Display
        End With
        
        Set oEmailItem = Nothing
        Set oOutlook = Nothing
        rs.MoveNext
End Sub
The debugger highlights:

Code:
.To = rs!qryCustomersExtended.EmailAddress
 
Old March 13th, 2017, 11:49 AM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

Better.
Do this, Debug.print rs!qryCustomersExtended.EmailAddress
Check what it gives you.
Put it just a line above the line that gives you the error
Set a breakpoint on the error line. I suspect an issue with the database code.
I do not see anywhere i
Where you define the rs variable you use in the mail code.
Unfortunately I do not have ms access.
__________________
Nostalgia 4 Infinity
 
Old March 13th, 2017, 12:12 PM
Registered User
 
Join Date: Mar 2017
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Zakalwe View Post
Better.
Do this, Debug.print rs!qryCustomersExtended.EmailAddress
Check what it gives you.
Put it just a line above the line that gives you the error
Set a breakpoint on the error line. I suspect an issue with the database code.
I do not see anywhere i
Where you define the rs variable you use in the mail code.
Unfortunately I do not have ms access.
I'm still getting the same error. I forgot to define the rs variable, what would i set it as in order to get it to pull the email addresses?
 
Old March 13th, 2017, 12:48 PM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Wink

Im going to use code from this site to cut the amount of work, very useful site: http://codekabinett.com/rdumps.php?L...ss-vba-outlook

I am combining their code and your code.

Code:
Sub SendSerialEmail()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Firstname, LastName, EmailAddress, IsVIP " & " FROM qryCustomer_SubscribedToNewsletter")
    
    Do Until rs.EOF
        mySendMail rs 'Passing the current resord set as an Argument to the eMail Sub
        rs.MoveNext
    Loop
    
    rs.Close

    Set rs = Nothing
    Set db = Nothing
End Sub
Code:
Sub mySendMail(rsPar As DAO.Recordset)

Dim oOutlook As Object 'Start MS Outlook
Dim oEmailItem As Object 'New email Item in Outlook
        
Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.CreateItem(0)

With oEmailItem
    .To = rsPar.qryCustomersExtended.EmailAddress
    .CC = rsPar.qryDivisionDirExtended.EmailAddress
    .Subject = "Project Update for " & rsPar.qryProjectsExtended.ProjectName
    .Body = "Project Name: " & rsPar.qryProjectsExtended.ProjectName & vbCr & _
            "Designer: " & rsPar.qryDesignersExtended.ContactName & vbCr & _
            "Project Status: " & rsPar.tblStatus.StatusTxt & vbCr & _
            "This email was auto generated from the Project Database. Please do not reply. "
    .Display 'Change to .Send once all is working
End With

Set oEmailItem = Nothing
Set oOutlook = Nothing
End Sub
__________________
Nostalgia 4 Infinity





Similar Threads
Thread Thread Starter Forum Replies Last Post
auto send of email adamhw Classic ASP Databases 5 July 19th, 2012 08:32 AM
auto send of email adamhw SQL Server 2008 0 July 18th, 2012 03:46 AM
auto email reminder waqi23 Classic ASP Basics 2 June 1st, 2005 03:43 PM
HELP Excel auto email jmunda Excel VBA 1 April 1st, 2005 06:33 AM
Auto Reminder Email netwizard_01 Classic ASP Basics 2 January 26th, 2005 10:32 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.