Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Email Users a list of their projects

Posted on 2014-08-21
6
Medium Priority
?
201 Views
Last Modified: 2014-08-21
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?
0
Comment
Question by:correlate
  • 4
  • 2
6 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40275880
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
 

Author Comment

by:correlate
ID: 40276051
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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40276072
You'll need to change this:

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT EmailAddress, FirstName FROM EmailTestAddresses")
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:correlate
ID: 40276109
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
 

Author Comment

by:correlate
ID: 40276149
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
 

Author Closing Comment

by:correlate
ID: 40276153
Brilliant - thank you very much
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question