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.

Due dates emails in excel VBA help

Discussion in 'Business Applications' started by natish8, Jan 24, 2018.

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

    natish8 Thread Starter

    Joined:
    Jan 24, 2018
    Messages:
    11
    Hello,
    What a great help this forum is.
    I know nothing at all about VBA, yet managed to get a macro in excel that allows me to send the "due date" emails that works almost perfectly for my needs. I based my macro on the code found here https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/
    But i'm still missing one function in this macro for it to be perfect for my needs. I want it to send only one email to the "area manager" specifying all the items (serial number and location) that follow my due-date rule (instead of one email per item). I want it to send a list of items that are due this month for example.
    Is it possible?
     

    Attached Files:

  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    Yes of course it is possible.
    Would you like to try and do it youself?
    It might be easier with a new set of VBA rather than adding it to your current one.
    But basically all you have to do is when looping through the records add each one to the "ebody", you can seperate them using chr(13) to put them one seperate lines.
    like this
    ebody2 = ebody2 & chr(13) & (your field data goes here.)

    So you could add a "ebody2" string variable to your code and add them to that and then when you have finished sending all the others send a last one using ebody2 to the manager.
    Or you could copy your current code and add the items to the ebody and move the emailing part to outside the Loop.
     
  3. natish8

    natish8 Thread Starter

    Joined:
    Jan 24, 2018
    Messages:
    11
    Thanks for your help. I'm afraid it's a syntax issue here. I can do it in Java no problem, but i met VBA only yesterday and it's not that straightforward to me. Is there any documentation site for VBA i can refer to?
    How do i form a "while" loop or make a "list" like i do in java?
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    The loop is already in the code, it starts with

    For i = 2 To lRow

    and ends with

    Next i

    I can do it for you if you like, I just thought that as you managed to get it working for your Worksheet you might like to try it.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    Your worksheet does not seem to have any code?
     
  6. natish8

    natish8 Thread Starter

    Joined:
    Jan 24, 2018
    Messages:
    11
    The loop in my code now, checks the date and "sent" status. I think i need another loop to check if the apply to a number of lines connected to the same person (hope that makes sense). I'm not sure I can write it completely by myself.
    Thanks so much for your help!!!
     
  7. natish8

    natish8 Thread Starter

    Joined:
    Jan 24, 2018
    Messages:
    11
    No, my table looks a little different, and i have some non english fields, so i didn't include that. The file i posted was for the example of how i want my table to function. I can upload the code i have now, but i'm not sure it will be beneficial.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    To be clear do you want to send
    1 email to the manager instead of multiple emails
    or 1 email to the manager as well multiple emails
     
  9. natish8

    natish8 Thread Starter

    Joined:
    Jan 24, 2018
    Messages:
    11
    I want to send one email to manager but i want all the items that follow the due date rule to be included (in other words, i dont want to spam the manager with multiple emails, i want the manager to get all the items due this month in one mail)
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    OK.
    I am working on some Access VBA at the moment, but I will do this one when I have finished.
     
  11. natish8

    natish8 Thread Starter

    Joined:
    Jan 24, 2018
    Messages:
    11
    Thank you so much!
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    I am looking at the code for your workbook, but your workbook does not have much data to send ie only the location and the Serial Number.
    Is that sufficient, if not can add any extra data you need?
     
  13. natish8

    natish8 Thread Starter

    Joined:
    Jan 24, 2018
    Messages:
    11
    Thank you very much. I thinks that is all. serial number, location, date and manager.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    As I cannot reproduce the characters on your worksheet name, which is needed in the VBA code I have chnaged it to Tempname, you can change it and the VBA back to what it should be when I post it.
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    What is your "that follow my due-date rule"?

    Will the manager names always be in order or will they be scattered down the column?

    You also do not say what the "Subject" of the email is.
     
  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/1203537

  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