Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 3rd, 2017, 08:07 AM
Registered User
Points: 25, Level: 1
Points: 25, Level: 1 Points: 25, Level: 1 Points: 25, Level: 1
Activity: 25%
Activity: 25% Activity: 25% Activity: 25%
 
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.
Reply With Quote
  #2 (permalink)  
Old March 4th, 2017, 09:07 AM
Authorized User
Points: 120, Level: 2
Points: 120, Level: 2 Points: 120, Level: 2 Points: 120, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2015
Location: South Africa
Posts: 28
Thanks: 0
Thanked 3 Times in 3 Posts
Default

What I can readily see is spelling error: qryProjectsExctended, is that not supposed to be Extended?
__________________
Nostalgia 4 Infinity
Reply With Quote
  #3 (permalink)  
Old March 13th, 2017, 07:12 AM
Registered User
Points: 25, Level: 1
Points: 25, Level: 1 Points: 25, Level: 1 Points: 25, Level: 1
Activity: 25%
Activity: 25% Activity: 25% Activity: 25%
 
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.
Reply With Quote
  #4 (permalink)  
Old March 13th, 2017, 08:38 AM
Authorized User
Points: 120, Level: 2
Points: 120, Level: 2 Points: 120, Level: 2 Points: 120, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2015
Location: South Africa
Posts: 28
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Mind posting the fixed code?
__________________
Nostalgia 4 Infinity
Reply With Quote
  #5 (permalink)  
Old March 13th, 2017, 09:26 AM
Registered User
Points: 25, Level: 1
Points: 25, Level: 1 Points: 25, Level: 1 Points: 25, Level: 1
Activity: 25%
Activity: 25% Activity: 25% Activity: 25%
 
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
Reply With Quote
  #6 (permalink)  
Old March 13th, 2017, 11:18 AM
Authorized User
Points: 120, Level: 2
Points: 120, Level: 2 Points: 120, Level: 2 Points: 120, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2015
Location: South Africa
Posts: 28
Thanks: 0
Thanked 3 Times in 3 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.
Reply With Quote
  #7 (permalink)  
Old March 13th, 2017, 11:32 AM
Registered User
Points: 25, Level: 1
Points: 25, Level: 1 Points: 25, Level: 1 Points: 25, Level: 1
Activity: 25%
Activity: 25% Activity: 25% Activity: 25%
 
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
Reply With Quote
  #8 (permalink)  
Old March 13th, 2017, 11:49 AM
Authorized User
Points: 120, Level: 2
Points: 120, Level: 2 Points: 120, Level: 2 Points: 120, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2015
Location: South Africa
Posts: 28
Thanks: 0
Thanked 3 Times in 3 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
Reply With Quote
  #9 (permalink)  
Old March 13th, 2017, 12:12 PM
Registered User
Points: 25, Level: 1
Points: 25, Level: 1 Points: 25, Level: 1 Points: 25, Level: 1
Activity: 25%
Activity: 25% Activity: 25% Activity: 25%
 
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?
Reply With Quote
  #10 (permalink)  
Old March 13th, 2017, 12:48 PM
Authorized User
Points: 120, Level: 2
Points: 120, Level: 2 Points: 120, Level: 2 Points: 120, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2015
Location: South Africa
Posts: 28
Thanks: 0
Thanked 3 Times in 3 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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 05:33 AM
Auto Reminder Email netwizard_01 Classic ASP Basics 2 January 26th, 2005 09:32 AM



All times are GMT -4. The time now is 04:08 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.