Email with peronalised content

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?
correlateAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chaauConnect With a Mentor Commented:
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
 
chaauCommented:
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
 
correlateAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
chaauCommented:
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
 
correlateAuthor Commented:
Thanks for that, that's cleared the error, alas now nothing happens when I try to run the code - any ideas?
0
 
chaauCommented:
Try debugging the code. Check that the emailing sub routine is executed without errors
0
 
correlateAuthor Commented:
Works a treat, thank you very much for all your help
0
 
correlateAuthor Commented:
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
 
chaauCommented:
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
 
correlateAuthor Commented:
brilliant - thank you
0
All Courses

From novice to tech pro — start learning today.