Email Users a list of their projects

Dear Experts,

I’m building an Access Db to produce reports from our project management system and I need to be able to email all users a list of all their live projects at the press of a button.  The details of the projects needs to be put into the body of the email rather than a report.

The query that contains all the info has the following:
UserIDString, FirstName, EmailAddress, CompanyName, Name, Status

The picture below shows what the data looks like …

What the data looks like
The email should be something like …

To: = [EmailAddress]

Subject “Your live projects”

Dear [FirstName]

Please find below a list of your current projects

[CompanyName] – [Name] – [Status]
[CompanyName] – [Name] – [Status]
[CompanyName] – [Name] – [Status]
etc

Can anyone help?
correlateAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use two recordsets to handle this:

Open a recordset based on the EmailAddress field, for Distinct values, then loop through those results and open a second recordset for each Project associated with the EmailAddress:

Dim rst As DAO.Recordset
Dim rstProjects As DAO.Recordset
Dim sBody As String

Set rst = Currentdb.OpenRecordset("SELECT DISTINCT EmailAddress FROM YourQuery")

Do Until rst.EOF
  Set rstProjects=Currentdb.OpenRecordset("SELECT * FROM YourQuery WHERE EmailAddress='" & rst("EmailAddress") & "'")
  Do Until rstProject.EOF
    sBody = sBody & rstProjects("Name") & vbCrLF
    rstProject.MoveNext
  Loop
 
  sBody = "Dear " & rst("FirstName") & vbCrLf & vbCrLf & " blah blah " & vbCrLf & vbCrLf & sBody
  DoCmd.SendObject acSendNoObject, , , rst("EmailAddress"),  ,  , "YourSubject",  sBody
 
rst.MoveNext
Loop
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
correlateAuthor Commented:
Hi

Thanks for this, unfortunately I'm getting an error (item not found in this collection) pointing the line beginning sBody (Line 15)

I have attached the code below ... Any ideas?

Private Sub EmailTestSend_Click()
Dim rst As DAO.Recordset
 Dim rstProjects As DAO.Recordset
 Dim sBody As String

 Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT EmailAddress FROM EmailTestAddresses")

 Do Until rst.EOF
   Set rstProjects = CurrentDb.OpenRecordset("SELECT * FROM EmailTestBodyofEmail WHERE EmailAddress='" & rst("EmailAddress") & "'")
   Do Until rstProjects.EOF
     sBody = sBody & rstProjects("Name") & vbCrLf
     rstProjects.MoveNext
   Loop
   
   sBody = "Dear " & rst("FirstName") & vbCrLf & vbCrLf & " blah blah " & vbCrLf & vbCrLf & sBody
   DoCmd.SendObject acSendNoObject, , , rst("EmailAddress"), , , "YourSubject", sBody
   
 rst.MoveNext
 Loop
 
 MsgBox "Done"
 
End Sub

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'll need to change this:

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT EmailAddress, FirstName FROM EmailTestAddresses")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

correlateAuthor Commented:
That works perfectly - thank you very much, just one quick question through, is there a way to send the emails without them being spellchecked (either by turning outlook spell check off then back on again or a different method of sending that avoids spell checking)
0
correlateAuthor Commented:
Don't worry re that last request - found an alternative way of sending it which by passes the spell checker (code below)

Dim ol As New Outlook.Application
Dim olMail As Outlook.MailItem

    Set olMail = ol.CreateItem(olMailItem)

    With olMail
  .To = rst("EmailAddress")
  .Subject = "Your Live Assignments on Invenias"
  .HTMLBody = sBody
  .send
  End With

   
 rst.MoveNext
 Loop

Open in new window

0
correlateAuthor Commented:
Brilliant - thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.