+ Reply to Thread
Results 1 to 4 of 4

Macro needed for sending emails about expiring products

  1. #1
    Registered User
    Join Date
    11-04-2019
    Location
    Augusta, GA
    MS-Off Ver
    2016
    Posts
    3

    Question Macro needed for sending emails about expiring products

    Greetings Excel Forum,

    I'm looking to upgrade a handwritten expiration date recording system to an automated excel spreadsheet that emails me when a product is close to and/or at expiration. I've got a sheet for recording the entries and calculating the dates for manager's specials and shrinking expired products.

    What I'd like to do is have an automated macro that runs every day (I assume I can set this with windows task management provided my company's macro security isn't prohibitive) to check for products that need to be put on manager's special or shrunk out. When a product does need discounting or shrinking, an email will be sent (hopefully) with all of those products on a single report instead of individual emails.

    Items needing 25% off would get highlighted yellow
    40% off orange
    Shrink, red fill with white text

    Instructions for current step would be posted in "notes" as, "Place on Manager's Special", or "Shrink".


    I've tried playing around with other codes for this, but the needs of the project don't quite fit the other macros I've found so far.

    I attempted to do it myself as well, but I don't really understand the initial parameters like " For Each Bcell In Sheet1.Range("K2", Sheet1.Range("K" & Rows.Count).End(xlUp))"

    So I thought it'd be easier if I just started from scratch with the help of someone who knows more than me.

    Thank you for your time and energy!
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro needed for sending emails about expiring products

    Hello zabrien,

    Welcome to the forum!

    You said, "I'm looking to upgrade a handwritten expiration date recording system to an automated excel spreadsheet that emails me when a product is close to and/or at expiration."

    1) Please define what you consider "close to".
    2) Will the cell coloring to be added by the macro?
    3) Do do you have any examples of what you want a finished email to look like?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    11-04-2019
    Location
    Augusta, GA
    MS-Off Ver
    2016
    Posts
    3

    Re: Macro needed for sending emails about expiring products

    Hi Leith!

    Thank you! I'm glad to be here. This place is full of useful resources!

    I've answered your questions below:

    1) Please define what you consider "close to".

    Cells in column G(25% off date) are determined by D(expiration date) minus E(serving size), minus 60 days. Any product that is 2 months within its expiration date minus the number of servings should be put on 25% special. Products within 1 month of expiration minus the number of servings would be on 45%(column H) off (I think I misspoke and said 40% earlier). Any product that is the exact number of days from expiring as it has servings needs to be shrunk out(column I).

    This would reduce the amount of products being thrown away, give the customer a chance to purchase something before they no longer have time to consume it, reduce the number of expired products on the shelf, and all while saving me time not having to search through our expiration records to see what's expiring.

    2) Will the cell coloring to be added by the macro?
    Yes, please.

    3) Do do you have any examples of what you want a finished email to look like?
    Sure! A finished email would look something like:


    Hello Vitamin/HBA Team Sprouties!

    Here is your product expiration report for today:

    Currently, there are (#_of_products_expired_on_shelf) products expired
    (#_of products_to_be_shrunk) for shrink
    (#_25%off_specials) to be put on manager's special at 25% off and,
    (#45%off_specials) to be placed at 45% off.

    Products are listed below:

    (Section | Determined by Sheet Name): (Brand) (Product) (UPC) | (Exp Date) | (Quantity) | (Notes | 25% off /45% off /Shrink)
    (Section | Determined by Sheet Name): (Brand) (Product) (UPC) | (Exp Date) | (Quantity) | (Notes | 25% off /45% off /Shrink)
    (Section | Determined by Sheet Name): (Brand) (Product) (UPC) | (Exp Date) | (Quantity) | (Notes | 25% off /45% off /Shrink)
    (Section | Determined by Sheet Name): (Brand) (Product) (UPC) | (Exp Date) | (Quantity) | (Notes | 25% off /45% off /Shrink)
    (Section | Determined by Sheet Name): (Brand) (Product) (UPC) | (Exp Date) | (Quantity) | (Notes | 25% off /45% off /Shrink)
    ...

    Please handle applicable product appropriately or print this report to give to your Manager or Assistant Manager.

    Thank you!

    (End email)

    Is it possible to have this set up to send out emails as something new becomes necessary for special or shrink? Otherwise, we'll be getting emails every day of products that are already on special for 2 months until they go to shrink. If not, it's not a big deal, we'll just print the emailed report and cross reference it for changes manually.

    The exact formatting of the email doesn't matter as much as just having all the information and the note that applies.

    Also, there are several different sections in this department. Each section would define the Sheet Name and would have to be sent in the email so that the person reading it would know where in the department to find the product. If it helps, these are the sections listed below:

    Amino Acids
    Sports Nutrition
    Protein Powders
    Nutrition Bars
    Electrolytes
    Pet
    First Aid
    Aloe/Energy
    Herbs
    Brain/Eyes/Antioxidants
    RENT
    Seasonal Wellness
    Emotional Wellness
    Joint Health
    Minerals
    Greens
    Functional Foods
    Diet/Weight
    Cleanse/Fiber
    Liver/Detox
    Digestion
    Heart Health
    Blood Sugar
    Children's Health
    Men's Health
    Women's Health
    Calcium/Bone
    Multivitamins
    Vitamin C
    Vitamin B
    Vitamin ADEK
    Plant/Fish Oils/Probiotics
    Probiotic Cooler
    Lozenge Slat Walls





    Thank you again for taking the time to look at this!

    Please let me know if there is anything else needed.

    -Zabrien
    Last edited by zabrien; 11-05-2019 at 01:17 PM.

  4. #4
    Registered User
    Join Date
    11-04-2019
    Location
    Augusta, GA
    MS-Off Ver
    2016
    Posts
    3

    Re: Macro needed for sending emails about expiring products

    I'm guessing this isn't possible or just too much to ask for

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with Macro - sending an email based on cell expiring
    By boomTO in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-22-2015, 07:55 PM
  2. Macro not Auto-Sending Emails
    By remadison3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2014, 08:32 PM
  3. Sending Emails via Macro
    By jnoble3 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2013, 03:34 PM
  4. Sending emails through macro
    By nbhatia4u in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-12-2013, 08:01 AM
  5. [SOLVED] HELP! Macro not sending emails
    By g1987 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2012, 12:25 PM
  6. excel macro for sending emails
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 12-11-2009, 08:50 AM
  7. Sending macro emails using excel: Send emails with their passwords.
    By loveisblind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2009, 03:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1