Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Automatically send email based on Excel cell values - Different email address per line (Macro)

    Hi. l’m new here and hope I can get some help with a macro that I think is possible, but don't have the Excel knowledge to complete. I have a workbook that is a query from PeopleSoft that lists Departments that are currently over budget in certain expense pools. I need an automatic email sent to each department manager to notify them that their budget requires attention. There are over 500 exceptions per week and it will take very long to set up individual emails to each manager. I have been trying to get this put together for some time now, but have maxed my current knowledge. Any help would be greatly appreciated and save me TONS of time!!! I am going to run the query and send the email weekly. Below is what I believe to be the necessary data and if I’ve missed something please let me know.

    Using Excel 2010
    Email is Zimbra or Thunderbird
    Column E is the Department ID

    Column I is the deficit amount
    Column J is the deficit pool
    Column L is the Manager Name
    Column M is the Manager Email address

    The worksheet is usually around 500 lines and if a manager has multiple departments, I would like each department and deficit listed in one email (don't want a manager to get 10 different emails). If this is too difficult to code, 1 email to line would work

    Email Subject = “Department (Data in Column E) Budget Error” - If multiple, separate with commas
    The body of the emails needs to read:
    “Department Manager,
    The following departments have budget errors that require a reallocation. At your earliest convenience, please send a Reallocation Form to the Treasurer's Office. Please move enough funds to cover the current deficit plus any additional funds the department expects to pay this year. In regards to all future check requests, please check your departments budgets in web reports prior to check submission to ensure there are enough funds to cover the requested payments. Also, please review the P-Card account (53201) and reconcile any necessary charges. The journal entry can be scanned to budfin@xyz.com.

    Chartfield String Account Description Current Deficit
    (Data in Column G) (Data in Column J) (Data in Column I)

    Thanks”

    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi tomas

    Welcome to the Lounge as a new poster.
    And many thanks for posting a sample workbook.
    This makes it much easier to help you.

    I'll have a look at the file and will post back tomorrow.

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    tomashammar (2016-04-07)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi tomas

    I have been struck with another virus.
    I am resisting a rest.

    Currently testing my proposed solution, hope to post later today.

    zeddy

  5. #4
    Star Lounger Graham Mayor's Avatar
    Join Date
    Mar 2016
    Location
    Cyprus
    Posts
    68
    Thanks
    0
    Thanked 24 Times in 24 Posts
    This looks like a Many to One mail merge. See http://www.gmayor.com/ManyToOne.htm which will do what you require without the need for any programming. It will however require Word and Outlook as well as Excel to prepare and send the messages.
    Graham Mayor - Word MVP
    http://www.gmayor.com

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Graham

    Email is Zimbra or Thunderbird
    ..my excel version is nearly ready.

    zeddy

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi tomas

    Please try out my attached file.
    It uses the Excel Envelope method to send emails.
    Have your email system running when you test it.

    It worked OK on my systems (gmail and outlook)
    I cannot test it with Zimbra or Thunderbird (I don't have them).

    On sheet [emailer], you can use the 'clicker' to select a Manager, or send ALL emails.

    I used one of my custom functions to 'join' the Dept names for the email Subject line.
    The code is documented with examples.

    I would appreciate others testing this to see if it works OK on their systems.
    Maud is better at vba emailing stuff than me, so maybe he could check it out too.

    zeddy
    Attached Files Attached Files

  8. #7
    New Lounger
    Join Date
    Apr 2016
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Zeddy. I hope you are feeling better!! Thanks so much for your help with this macro. I was not able to use this Macro with Thunderbird or Zimbra, but I managed to use Outlook in my office and it worked! I don't know if anyone else was able to get it to work with Zimbra or Thunderbird, but what you created should work perfectly! Thanks SOOOO much!!!!!

    Tom

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi tomas

    ..great news.
    Yes, I'm feeling much better.
    The Pharmacy initially gave me the wrong drugs.
    I should have spotted it earlier.
    They had a sign up
    "We dispense with accuracy"

    ..now, if you wanted to tweak it, you could always sort the Control Account Deficit amounts so that the entries were in order of 'importance' for those Managers that have multiple departments i.e. the largest deficits shown first!

    zeddy

  10. #9
    New Lounger
    Join Date
    Apr 2016
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Zeddy. Glad you are feeling better. I have a question about the spreadsheet that you sent. When there are multiple email addresses, it emailer tab doesn't match up the email addresses with the data tab. Do you know if there is an easy way to fix this issue? I've attached a version that has several different email addresses. Thanks.

    TomQuerry Example Issue 4.13.xlsm

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Tom

    ..I'm such a plonker!!
    The clicker was selecting the Manager Name from the alphabetically sorted [Managers] sheet.
    But this index row doesn't relate to the row on the [Data] sheet, which has the Manager's email.
    The attached file fixes this.

    If a manager has multiple Departments, we assume that the Dept. Manager has the same email address for all, so we just return the first match to get their email address. If this isn't the case, please let us know.

    I changed the demo managers names to make it easier for me to check this.

    zeddy
    Attached Files Attached Files

Posting Permissions

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