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.
