+ Reply to Thread
Results 1 to 1 of 1

Send email integrating cell data into Subject and Body, with attached invoice

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

    Smile Send email integrating cell data into Subject and Body, with attached invoice

    Hello,

    I got some code online a while ago and started tweaking to my needs (as much as possible) , but being completely new to Macros, I'm stuck.

    Every month I get a list of past due customers, and part of my job is making sure they receive a reminder email, with the details of what they owe, and following up with a second and even third reminder if they don't pay after the first one.

    The spreadsheet I receive from corporate contains
    - Account #
    - Account Name
    - Sales Rep's ID number
    - Original Due date
    - Minimum amount due
    - Date they hit 30 days, 45 days, and 60 days past due (each in own column)
    - Payments and adjustments and the dates we received
    - Total due (which is the minimum plus the current month's services)

    Then I add:
    - Client's email
    - Sales rep's email
    - A calculation of how many days past due they are
    - And three columns for 1st, 2nd, and 3rd notifications to indicate when I contacted them

    Here is what I want the script to do, in laymen's terms:

    1) Look in the 1st, 2nd, and/or 3rd Reminder Notification columns. If empty, assess whether the date in the 30, 45, or 60 day columns are equal or later than today's date. If so, initiate the sending of the email. (ie, if 1st Reminder field is empty, look at 30 day date, if 1st Reminder is filled but 2nd Reminder is empty, look at 45 day date, and so on).
    2) Email specs
    - Use the contents of "Client's email" as the "to" address
    - Use the contents of "Reps's email" as the "cc" address
    - Format the Subject line so it says "Reminder - account for {Client's acct # and client's acct name} is {days} past due"
    - Format the Body so it says
    "Dear {account name} - Thank you for being a valued customer"
    "Line one text about if you've already paid, disregard"
    "Line two text stating the account is now {days} past due. Please remit {Minimum due} to avoid service interruption."
    "If you have questions, please see your attached statement, or call us at 555-555-5555"
    "Thank you"

    3) OPTION 1 - Attach pre-saved PDF file, the path for which can be found in column on spreadsheet named "Attachment File Path". (for this method, the only manual input I would need is to save each client's invoice separately using a pre-determined file-naming convention. Then run the macro so Excel can send the emails. This is not the ideal method, but at this point I'm desperate and will do anything)

    OPTION 2 - Instead of saving PDF invoices into a folder, the other alternative is I could copy/paste each invoice into a new Workbook, adding each invoice to a new sheet. So for example, the invoice for the customer on Row 10 of the past due spreadsheet, could correspond to the Workbook with the invoices (let's call the filename "PastDueInvoices.xlsx"), Sheet 10 (to maintain consistency). The customer on Row 11, their invoice would be on Sheet 11, and so forth.... In this case, have the Macro copy the contents of the corresponding sheet, and paste that into the email body along with the text above.

    OPTION 3 - The two top ways would work, but would still involve me personally being involved in the process each day. Better than nothing, but here is what would rock my world if it's possible: Copy the account number from the first column, open a browser window to www.example.com (the exact URL is accessible only on our server so if I gave it to you, it wouldn't do you any good), and paste the account number into the field named "newzware id" and 'click' Submit. Then Select All, Copy, and Paste directly into the Body of the email, or if that's not possible, paste into workbook sheet, then convert that sheet to email body, or if not possible, convert sheet to PDF and add as attachment.

    4) After the email is sent, add today's date into the corresponding 1st, 2nd, or 3rd notification column accordingly. (this part of the code I pretty much had figured out using someone's template).

    The code I have currently is:

    Please Login or Register  to view this content.
    Problem 1 - what I have even here is not really working. I keep getting compile errors and all sorts of weird issues. I don't really know what I'm looking at on most of it, so it's possible I wacked it up pretty bad.

    Problem 2 - It only initiates the email and saves the notification. I do not know the best way to go about copying/pasting or saving the invoice as an attachment on the email. Please advise on the best way to do this.


    Thank you for your help.Past Due Reminders List.xlsm
    Karrie
    Last edited by davesexcel; 11-09-2012 at 10:09 AM. Reason: code tags required, please read the Foum Rules

+ 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