This Joomla CLI script lets you send a DB query output as CSV attachment via Email

joomla-cli-script-email-csv-attachment.php

<?php
/**
 * @version    3.6
 * @package    CLI
 * @author     Manoj L<manoj_l@techjoomla.com>
 * @copyright  Copyright (c) 2009-2017 Manoj L. All rights reserved
 * @license    GNU General Public License version 2, or later
 */

// Make sure this is being called from the command line
if (PHP_SAPI !== 'cli')
{
	die('This is a command line only application.');
}

// Ensure, this has a valid entry point.
const _JEXEC = 1;

// Import necessary class files
define('JPATH_BASE', realpath(dirname(__FILE__) . '/..'));

require_once JPATH_BASE . '/includes/defines.php';
require_once JPATH_BASE . '/includes/framework.php';
require_once JPATH_BASE . '/libraries/import.php';

// Load Joomla configuration file
require_once JPATH_CONFIGURATION . '/configuration.php';

// Import joomla cli app file
jimport('joomla.application.cli');

// Error reporting
ini_set('display_errors', 'On');

/**
 * Class for cli - send csv email
 *
 * @since  3.6
 */
class SendCsvEmail extends JApplicationCli
{
	/**
	 * Execute cli task
	 *
	 * @return   void
	 *
	 * @since    3.6
	 */
	public function execute()
	{
		// Define vars
		$app   = JFactory::getApplication('site');
		$db    = JFactory::getDbo();
		$query = $db->getQuery(true);

		// Query
		$query->select('u.id AS userid, u.name, u.email');
		$query->from('#__users as u');
		$query->order('u.id');
		$db->setQuery($query);
		$users = $db->loadObjectList();

		// Genrate CSV
		$filePath = JPATH_SITE . '/tmp/users-report-' . date('Y-m-d') . '-.csv';
		$fp = fopen($filePath, 'w');

		// Add CSV first row
		$csvFirstRow = array('userid', 'name', 'email');
		fputcsv($fp, $csvFirstRow);

		foreach ($users as $user)
		{
			// Convert to array for fputcsv
			$user = (array) $user;
			fputcsv($fp, $user);
		}

		// Get mail config from Joomla config
		$from      = $app->getCfg('mailfrom');
		$fromname  = $app->getCfg('fromname');

		$emailSubject = 'Users report ' . date('Y-m-d');
		$emailBody    = 'Hi, <br/><br/>Please find attached user information CSV export.<br/><br/>Regards.';

		$mailer = JFactory::getMailer();
		$mailer->isHTML(true);
		$mailer->Encoding = 'base64';

		// Add recepient
		$mailer->addRecipient('manoj@dummy-domain.com');

		$mailer->setSender(array($from, $fromname));
		$mailer->setSubject($emailSubject);
		$mailer->setBody($emailBody);

		// Add CSV attachment
		$mailer->addAttachment($filePath);

		if ($mailer->Send())
		{
			$this->out(sprintf('Email sent'));
		}
		else
		{
			$this->out(sprintf('Email sending failed'));
		}

		// Delete temp. csv file
		@unlink($filePath);
	}
}

// Call task
JApplicationCli::getInstance('SendCsvEmail')->execute();

To use this
Open terminal, Browse to Joomla root
cd /var/www/my-joomla-site

Execute php file
php cli/joomla-cli-script-email-csv-attachment.php

You will see this as o/p on successful email sending
Email sent

You can also setup cronjob to automate email sending.