Send Email based on Worksheet Formula Results

Hi,

I have a report (sample attached) that I extract from DB, which shows projects that appears to have errors. Errors are indicated by formula result "1" (Columns H-K), and color coded red. I have specific outlook templates for each error that I send out to Managers, where I have to add/paste to the body of the template a range on the worksheet before sending it out. This post seem to be somewhat similar to my problem, It's here: http://www.excelforum.com/excel-prog...excel-vba.html

What I want to do is:
1. Use the template I have on my outlook, specific for each error. E.g, for "Error on Start Date" I call "Temp for Error on Start Date", etc.

2. Copy the range corresponding to the error and paste it to the body of the template, a few lines above my signature. E.g. Line 155 has Error on Start Date, I want to copy Row 155, from Columns A-G, always including the header and incorporate it on the body of the email.

3. Send out the email to the Manager, which name's appear on Column G of the worksheet. However, the name of the Mgr on that cell is on format Firstname(space)Lastname only, and I need it to be on the format of [email protected] and put that on the To: field of the template. This is a bit tricky because there are instances that there is no Mgr name listed (blank), or just have TBD on it, or not in the format of Firstname(space)Lastname. I need to add additional column or some kind of indicator that will tell me which row/items didnt send out an email because they have this values, so I can look at it. At the same time, I need to have an indicator for those rows/items that have successfully sent emails, so when i re-run the code, it will not send another email again.

I hope you could help me.

Thanks,
Sarah

Hi Sarah

If permitted, it would be helpful if you could upload your template(s) so I can see what they look like. You'll have to zip them to do so.

Hi Sarah

I was looking for a copy of the actual template file (the .oft file). You appear to have posted an email message??

While we're on the subject...what if Audry Hepburn has two items in Column H with 1's...should they appear on the same email or separate emails?

I assume 1's eventually become 0's. Will a 0 ever become a 1?

One other point...there's a rather direct way to accomplish this without using Templates...I'll demonstrate that in a workbook for you. If you REALLY want to use the Templates, I'm certain (I think) that we can make that happen.

Gotta warn ya...don't know how to distinguish between "Audry Hepburn" and "Hepburn Audry". Can distinguish between "Audry Hepburn" and "Hepburn, Audry"

I've been on the road for 8 hours today...involved Family duty...that accounts for the random thoughts I had while driving. I'll get back into this tomorrow.

I was looking for a copy of the actual template file (the .oft file). You appear to have posted an email message??

What I attached is actually a copy of the actual template, just edited it a bit as it's not permitted to post/send it. What I did was copy the content into a new email message and saved it. Anyways, I attached the .oft file, but it's just the same, only different file extension.

While we're on the subject...what if Audry Hepburn has two items in Column H with 1's...should they appear on the same email or separate emails?

If Audry Hepburn appears more than once in the same column error, example Column H. Ideal scenario would be to send the email containing all the rows/PIDs concerned. That would be awesome if we can do that. My only concern is if there are say 5 errors, how would that appear on the subject line as it follows the format of "PID - Project name"? If we can apply exception that for more than 1 error / Mgr Name on the same column error, we follow the format of "PID only" on the subject line, that will do it. Again, that is the ideal scenario. If this is not doable, I can live by sending 1 error / Mgr Name, regardless how many times Mgr Name appears on the same column error.

I assume 1's eventually become 0's. Will a 0 ever become a 1?

Yes on both questions.

1 will eventually become 0 when the correct value is entered to the DB. Correct value will be coming from the email reply of the Mgr. When I enter that on DB and do a refresh on the file 1 will become 0.

0 will become 1 if for example on the next run/refresh of the report, a PID doesn't meet the condition set, it will appear as error. And will need to send email again to the Mgr.

One other point...there's a rather direct way to accomplish this without using Templates...I'll demonstrate that in a workbook for you. If you REALLY want to use the Templates, I'm certain (I think) that we can make that happen.

I am open with suggestions. The reason why I want to use email template is so I can check the content first (display) prior sending it.

Gotta warn ya...don't know how to distinguish between "Audry Hepburn" and "Hepburn Audry". Can distinguish between "Audry Hepburn" and "Hepburn, Audry"

Yeah, I figured you'll say that. But I am assuming (I could be wrong) that if the email address on outlook is incorrect, it should send me an "undeliverable message". Another thing I want to mention again is that there are instances that the value of Column G (Mgr Name) is sometimes TBD, or Blank, or sometimes contain invalid characters, "&", "/". I need an error to warn me bout that so I can check which items these are. So I think the format that is valid that we can follow is "abc(space)xyz". If it's not that it should warn me as error.

I've been on the road for 8 hours today...involved Family duty...that accounts for the random thoughts I had while driving. I'll get back into this tomorrow.


Thanks for looking into this. Really appreciate it.

Hi Sarah

The attached is going to take some work yet but I think your issues are mostly surrounded...this is still outstanding

or sometimes contain invalid characters, "&", "/".

This can be handled if I know all the invalid characters...wanted to see if this initial version is approaching what you need.

I also need to get better definition of this

I need to add additional column or some kind of indicator that will tell me which row/items didn't send out an email because they have this values, so I can look at it. At the same time, I need to have an indicator for those rows/items that have successfully sent emails, so when i re-run the code, it will not send another email again.

Yes, you are correct on this

if the email address on outlook is incorrect, it should send me an "undeliverable message".

