Solved

Email with peronalised content

Posted on 2014-07-24
10
301 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
  • 5
  • 5
10 Comments
 
LVL 24

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 24

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 24

Expert Comment

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

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 24

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

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.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 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