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.

Excel macro

Discussion in 'Business Applications' started by jenrowlands, Oct 14, 2008.

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

    jenrowlands Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    2
    hi.

    I have an excel sheet with many rows and columns. the headings for the columns contain an expiry date, who the agreement is with (hyperlinked to a pdf), type of agreement, time until expiry and type (7 different types). I need a macro that will -
    search each row and look for when 'time until expiry' = "less that 2 months" and 'type' = (one of the seven)
    then send an email to a person whose email is in a different cell off to one side of the worksheet.

    After it has done this, i need to run another macro which checks the same thing but with a different type (and email address)

    Once all this is done, i need a macro to do all of the above but with "7 days" as the 'time until expiry'.

    I also need a macro to run and search the rows for "unsigned" in the comments and again go through each type and send an email to the respective person.

    Can this be done?!?!? It would be great if the macro could run automatically when the workbook is open.

    Thanks so much in advance!!!
     
  2. iltos

    iltos

    Joined:
    Jun 13, 2004
    Messages:
    18,287
    welcome to TSG, jenrowlands :)
    while this is indeed a "do it yourself" problem, we have a forum much better suited to a business application problem....it's called "business applications :cool:

    i'm moving your thread there.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    If you could post a sample of your Excel file - delete any confidential data but please, replace it with some fake data if you do - that would make it much easier for us to see what you want done (I could ask a lot of questions, e.g. how are your expiry dates formatted and are they listed by days, weeks, etc?) but the file would explain much of that.
    If you could also put in your file what you want the finished product to look like.
    I have helped with (but can't do myself) each of the various tasks you ask about, so iltos is correct in saying it can be done.
     
  4. jenrowlands

    jenrowlands Thread Starter

    Joined:
    Oct 14, 2008
    Messages:
    2
    Hi
    Thanks so much for replying!!!
    I have attached a spreadsheet like the one i have but with different data. In the real one, the headings are a little different (but if you give me the code using cell references - like [a2] i should be able to modify that and the headings. Also, when you look at the sheet, up the top you'll see in L & M 1-3 the types with emails beside them. In the real file, the same thing has been done but those cells are locked so they always appear even when you scroll through the hunders of records. (In other words - the headings for all the data should still work even though they start in row 5). For the 'time until expiry' I have made a code that automatically places either nothing, expired, expires in 2 months or expires in 7 days (in the sheet ive uploaded ive just written this info in rather than using the formula to do it).
    What is need is an email to be sent out firstly when this box says 'expires in 2 months' and then again when it says 'expires in 7 days' and then again when it says 'expired'. I'm thinking I'll need 3 cells/check boxes to do this so that it doesnt send over and over again???
    The emails need to go to a different person depending on the type. So, if in one row there is 'outdated' & 'CO', an email will go to someone. If in another row there is 'outdated' and 'MA' the email will go to another person. The relative emails are written there beside the types but if its easier, they can be written into the code. I thought the advantage of having them on the sheet is if they change, all I have to do it change the email in the cell rather than playing around with the code.
    I also need an email to be sent, like above, if there is 'unsigned' in the comments. Again will i need another box saying either sent/unsent for this??? The difficulty I can see in this is sometimes in the comment column there is 'unsigned, deactivated'. Would excel recognise the 'unsigned' even though it is combined with other words???.
    I would really like the macro to run every time the workbook is opened up - does that message have to pop every time warning that there is an email being sent???
    With the emails -
    For the 2 months and 7 day expiry warning -
    the subject can be constant and I need the body so be like "Hi. The event with 'name' about the 'thing' 'date until expiry' on the 'expiry date.

    For the expired
    "Hi. The event with 'name' about the 'thing' is 'date until expired'.

    For the unsigned
    "Hi. The event with 'name' about the 'thing' is unsigned.

    I'm so sorry this is so long!!!! Please ask as many questions as you need!!!!!! THANKS SO SO SO SO SO MUCH
     

    Attached Files:

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/759060

  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