This attachment does NOT use templates. The error type changes based on the column header of the column we're looking at. If that's the only thing that changes in the templates, I'd suggest we don't need them.

You CAN do this with the attached

The reason why I want to use email template is so I can check the content first (display) prior sending it.

We need to explore this further:

I assume 1's eventually become 0's. Will a 0 ever become a 1?

Yes on both questions.

1 will eventually become 0 when the correct value is entered to the DB. Correct value will be coming from the email reply of the Mgr. When I enter that on DB and do a refresh on the file 1 will become 0.

0 will become 1 if for example on the next run/refresh of the report, a PID doesn't meet the condition set, it will appear as error. And will need to send email again to the Mgr.

That's all I have for now. I'll continue to work at it but let me have your feedback. This is an interesting exercise...hopefully we can make it work for you.

Hi Sarah

Glad you like it. I'm getting house guests for the Holiday (12 of em) and they start arriving this afternoon and will be in-house until Friday the 8th.

So, I'll look at your comments as I can...for a while, can't guarantee I'll be sober enough to work through them .

Get back to you.

Hi Sarah

Thanks for the Holiday wishes.

Regarding this

Column K is only to be able to filter all the 1's on Columns H, I, & J at the same time

To make sure I understand and if I read the Column K formula correctly, it's value will be "1" if there's a "1" in any of Columns H, or I or J. Is my understanding correct?

Regarding this

How do I select the range that I want to copy and paste on the body of the message if they get separated? E.g. a column is added in between the same selection that I want, how do I choose that

Are you suggesting you MAY or MAY NOT want these newly inserted items in the range? Currently we're using these column headers

PID Project Name Start Date Finish Date Comp_Stat Status Mgr Name

in the body of the email. Would you be adding to these headers or will ONLY these headers be included?

I need to be clear on this issue. It impacts not only the Email Body. When you add or delete columns, it affects other parts of the code. So, I'll need to think on that. Please clear this up for me.

Regarding this

Please have all non-alphabet letters (numbers and special characters) considered as invalid

Please give me a list (one of us has to think about this) so it may as well be you.

Regarding this

For those that are marked as Yes, I want that when I run the code again, it will not send another email and cause duplicate to the recipient

Yes, I'd thought of that...wanted you to look at the project...we'll add bells and whistles.

Regarding this

Each error has different message body

Please show me the different messages.

Hi Sarah

Yes, my children and grand children gave me some "break" time these last couple of days so I've had some time to look at your issues.

I understand this

To make sure I understand and if I read the Column K formula correctly, it's value will be "1" if there's a "1" in any of Columns H, or I or J. Is my understanding correct?

I understand this and believe, based on my testing, I've got it handled

ONLY these headers will be included. My worry is, their position on the spreadsheet may change. New columns can be inserted between them, and I don't need these new inserted columns

This has been handled

For those that are marked as Yes, I want that when I run the code again, it will not send another email and cause duplicate to the recipient

Still dealing with this...may need some help from my friend Leith Ross...seems like a "Regular Expressions" approach may be appropriate

The only values accepted should be A-Z, and a-z. Anything not on that list is not valid

I'll get back to you.

Hi Sarah



Try the attached. I believe all the issues are addressed.

• You can insert columns as you wish, the range will be created using ONLY the columns as previously described.
• Emails will not be sent a second time (based on "yes" in the Email Sent Column).
• Only values A-Z, a-z and one " " (space) will be allowed for valid names, otherwise the email address will be marked as "Invalid Name" and that item will be skipped. This approach is probably not bullet proof but should catch the major portion of your data errors. The rest you'll get the delivery error due to a bad email address.
• You can define your message based on the error type.

Play with it...let me know of issues.

Hi John,

Okay...I played the code with the actual data and I encounter just one more issue. This is in addition to my last post.

The Invalid Email doesn't work on the following names...below is how each is written on each cell.

Sean Connery / Paul Newman
Cary (Jerry) Grant
Bryan Downing, Shannon Mason-Jenkins
Vivienne Leigh & Bette Davis
Myrna Loy / Howard Keel*

It generates an email, puts the first name (leftmost) to the Greeting field, and the To field is blank. The EmailSent column is set to YES. Please try to put these values and run the code to see. I also get the below error:

Run-time error '1004':
AutoFilter method of Range class failed

and when I debug it points to the line that contains:
ActiveSheet.Columns(emCol).AutoFilter Field:=1


The Invalid Email works fine with the following entries:

West, Mae
TBD
<blank entries>

That's all the issues that I could find. Otherwise it's working perfectly.

Regards,
Sarah

Hi Sarah

Try the attached. I believe these issues are resolved.

1. The font and font size of the greeting (Hi <FirstName>) is different from the body of the email. It's Times New Roman, size 12 while the body of the email follows what's my default setting on outlook. Can it be changed to follow the default as well? And make the <FirstName> bold. My default setting is Calibri, size 11.

I need to be added a function that when I click "Refresh" button, it clears out all that has "YES" on the EmailSent column

The Invalid Email doesn't work on the following names...below is how each is written on each cell.

I'm unable to duplicate this issue

Run-time error '1004':
AutoFilter method of Range class failed

and when I debug it points to the line that contains:
ActiveSheet.Columns(emCol).AutoFilter Field:=1

See if you get that error on the attached (I don't).

Hi Sarah

Glad it works for you...it's been an interesting exercise...I too learned a couple things.
Best to you.