Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Email with peronalised content

Posted on 2014-07-24
10
Medium Priority
?
325 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 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
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: 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

773 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