Sending attachment in PDF with conditional color coding

Hi again...

I learned my lesson in the hard way, NO CROSS POSTING, it is clear and I got it, but please help.

Well, I have a problem. I am attaching herewith my excel sheet which contains "Materials Procurement Status report (E2)". I made some VBA macros enabling me to do the following:-
1- Status Check, the aim is to compare (ROJ)date with Today's date, and if the difference between (ROJ) and TODAY's date <14, mark the related Cell by red and under remarks column to type "Send Reminder".
In sometimes, ROJ date passed already, I need under remarks to show the date already passed.

2- Rows have not any ROJ date, should not be coloured and NO "Send Reminder".
3- Send email reminder module, the aim is after checking the sheet(in this case E2), colouring everything, know to whom this item is related,etc.. I need to save this sheet first in pdf format, and attach it to the email (listed in S column) with the item has been identified by "Send Reminder".
Can you please help me with that ?!
Regards...
Ahmad

You can cross-post. The main consideration when doing so though is to include links in each of the other forums where you post. Sounds like you were given a link that explained that and the other considerations. I do suggest limiting cross-posts threads. Just explain what you need in the most simple way possible.

It looks like a nicely formatted file. It looks like you have done all of (1) and (2) and most of (3).


This not that complicated. Let me see if I get what you wanted but I will list it in my order of solution:
a. Assume that autofilter is active but no filters in effect.
b. Set autofilter for STATUS column to only list values of Send Reminder.
c. Get the list of emails to send to in the EMAIL column. This is the biggest issue. If field is blank, skip and remove duplicates from full list. Would there be more than one email in some cells? If so, how would they be delimited: commas, vbLF (new line), semi-colons, etc.?
d. Hide any columns like STATUS, EMAIL, etc. prior to making PDF file?
e. Send one email with the pdf file attached. I would use the BCC field rather than the TO field unless you want all to see who got the email.

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Thanks!

Originally Posted by Kenneth Hobson

You can cross-post. The main consideration when doing so though is to include links in each of the other forums where you post. Sounds like you were given a link that explained that and the other considerations. I do suggest limiting cross-posts threads. Just explain what you need in the most simple way possible.

It looks like a nicely formatted file. It looks like you have done all of (1) and (2) and most of (3).


This not that complicated. Let me see if I get what you wanted but I will list it in my order of solution:
a. Assume that auto filter is active but no filters in effect.
b. Set auto filter for STATUS column to only list values of Send Reminder.
c. Get the list of emails to send to in the EMAIL column. This is the biggest issue. If the field is blank, skip and remove duplicates from a full list. Would there be more than one email in some cells? If so, how would they be delimited: commas, vbLF (new line), semi-colons, etc.?
d. Hide any columns like STATUS, EMAIL, etc. prior to making PDF file?
e. Send one email with the pdf file attached. I would use the BCC field rather than the TO field unless you want all to see who got the email.

Well, Thank you very much for passing by. Nice one hs, you don't know how many nights I spent to achieve it that's why I said I learned my lesson in a hard way. appreciate your comments
Regarding your questions:-
a-correct.
b-correct.
c-Yes there will be an email list because this will be distributed to all discipline manager(mechanical, electrical, civil,communication...etc)., and by using semi column as a separator
d- Yes, I need to hide those columns before saving the report into pdf and attach it. but I need to add a date to existing file name, I mean (Material Procurement Status report+date of creating the report).
e- Yes, if you have noticed, I used BCC and Yes I don't want to use TO field.

but make sure please of one thing here:
if (ROJ)-TODAY()<14 I need to show in status "Send Reminder, 14 DAYS are remaining" flashing with yellow.
if (ROJ)-TODAY()>14, Status message"send a reminder, 14 DAYS are remaining!" flashing in yellow colour.
if (ROJ)-TODAY()>30, status message"Recommend placing the order", flashing in green.
if (ROJ)-TODAY()<7 status message "Send reminder, 7 DAYS are remaining!" flashing red
if (ROJ)-TODAY() already passed, I mean (negative value)...status field "your material already due by (...)DAYS, late in placing Order".

appreciate your consideration at your earliest, you know, these sheets are updated daily, and I need to have it ready

Hi, AliGW...

thanks for passing by, well if you may noticed Mr. Keneeth post above, I did most of thing, but to be honest with you, everytime I running the dubugger, error message indicate to the row of "myAttachment.Add".
anyway, next time, I will take this into consideration.
if you have any suggestion, please share and will comply accordingly.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Thank you for amending your thread title.

@Kenneth
Sir, Thank you very much for your efforts.
However, I will appreciate if you share the workbook you've made applying above-mentioned macros, because whenever i run, debugger gave error "Set c1 = CreateObject("System.Collections.ArrayList")
Set c2 = CreateObject("System.Collections.ArrayList") and I don't have a clue.

In addition, ignoring flashing thing, but I need still to have the colour index changed. I mean:-

if (ROJ)-TODAY()<14 I need to show in status "Send Reminder, 14 DAYS are remaining" WITHOUT flashing, but color it in yellow.
if (ROJ)-TODAY()>14, Status message"send a reminder, 14 DAYS are remaining!" WITHOUT flashing, but color it in Green.
if (ROJ)-TODAY()>30, status message"Recommend placing the order",DO NOTHING, maintain white.
if (ROJ)-TODAY()<7 status message "Send reminder, 7 DAYS are remaining!" WITHOUT flashing, but color it in RED.
if (ROJ)-TODAY() already passed, I mean (negative value)...status field "your material already due by (...)DAYS, late in placing Order".