How to send merged emails with filtered query from Access with VBA?

[TManolache]

Hello!



I have a sport event that is coming up and we have a lot of volunteers and activities to share with them. So i want to send each volunteer an email with the to do list of activities.
I have the following tables:
- volunteers (id volunteer, first name, last name, email)
- activities (id activity, date, hour start, hour stop, location, name, description, coordinator id)
- allocation (id volunteer, id activity). Here i am introducing the allocation of activities and volunteers

Queries:
- [volunteers and activities] in which the tables volunteers and activities are together.

The challenge is to send ONE email to each volunteer with the activities he has to accomplish. I can do a simple merge on the query, but that will result in multiple email when it comes to someone that has multiple activities and there are some people that have 5-10 activities. So i'm thinking that i should send the query data filtered form each email as the email it sends to. This could be a way or maybe other approach will do the job too. Like create a temp report for each volunteer and send it to each volunteer?!?

Can you please help? Thank you!

[pbaldy]

In general terms, you can use two recordsets or a recordset with a report, depending on your desired result. The first recordset would get the volunteers and their info. Within a loop of that, you'd either open a recordset of the current volunteer's activities and put them in the body of the email or attach a report of their activities and then send the email.

[TManolache]

Thanks a lot! Can you please help me with the vba code?

[pbaldy]

You haven't said whether you wanted data in the body or in an attachment. Here's what I started with. There's looping code in a related link.

[TManolache]

It will be ok to have the info in the email body. I am having hard time adapting the code.
Look what i have:

Public Function SendTestEMail()
Dim rsData As Recordset
Dim rsEmails As Recordset
Dim strBody As String
Set rsEmails = CurrentDb.OpenRecordset("SELECT Email FROM oameni;")
While Not rsEmails.EOF
Set rsData = CurrentDb.OpenRecordset("SELECT * FROM [oameni si activitati q cu coordonatori] WHERE Email='" & rsEmails!email & "';")
While Not rsData.EOF
'code to build email body using rsData record
DoCmd.OpenReport "", , , "email='" & rs!email & "'"
DoCmd.SendObject acSendReport, , acFormatHTML, rs!email, , , "subject", "message", False
DoCmd.Close acRecport, "testReport", acSaveNo
rsEmails.MoveNext
Wend
'code to send email
rsData.Close
rsEmails.MoveNext
Wend
End Function


Thanks!

[pbaldy]

Maybe because I'm a dodo who didn't actually paste the link.

http://www.granite.ab.ca/access/emai...recipients.htm

This is for the report, there's a link in there to looping code. It is a bit overly complicated frankly. Here's my template code:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."

  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF

    rs.MoveNext
  Loop

  rs.Close
  set rs = nothing
  set db = nothing

[pbaldy]

Ah, you edited. Where are you stuck? To put the data into the body loop the data recordset. In it populate a variable:

strBody = strBody & rsData!FieldName & "some fixed text & rsData!SomeOtherFieldName & vbCrLf

then use that variable in the SendObject, dropping the report parts. Have to leave for lunch, back in a bit.

[TManolache]

Unfortunately i'm such a beginner that i don't understand much of the code. Can someone please help me with the complete code solution?

[TManolache]

This line of code:

DoCmd.OpenReport "testReport", , , "email='" & rs!email & "'"

is generating Run-time error '424': Object required

[pbaldy]

As a starting point try this (make sure to use your own email address or something so you aren't sending out to actual people yet).

  Dim rsData As DAO.Recordset
  Dim rsEmails As DAO.Recordset
  Dim strBody As String
  Set rsEmails = CurrentDb.OpenRecordset("SELECT Email FROM oameni;")
  While Not rsEmails.EOF
    Set rsData = CurrentDb.OpenRecordset("SELECT * FROM [oameni si activitati q cu coordonatori] WHERE Email='" & rsEmails!email & "';")
    While Not rsData.EOF
      'code to build email body using rsData record
      strBody = strBody & rsData!FieldName & vbCrLf
      rsData.MoveNext
    Wend
    'code to send email
    DoCmd.SendObject acSendNoObject, , , rsEmails!email, , , "subject", strBody, False
    rsData.Close
    rsEmails.MoveNext
  Wend

[pbaldy]

Oh, and change "FieldName" to the name of a data field you want in the body.

[TManolache]

This is fantastic, it really works! Thank you so much! I just started to compose my email now with all the fields in the table.
Any idea on how to transform the strBody in HTML format so i can bold some fields or text? The <b></b> is not working as strBody is plane text.

      strBody = strBody & "<b>ACTIVITATE</b>" & vbCrLf & rsData!activitate & vbCrLf & "DESCRIERE ACTIVITATE" & vbCrLf & rsData![Descriere activitate] & vbCrLf & vbCrLf

[TManolache]

It looks like the code is working only when i have one person in the data base and one email to send with a summary of activities. But when i added the second person with activities i get the Run-time error 2295: Unknown message recipient(s); the message was not sent. The problem is with this code

   DoCmd.SendObject acSendNoObject, , , rsEmails!email, , , "activitati BFR", strBody, False

If i take out the False argument for Edit Message of the DoCmd.SendObject it creates one email and after i click send comes the next one, so in "manual mode" is working. Why not automatic as well?

[pbaldy]

If you're using Outlook you can use automation to get an html body. One link among many:

https://www.devhut.net/2010/09/03/vb...ok-automation/

instead of

.Body = whatever

You'd use

.bodyhtml =...
or
.htmlbody =...

I can't remember offhand and I'm on a mobile right now.

[TManolache]

It looks like the code is working only when i have one person in the data base and one email to send with a summary of activities. But when i added the second person with activities i get the Run-time error 2295: Unknown message recipient(s); the message was not sent. The problem is with this code

   DoCmd.SendObject acSendNoObject, , , rsEmails!email, , , "activitati BFR", strBody, False

If i take out the False argument for Edit Message of the DoCmd.SendObject it creates one email and after i click send comes the next one, so in "manual mode" is working. Why not automatic as well?

...and if i enter same email address for the second person as the first, it generates one email with all the activities of the first and second person which is ok because this is the unique key (email), but also a second email with the body of the first email twice.