Was our email read

[liderbug]

I host a org/site where a monthly newsletter is sent to all the members of the group (~200). Somewhat aside, I have a problem attaching a multi-meg PDF or images in the body of the email. To that end the PDF resides on the server and the email has a link. Images are also on the server with - img src=server/img.png. etc. Keeps emails small in data, large in display.

The lead in to the question: Did they get the email? Did they read it? Our Apache logs show the IP, time, etc. But not who. When the email goes out - via SQL "select name, email from membership ..." I can personalize the email, tell them if their dues are paid, etc. So solve the problem of "who" read their email, who hasn't, I add a dummy image at the bottom of the email "img src=https://server/emailread/name@email.com". Yes it shows as a broken image but in the Apache log I can grep emailread access.log and get a list of who read the newsletter or special notice. So, that's what I came up with. Now the question: Is there a better way?

Thanks

 

[Michalio]

Hi,
Probably in your email you have the logo of the sender or any other image that is in each mail. You can use that to validate the email was opened (not equals to read).
For example you have a logo on https://example.com/logo.png, you can change it to https://example.com/newsletter-logo.php?token=<some token>. Then you need to generate the token, to validate the user and the specified email (because we need to know that the user opened the first newsletter, but not the second and third) we need to use user id and email id to generate that. We can use the database table to generate random token and store user id and email id inside the table, but now we will encode the data inside the token using base64_encode function:

$token = base64_encode(json_encode([
  'userEmail' => $user['email'],
  'mailId' => $newsletter['id'],
]));
$logoUrl = 'https://example.com/newsletter-logo.php?token=' . urlencode($token);

Then in the newsletter-logo.php, you can read the data from token:

<?php
// function to insert into database information about the mail was opened and when
function setMailAsReader(string $email, int $messageId) {
  // insert (`email`, `message`, `openedDate`) (:email, :messageId, now()) INTO `MessageOpenedInformation`
}

// check the request
if (!isset($_GET['token'])) {
  die('Invalid request');
}
try {
  // ecode the data from request
  $tokenData = json_decode(base64_decode($_GET['token']));
  setMailAsReader($tokenData[->userEmail, $tokenData->mailId);
} catch(Exception $e) {
  die('Invalid token');
}

// send the image to the client
header ('Content-Type: image/png');
file('logo.png');