Solved

Email with peronalised content

Posted on 2014-07-24
10
310 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

710 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