Solved

Email with peronalised content

Posted on 2014-07-24
10
316 Views
Last Modified: 2014-07-25
Dear Experts

I am building a reporting tool to sit on the back of our CRM – the tool is designed to email users a list of records they set up that have been badly coded (aka Sins)

I would like to be able to email each user their list of sins – however rather than emailing a report I would like the list of Sins to be in the body of the email.  The data comes from a query called sinners and has the following fields in it:

EmailAddress (who the email goes to)
Creator (the name of the recipient)
Person (the name of the record that is badly coded)
Role (more of the record that is badly coded)
Offences (why that record is badly coded)

Each recipient can have multiple ‘Sins’ and so their email address can / is repeated in the query against each 'Sin'.

I have attached an example of the query in excel

Sinners.xlsx

Can anyone help?
0
Comment
Question by:correlate
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40218468
There are plenty of Access VBA email functions on the Internet. Please choose any of your liking. Take for example this function:
Public Sub doEmailOutlook(sTo As String, sSubject As String, sBody As String, sFile As String, Optional bFileAttach As Boolean = False, Optional bPreview As Boolean = False)
On Error GoTo doEmailOutlookErr

Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object

Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.createitem(0)
With oMail
    .ReplyRecipients.Add "YourEmail@YourDomain.com"
    .to = sTo
    '.body = sBody
    .htmlbody = sBody
    .Subject = sSubject
    If sFile <> "" Then
        .Attachments.Add (sFile)
    End If
    If bFileAttach = True Then
        .Attachments.Add (CurrentProject.path & "\YourFile.pdf")
    End If
    If bPreview = True Then
        .display
    Else
        .Send
    End If
End With

If bPreview = False Then
    Set oMail = Nothing
    Set oLook = Nothing
End If
Exit Sub

doEmailOutlookErrExit:
    Exit Sub

doEmailOutlookErr:
    MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
    Resume doEmailOutlookErrExit
End Sub

Open in new window

Add this (or other functions) to your module in Access.
Now, create a form and put a button there. Call it "Annoy sinners!". In the code of the function write this:
Private Sub Command0_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select EmailAddress, Creator, Person, Role, Offences qrySinners ORDER BY Creator, Person")
Dim creator As String
Dim theirEmail As String
Dim body As String
creator = ""
body = ""
theirEmail = ""
Do While Not rst.EOF
    If creator <> "" And creator <> rst!Creator Then
         body = body & vbCrLf & "Regards," & vbCrLf & "The Administrator"
         Call doEmailOutlook(theirEmail, "Your sins", body, "")
         body = ""
         creator = rst!Creator
         theirEmail = rst!EmailAddress
    End If
    If body = "" Then
          body = "Hi " & rst!Creator & "," & vbCrLf & vbCrLf
          body = body & "You have sinned again. Please find some time today to fix it." & vbCrLf
    End If
    body = body & rst!Person & ": " & rst!Role & ": " & rst!Offences & vbCrLf
    rst.MoveNext
Loop

If creator <> "" Then
     body = body & vbCrLf & "Regards," & vbCrLf & "The Administrator"
     Call doEmailOutlook(theirEmail, "Your sins", body, "")
End If
'Cleanup
rst.Close
Set rst = Nothing
End Sub

Open in new window

0
 

Author Comment

by:correlate
ID: 40218742
HI thanks for this

I am however getting a run time error (3075) - Syntax error (missing operator) in query expression 'Offences qrySinners ORDER BY Creator' - any ideas?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40218744
Sure. I forgot the from clause. Use this
Select EmailAddress, Creator, Person, Role, Offences from qrySinners ORDER BY Creator, Person

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:correlate
ID: 40218753
Thanks for that, that's cleared the error, alas now nothing happens when I try to run the code - any ideas?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40218792
Try debugging the code. Check that the emailing sub routine is executed without errors
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 40218800
I just realised there is a bug in the program. See this line

creator = rst!Creator.

It is inside if. It needs to be after the first if, like this
If creator <> "" And creator <> rst!Creator Then
         body = body & vbCrLf & "Regards," & vbCrLf & "The Administrator"
         Call doEmailOutlook(theirEmail, "Your sins", body, "")
         body = ""
         
         theirEmail = rst!EmailAddress
    End If
creator = rst!Creator
    If body = "" Then
          body = "Hi " & rst!Creator & "," & vbCrLf & vbCrLf
          body = body & "You have sinned again. Please find some time today to fix it." & vbCrLf
    End If
0
 

Author Closing Comment

by:correlate
ID: 40218827
Works a treat, thank you very much for all your help
0
 

Author Comment

by:correlate
ID: 40219099
Hi,  Just spotted one issue that I hadn't noticed - the email for the first recipient never has his / her email address added, the rest of it is fine (i.e. the email is personalised & has the right data in it) - any ideas?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40219173
Same bug. TheirEmail was not initialised.
If creator <> "" And creator <> rst!Creator Then
          body = body & vbCrLf & "Regards," & vbCrLf & "The Administrator"
          Call doEmailOutlook(theirEmail, "Your sins", body, "")
          body = ""
         
         
     End If
 creator = rst!Creator
theirEmail = rst!EmailAddress
     If body = "" Then
           body = "Hi " & rst!Creator & "," & vbCrLf & vbCrLf
           body = body & "You have sinned again. Please find some time today to fix it." & vbCrLf
     End If
0
 

Author Comment

by:correlate
ID: 40219362
brilliant - thank you
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

624 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