Schedule mail with account balance

Hello there,
I am new here but I am using SuiteCRM from few mounth and what I have done is to integrate CRM instance with my company’s ERP.

Now we need to send mail, every mounth, to our customers to remind their account balance with also a PDF where all transactions are showed in detail.
The CRM instance does not know what’s the account balance of the customer and, everytime, it has to request to e-commerce instance, through a simple GET HTTP request, to get the account balance of each customer. Obviously, the CRM knows the e-commerce ID of each customer so it can perform the request using that.
So to be able to do this activity every mounth I am thinking to use WorkFlow, creating some custom condition and action but I do not know if it’s the best way or if exists another way, like scheduling a campaign.
Beside to send mail we need to save a record in CRM just to know that mail to the customer was sent successfully.

The data flow that I imagine is something like the following:
(assuming that I set the mail sending to start on 1st day of every mounth)
[for each customer]

  1. CRM calls the E-Commerce API
  2. E-Commerce give to the CRM the account balance with all active transactions (JSON)
  3. CRM creates a mail body with the account balance and a PDF with all active transactions
  4. CRM sends the mail to the customer with the PDF attached and it saves the mail status (successfully sent, failed, queued etc…)

So the question is, what’s the best way to do this? Have anyone already done anything like this?

Let me know if I have to supply more details or I have to clarify any points.

EVERY TYPE OF HELP IS VERY APPRECIATED.
Thank you so much in advance guys.

Have a look at “Campaigns” module. SuiteCRM is not able to do a different PDF for each record. You can add PDF’s to Templates in Campaigns, but it’s the same for everyone. You can do it manually, of course.

You can also do it with some custom code, and you will definitely require a bit of custom code to consume the ERP API.

My PowerReplacer add-on would save you a ton of work, check out the demo video.

If you want to do it yourself, maybe it’s better to break down the problem into smaller tasks and ask more questions here, I’ll try to help as long as it’s not too extensive.