My role at Snowsport England is office based to handle some of the administrative tasks of a National Governing Body. Here’s a look at my first ‘project’ that I created out of my own proactiveness when arrived at Snowsport England last year.
- A very tedious and manual process for sending a receipt of payment to a payee after taking a payment over the phone.
- Not urgent – the current process worked, it just wasted time and energy.
- Our current system was (simplified) as follows:
- Take payment and record details on Excel
- Open Microsoft Word template & enter details to create a receipt, and save it as a pdf file.
- Open Microsoft Outlook, create a new email – attach pdf receipt from step 2, find payee email address and hit send.
- I wanted to make it as streamlined and automated as possible, taking as little time as possible and being a much more efficient service.
This was arguably my first big macro-coding challenge, with all my previous work being form or formula-based, but after a bit of googling and research I realised it should be possible.
I started with jotting down on a piece of paper the current steps that we took to send a receipt out, highlighting the steps that were mandatory and then drawing a line through all the one’s I thought could be performed by a macro code.
My first challenge was that I didn’t want it to re-send every receipt that had been sent so far. I added a column at the end for whether the payment had gone through fine, and another to log when a receipt had been sent. I wrote a For Next loop to check each row if the payment had gone through and if a receipt had been sent. If the answer was ‘yes’ to both, or ‘no’ to the payment, then it would simply skip to the next payment record. If the answer was ‘yes’ to the payment and ‘no’ to the receipt then it would satisfy a “To Send” ‘boolean’criteria.
If this criteria was met – it would copy the details of the payment record onto a separate sheet where I had re-created the Word Template but as an Excel sheet. The macro then saves this sheet separately as an individual excel spreadsheet- over-writing any previous copies, before prompting the user to enter the payee’s email address.
It then connects to Microsoft Outlook, create’s a new email to the address that’s just been entered, searches for the pdf filename to attach, adding in a bit of pre-determined text to say thanks for paying before sending it off.
Once the email has been sent, a “Message successffuly sent” messgage box pops up, the macro logs that the email has been sent in the right column before moving onto the next payment record and repeating the process.
It’s been a complete success – and hasn’t ever broken or caused any issues – and has been well-received by the other team members. It also helped me cement myself as the “tech expert” in the office which is pretty exagerated, but I don’t mind too much.
It was requested by one of the team members that I alter it slightly to send the receipts as a pdf file rather than an excel document so that it was harder for the payee to change (although we doubt they’d ever need to).
Want to know a bit more about this project or replicate some of the code? Drop me an email email@example.com and I’ll be happy to help!