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 13th, 2017, 01:08 PM
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





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.