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.

Solved: Simple Email From Excel?

Discussion in 'Business Applications' started by djgassman, Mar 25, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    I am looking for the best way to have emails sent out periodically based on information within a simple spreadsheet.

    I work for an airline, and receive a daily audit that tells me when certain passengers flying from cities X, Y, and Z meet certain criteria. For instance a simple example would be:

    Passenger Smith From Boston No Luggage
    Passenger Jones From Tampa Luggage
    Passenger White From Tampa No Luggage
    Passenger Walker From Denver Luggage
    Passenger Evans From Tampa No Luggage

    Each day the names, cities will change. For this example, I would like to send a standard email to each of my city managers ONLY if their city appears on the list that day AND column C = "no Luggage".

    I don't want each manager to get the whole list, but only the rows that originate from their city and only those that meet the criteria for that day. Also, how do I tell excel/outlook that if the City = Boston use email address [email protected] and if City = Tampa use email address [email protected]

    I'm pretty good with the basics of excel, but not with macros (which I think may be needed?)... any advice or direction would be appreciated in solving this (hopefully) fairly simple issue!
     
  2. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    How would you like the code to be executed? Are you going to manually run the code each day, or do you want this to take place at a specific time?

    Will you be using Outlook or some other e-mail application?
     
  3. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    When you receive the audits, are the locations mixed like in your example above? If so, there will need to be some type of code to separate the location data on different worksheets.
     
  4. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    I'll be using outlook. And, yes, it would me a manual thing. I would receive the spreadsheet on a daily basis which would be a composite of all the various cities. I'd like to look it over, remove any for various reasons, and then would like be able to just punch "the button" to send the various emails at the same time (but, like I said... each city manager would only receive the row(s) that apply to their city.... make sense?... and if there are 6 rows for Austin, then the manager in that city would get all 6 rows in the one daily email... if two rows are in Tampa, he/she would only get those for Tampa... If there are no Tampa's that day, then no email would be generated....
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    As Cman asked, what email client are you using? Outlook? Outlook Express? Something else? That would have to be known in order to write the code.
    Also, I assume the email is going to the departure city manager, so that "Passenger Smith From Boston No Luggage " would go to the manager in Boston?
    Finally, how many cities are you talking about? It would perhaps make it easier to have a column for each city with a formula so that =IF(cityname=name of city at top of column, 1,"") so that emails would be sent to only the city managers with a number appearing in their column....
     
  6. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    In my example, it's luggage/no luggage... each of the qualifying categories would be similarly simple.... i know i can filter that qualifying column to show only 'no luggage'... but no clue how to send the emails based on the filtered data.... ideas?
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Oh, excuse me - Welcome to the Forum! :)
    Could you upload a sample (no confidential info please) to give us an idea of what we are dealing with?
     
  8. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    we have approximately 60 departure cities i'll be dealing with... not all will be included each day, but any/all might appear on any given day...
     
  9. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    I'll upload a simple sample in a few minutes... thanks, folks for your interest....
     
  10. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    This is an example of the spreadsheet i'll be using...
     

    Attached Files:

  11. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    in essence, these are errors found in an audit... and i need to send the applicable rows to each manager to advise them how many errors were found for their respective stations....
     
  12. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    the body of the email itself need not change... just a simple:

    City Manager X, the following errors were found for your station today.

    row
    row
    row
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    First, I hope that is fake data. What is the trigger that should be used? Code Used?
    If all you want is a count of how many times a city appears, a Pivot Table could count that and then the data from the Pivot Table could be used to send the emails.
     
  14. djgassman

    djgassman Thread Starter

    Joined:
    Mar 25, 2009
    Messages:
    15
    Nothing confidential... this is just dummy data in the format i receive it...

    not looking to count .... actually want to send the rows applicable to each city to the city manager... the actual table would have potentially hundreds of rows each day, with multiple cities much as in the small example i attached....

    right now, i take the table, sort by city, then copy/pase the rows for each different city into separate emails and send 'em out...

    I'd like to have the system look at the spreadsheet, grab and combine the rows for each unique city and send a separate email TO each city with only the rows applicable to them... can excel and outlook interface somehow to do that.... i can do this manually, but it's quite time consuming....
     
  15. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "I'd like to have the system look at the spreadsheet, grab and combine the rows for each unique city ..."

    Select Sheet1 in the attached, press CTRL+Shift+M to run the code.

    What you see is -- Sheet3 becomes selected, rows 1 to 3 have the Boston records, and there's a message box.

    OK the message box & the records for the next city are retrieved. And so on.

    What it would need to do differently IRL is the actual emailing instead of the message display. That requires a 2-field lookup table (City/Email), which requires col A on Sheet2 completed in full. So, I'll check in tomorrow and see if (a) you've done that or (b) someone else has moved this on in some other direction. Since it's now bedtime here.

    HTH :)
     

    Attached Files:

  16. 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/812709

  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