1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Emailing multiple recipients from Excel Based off Cell Value Collate to one email

Discussion in 'Business Applications' started by Visual-Learner, Jul 10, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Visual-Learner

    Visual-Learner Thread Starter

    Joined:
    Jul 10, 2013
    Messages:
    4
    Good Afternoon - this is a follow-up to an earlier post that has been closed.

    http://forums.techguy.org/business-applications/1090938-emailing-multiple-recipients-excel-based.html

    I would like to do something similar.

    My Excel sheet has a list of Email addresses in Column A (with duplicate email addresses).
    I have several other columns with data that that I would like to have appear in the body of the email in Outlook.

    I need to collate each row with the same email address so ONLY 1 email is sent to each recipient.

    Is this something easy to do?
    I have little to no VBA coding skills

    Attached is an Excel mockup of what I am attempting to accomplish.

    The 1st tab called "Sample Data" is basically the raw data I want to leverage.
    (which I also tried to display below)


    Email Address .....Invoice Number .....Date..... .....Dollars
    [email protected] .............1 ...............7/3/2013 ......$10,000
    [email protected] ..............2 ...............7/9/2013...... $50,000

    [email protected] ..........3 ...............7/9/2013 ......$40,000

    [email protected] ............4 ...............7/10/2013 .....$1,000

    [email protected] ............5 ...............7/11/2013 .....$3,000
    The 2nd tab called "Body of Email" is an example around how I would like to see the data appear in the email.
    Even though [email protected] appears 3 times in the above example, I ONLY want him to receive 1 email that contains 3 rows of data in the body of the email.

    Lastly, if there is a way to put in a generic subject line (ex. Invoices) would also be greatly appreciated.

    btw - I am using Excel 2007 on Windows 7.


    Thanks in advance for any help you can provide,
    The Visual Learner
     

    Attached Files:

  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi visual-learner

    It would save a great deal of time if you supplied the real tab names and the actual layout of your spreadsheet if it is different from your example - including any columns used that do not show up in your example.

    Also, an indication of how many rows you may have.

    Also what is the workflow? How do you record that an invoice has been sent and then when it has been received. Is there a time difference between those two where you would re-send the invoice i.e. if it is greater than 30 days since you sent the invoice and have not received payment-would you want to re-send the invoice?
     
  3. Visual-Learner

    Visual-Learner Thread Starter

    Joined:
    Jul 10, 2013
    Messages:
    4
    Hi XCubed - thank you so much for responding.

    I will be getting the final format tomorrow and will post it once I get it.
    With regard to the number of rows, I believe it should be less than 2,000.

    The workflow is fairly straightforward.
    Each week, I will get a new file with a complete list of invoices (including ones that may have carried over from previous weeks).

    Regardless if the record is new or not, I will send out the updated list of open invoices to each Sales Rep.
    We do not need any logic built around 'aging' invoices and such.

    I hope this provides more clarity.
    Again I really appreciate any direction or insight you can offer.

    Thanks,
    Visual-Learner
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    What would the average number of emails be or, in other words, how many sales reps are there?

    Here is a preliminary go at it based on your sample.

    The one aspect of this that you may be disappointed with is the format of the email message. Besides being complicated to replicate the table format you want (complicated because I'm not sure how to do it) it is often a wasted effort as many people set their email systems to display text only - which would muck up any fancy formatting we might include.

    Give this a go and see what you think.
     

    Attached Files:

  5. Visual-Learner

    Visual-Learner Thread Starter

    Joined:
    Jul 10, 2013
    Messages:
    4
    Thanks XCubed - I will try running the macro's shortly.
    With regard to the # of Sales Reps, we have ~300, but depending on the approach, we may end up only having to deliver this information to their Managers (~25).

    With regard to the formatting piece, would it be easier to create unique Excel files (with only the Sale's Rep's Invoies) and send them as attachments instead?

    I should have more information after tomorrow morning's meeting.

    Thanks Again for you Assistance,
    Visual-Learner
     
  6. Visual-Learner

    Visual-Learner Thread Starter

    Joined:
    Jul 10, 2013
    Messages:
    4
    Hi XCubed - I have some more information.

    We will average between 400 and 800 records(invoices) per week.
    The Sales Rep count will be lower (~100) due to this only being needed for a sub-set of Reps.

    It sounds like I won't see the actual file format until mid-next week.

    Once I get this information, I will upload a new file.

    One question I do have for you.
    I am seeing 4 macro's in your report (Macro1, Macro2, Macro3 and mail_invoice).
    Do I just run them in that sequence?

    Thanks,
    Visual-Learner
     
  7. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi VL

    Mail_Invoice is the only Macro that needs to be run. The others are just my "scratch-pad" stuff which can be deleted.

    Attaching a file to the email is easy enough. I recommend that the attached file is in PDF format - this will avoid any Excel version differences (which can happen even within the same company).

    You might also start thinking about what text you want for the email and in the file e.g.

    "Outstanding invoices as at 12/07/2013"

    You may want to personalise that with the name of the sales rep which, hopefully, will be included in the file that you receive (other than their email address). You may also want to add some instruction in terms of actions required and due dates.

    Attached is an example of generating emails with a PDF attachment.
     

    Attached Files:

  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1103274

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice