+ Reply to Thread
Results 1 to 2 of 2

Generate nicely-formatted email notification for past due customers and attach their invoi

  1. #1
    Registered User
    Join Date
    08-09-2012
    Location
    Key West, FL
    MS-Off Ver
    Excel 2010
    Posts
    2

    Generate nicely-formatted email notification for past due customers and attach their invoi

    Hi all, I hope you'll help indulge a beginner. I have some code I found online that closely resembles what I need it to do, and I've managed to stumble through it enough to get an email to send, but it needs some massaging to get it to do exactly what I need.

    Ultimate goal: Generate nicely-formatted email notification for past due customers and attach their invoice.

    It's the "nicely-formatted" and "attach invoice" part that I don't know how to do. Because our invoices are generated from a ColdFusion script (not written by me obviously...way beyond my skills), my plan is to save all of the past due invoices as PDFs in a folder on the desktop at the beginning of each month, using a standard file-naming convention, and then enter the file path in the excel sheet.

    So here's how it goes, in layman's terms. Customer's account is past due. I run the Macro to send reminders. It looks in the various "Reminder Sent" cells to see if the reminder has already been sent, and if nothing is there, looks at the "30 Day" "45 Day" or "60 Day" column respectively. If that date is equal to or later than the current date, it generates an email stating the following (values in "quotations" indicate data that would feel from excel file):

    Dear "Joe Smith"

    Thank you for being a valued customer! This is an automated email reminder, so if you've already sent payment, please disregard.

    However, it appears your account has reached "30" days past due.
    To remain in good standing and prevent your services from being interrupted, please remit payment of "$100.00".

    For additional information, please see the attached copy of your invoice.

    Please send payment to 123 Anystreet Dr. OurTown, FL 33040, or call 555-555-5555 ext 5 to pay by phone.

    Thank you,
    Our Company name
    The table I'm working off of as a sample to learn how this all works is attached "EmailNotificationSampleWorkbook.xlsx"

    And the code I used while testing can be found, of course, in the View Code area of the sheet.

    First problem - Missing the "CC" portion. It sends the email to the designated recipient, but does not copy the intended CC recipient

    Second problem - Formatting. Can I make it look "pretty" without getting into complicated references to HTML files that exist somewhere else on my computer? Or is that the easiest way and I'm thinking it's harder than it really is? Should I use an Outlook Email Template? (I have 2010 version of both Excel and Outlook). What I get currently is just all plain black text. Can I change font size, color, or other formatting like Bolding within the code itself?

    Third Problem - don't know the best code way to add the attachment. I've thumbed through existing resources online, but being a beginner, I don't always understand if what I'm looking at is exactly what I need. Can I just include some code that effectively says "Look in this Cell for a path to a file, Go retrieve that file, attach it to this email."

    Fourth Problem - also related to the invoices, although this is more minor....but if I can go ahead and address is now, great...when the invoice is generated and saved from our system, it always uses the date it was PULLED/generated. In other words, if I spend an entire day pulling invoices at the beginning of the month, all of those invoices I save will have, say, "8/1/12" as the date on them. But if the customer is not getting an email notification until 8/15, because that's when their 30 day past due hits, the email will go out with an attached invoice with "8/1" as the invoice date, which will be confusing to some. Do you have any suggestions around this? No, it is not possible to change the ColdFusion script with which we generate the invoices. I've already been told I have to make due with what I have available currently. One idea....could I, instead of saving the invoices as PDFs, copy and paste the data into a new Sheet (erasing the date manually in the process), so that each invoice is on it's own sheet in the workbook, and then have the code pull from the sheet, attaching as a PDF? Would that work, or is that just causing more work than it's worth? Any other suggestions on a way to work this?

    Thank you all for all of your input ahead of time! This has been a 3 day process for me to just get to this point, so I promise I'm not just trying to pawn off on everyone else something I should already know. And if you can speak in somewhat of laymen terms when posting code, that will be helpful too, as I know what many of the items mean, but not all. Thank you for your help!
    Attached Files Attached Files
    Last edited by KarBar110; 08-09-2012 at 05:04 PM. Reason: forgot attachment

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: Generate nicely-formatted email notification for past due customers and attach their i

    No file attached. Look for Go Advanced -> Manage Attachments.
    David
    (*) Reputation points appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1