VBA for email with body to look multiple columns for expiry dates

hi
I am trying to put together VBA code for creating weekly email sent to suppliers about expired certificates in different columns, I want to have a email body with expired certificate names and asking for new certificates. (excel attached)

Please help.
Rag.

Where will the email for the supplier be found?

emails will be in same sheet last column S

Try this solution.

Please Login or Register  to view this content.

Originally Posted by maniacb

Try this solution.

Please Login or Register  to view this content.

Thank you I will try and keep you informed.

Thanks alot Maniacb.

now I learnt it can be done, can you help me with few more changes please.

1.If this sheet is part of number of sheets in a work book and sheet name is products, can I replace sheet1 in VBA code to products?
2.if I have to filter by supplier and send one email for many requests, how do I do that?
3. do I have to add all contact details to the data base of can I pick from a next sheet?
4. the code is also adding request for not expired certificates of the product, can it be filtered and only expired certificates are highlighted? in my case I put data validation and all expired certificates turn red
5.can I click on a particular supplier name to make an email with only their products listed.

Your time and help is highly appreciated.
kind regards
Rag

1.The code can be updated to incorporate renaming sheet1 to Products.
2.The code is set up to send common supplier emails already. But it uses the email to associate common suppliers. The code assumes each supplier has a unique email.
3. You can use a separate sheet for contact information, but we need to know what you'll be using to associate them together. Are you expecting to use the supplier name to then associate the email from the contact sheet ? Email is not listed in the contact sheet.
4. What logic are you using to identify the expired dates, so that that code can be incorporated into the code.
5. This will require a separate email code module. Is column A, "DT Raw code", the product you want to email?

Originally Posted by maniacb

1.The code can be updated to incorporate renaming sheet1 to Products.
2.The code is set up to send common supplier emails already. But it uses the email to associate common suppliers. The code assumes each supplier has a unique email.
3. You can use a separate sheet for contact information, but we need to know what you'll be using to associate them together. Are you expecting to use the supplier name to then associate the email from the contact sheet ? Email is not listed in the contact sheet.
4. What logic are you using to identify the expired dates, so that that code can be incorporated into the code.
5. This will require a separate email code module. Is column A, "DT Raw code", the product you want to email?

Hi Maniacb

Thanks for your time answering back.
please regard the attached-

• I modified quote to suit me and not sure why its not working.
• I have added contact names and emails addressess onto same sheet,
• in column AP todays date is added to validate the expiry dates,
• so the Idea is to send an email to the suppliers if their certificates from Column J to R (it may be 1 or more certificates expired during that time)are expired requesting for latest ones.
• it would be better to use the email to associate the common supplier.
• is it possible to send email to certain emails by selection filtering rather to every one if required?
  

Thank you soo much for helping me in building the foundation and learn VBA.

Regards
Rag.

Here is a new code that will send an email per line if there is a past due certificate. See how this works for you.

Please Login or Register  to view this content.

cheers will keep you posted.

Hi Maniacb
I tried and it shows the error to start with at " Sub Email_filter2() " and "RangetoHTML"

please look at my last excel attachement.

Regards
Rag.

You need to make sure the RangeToHtml module is also included in the module:

Please Login or Register  to view this content.

hi Maniacb

I added the rangetohtml module and now facing another glitch unable to fix myself.

please regard attached pictures.

regards
Rag.

SorryManiacb, ignore previous email, I might have done a mistake at my end. I will keep you updated.

Hi Maniab

It worked once with enlarged rows and then stopped working with errors, please regard attached.

Regards
Rag.

Here is an update to that line. I also use column AS to assign the email sent date, let us know how this works for you.

Please Login or Register  to view this content.

Hi Maniacb

I tried it again and found below

1 when opened excel with unfiltered rows, code works for the first email address and the email format covers the whole email body with ablong cells, and the excel freezes for very long time until I close task,
2 when filtered by supplier (C) to corresponding common email address, the code get stuck by creating that first emial in excel in saperate sheet and hiding the non-expired cells,
3 puts a date in AS and moves to next supplier with many products and excel gets stuck for ong time,
3 close and reopen to filter the supplier with common email address creates report with only one produt (error 3.4) then excel gets stuck for long time

P.S.I am using excel 365

regards
Rag.

Try this updated code. It has been updated to support a workbook with multiple worksheets.

Please Login or Register  to view this content.