Help needed for setting up Email Alert for Payment Reminder

[adityaduggal]

Hi,
I would like to setup an email alert for payment reminders. Now I have never setup an email alert so I am kind of confused.
What I would like the email alert to do:

1. Send one email to customers daily with Payment Reminder, now there seems to be no option to send only one consolidated email alert for payments as sending multiple emails based on pending sales invoices would be futile.
2. Email alert should show the total GL balance to customers and also the individual unpaid invoice.
3. How would the system be able to decide which email ID to send the payment reminder to for a customer (in case of multiple contacts)

Any help in this regards would be highly appreciated.

And yes I have read this link: https://erpnext.com/user-guide/setting-up/email-alerts

[rmehta]

A great feature again, but will have to be built. I don’t think this works via Email Alert.

[Deven_Shah]

This would be very-very useful and something like basic necessity.

As per survey of ERPNext (sent before few months), and world over majority of the ERPs are primarily used for booking Sales (Sales Invoices). And the next logical one of the requirements is to send “Payment Reminders” (In form of details of all outstanding details).

Sample mail is shown below.

[Deven_Shah]

One more and better example

[Mukesh_Variyani]

Hi,

I was able to send outstanding invoices along with new invoice through below code: We can also replicate this in case any of the invoice is overdue.

Dear {{ doc.contact_display or 'Sir' }},
<br><br>
Thank you for your business. Please have Sales Invoice {{ doc.name }} as attached.
<br><br>
{% if frappe.get_list("Sales Invoice", 
	fields=["name","posting_date","customer","rounded_total","outstanding_amount"],
	filters={ "docstatus":1, "customer":doc.customer, "outstanding_amount":(">",0),"name":("!=",doc.name)})
%}
Now including this we have below outstanding invoices for {{ doc.customer }}.<br>

<div align="center">
	<table border="1" cellspacing="0" cellpadding="0" width="100%">
		<tbody>
			<tr>
				<td width="18%" valign="top">
					<p align="center">
						<strong>Invoice</strong>
					</p>
				</td>
				<td width="12%" valign="top">
					<p align="center">
						<strong>Date</strong>
					</p>
				</td>
				<td width="37%" valign="top">
					<p align="center">
						<strong>Customer</strong>
					</p>
				</td>
				<td width="13%" valign="top">
					<p align="center">
						<strong>Invoice Amt</strong>
					</p>
				</td>
				<td width="18%" valign="top">
					<p align="center">
						<strong>Outstanding Amt</strong>
					</p>
				</td>
			</tr>
			
			{%- set outstanding_invoice = frappe.get_list("Sales Invoice", 
				fields=["name","posting_date","customer","rounded_total","outstanding_amount"],
				filters={ "docstatus":1, "customer":doc.customer, "outstanding_amount":(">",0),"name":("!=",doc.name)})
			-%}
			{%- for row in outstanding_invoice -%}
			
			<tr>
				<td width="18%" valign="top">
					<p align="center">
						{{ row.name }}
					</p>
				</td>
				<td width="12%" valign="top">
					<p align="center">
						{{ row.posting_date.strftime("%d-%m-%Y") }}
					</p>
				</td>
				<td width="37%" valign="top">
					<p align="center">
						{{ row.customer }}
					</p>
				</td>
				<td width="13%" valign="top">
					<p align="right">
						Rs. {{ row.rounded_total }}
					</p>
				</td>
				<td width="18%" valign="top">
					<p align="right">
						 Rs. {{ row.outstanding_amount}}</strong>
					</p>
				</td>
			</tr>
			{% endfor %}
			<tr>
				<td width="68%" colspan="3" valign="top">
					<p align="center">
						<strong>Total Outstanding</strong>
					</p>
				</td>
				<td width="13%" valign="top">
					<p align="right">
						<strong></strong>
					</p>
				</td>
				<td width="18%" valign="top">
					<p align="right">
						<strong>Rs. {{ outstanding_invoice|sum(attribute='outstanding_amount') }}</strong>
					</p>
				</td>
			</tr>			
		</tbody>
	</table>
</div>
<br>
Request you to process the payment at earliest.
<br><br>
If you have already made payment of any of above invoices please revert with payment details, and accept our gratitude.
<br>
{% endif %}
<br>
Accounts Department<br>
Email: accounts@........com<br>
<strong>{{ doc.company }}</strong>

Final Result: