Auto Send Email when text value changes from web query

I have this spreadsheet which I am updating on a daily basis. The Formula in 'J2' updates based on the arrival time 'C2' to either "early" or "late" or "on time". But Is there a way to send an email to multiple sources when the value say changes to "late"?.The Whole Row needs to be copied and sent out in an attachment.
I have seen this macro around by Ron De Bruin but I cant seem to customize this to my sheet.

Hi sonny.thind

Please include in the workbook the code you've tried. We'll look at it and see if we can make it work.

Hi sonny.thind

Try the code in the attached. Works when one line is updated. Don't know what will happen if more that one line gets updated...try it and find out...let me know.

Hi sonny.thind

This is relatively simple (I think )

what if I need to have the copied row (which is "late") also copied onto another sheet

What I'm not sure of is this

add it to a template so that I can then copy the whole template to draft a proper email

What's "a proper email" look like. I'm assuming you don't want 50 emails going out if 50 items change to "Late". I'd assume you want one email that includes all 50 items. Please confirm if my assumptions are correct (or not).

Additionally you have 17 columns of data. So what's the "proper email" going to look like? Getting 17 columns of data into the body of an email may be problematic. It can be done but will it be readable?

Mock up a worksheet that demonstrates what you expect to see in the body of the email. We'll see if we can make it happen. An attachment may be more appropriate.

Hi
Couple of issues I am having with this code (please bear with me )

1) I am using my work Microsoft Exchange server, would that cause any issues?. we have a lot of Firewalls so I would prefer to do this through regular sources
2) Also I have attached the Email Template -I require only certain columns be sent instead of the entire row. I also need the copied Row onto a second sheet to generate a report.
3) What if I need to change the statuses i.e change from "Late" and Add maybe some "Early" and some "On Time"

Say I am using the same Sheet attached ,Could you help modify this with the new parameters I mentioned?

thanks a bunch

Hi sonny.thind

Don't know to whom you're responding

Couple of issues I am having with this code (please bear with me )

You've got a couple of code versions posted...to which code are you referring?

Hi sonny.thind

In the attached is code that completes an Email template as you described. The code also copies the complete row to Sheet4.

You will have three attachments:
BOOks3.3.xlsm
Email.JPG
Delivery Notification.txt

The Email.JPG is a picture of the Email that's generated. The Delivery Notification.txt file is the actual Outlook Template used in the Email. You'll need to rename the file to Delivery Notification.oft.

Place the workbook and the Outlook Template in the SAME folder.

I don't know about Microsoft Exchange servers so I can't help you with the question.

You'll need to expand on this

What if I need to change the statuses i.e change from "Late" and Add maybe some "Early" and some "On Time"

...what is it you wish to do?

Hi sonny.thind

Sorry 'but that. See attached

Hi sonny.thind

Don't know.

Does the file exist in the path described in the error message?

Double click on the file itself "Delivery Notification.oft" ...does it open?

Add these lines of code here and set a break point as described

Please Login or Register  to view this content.

Look at the Immediate Window...it should look like this

Please Login or Register  to view this content.

Has the code executed to the Break Point? Is this the correct path

Please Login or Register  to view this content.

If all is well to this point step through the rest of the code. Let me know what happens.

PS: set a breakpoinh here also

Please Login or Register  to view this content.

Hi sonny.thind

Set a breakpoint here also

Please Login or Register  to view this content.

Another thing that came to mind...did you

The Delivery Notification.txt file is the actual Outlook Template used in the Email. You'll need to rename the file to Delivery Notification.oft.

Hi sonny.thind

Well I'm also unable to open the file...get the same error message you're getting. It must be corrupted.



We can do one of several thing (or all three):

1. The Forum won't allow me to attach an .oft file so I'm going to reattach the Delivery Notification.txt which you'll need to rename Delivery Notification.oft.
2. You can re-download the original Delivery Notification.txt which you'll need to rename Delivery Notification.oft.
3. I can email you a copy of the original Delivery Notification.oft.

I tried number 2 successfully. If you opt for number 3, PM me with your email address. Let me know how it goes.

Hi John,

thanks a lot ,it works now , so it must have had to do with the file.

but another problem crops up. I am getting a run time error whenever the email is drafted..

please see attached..

Hi Sonny

Please comment out this line of code and see if the procedure completes

Please Login or Register  to view this content.

The code is in

Please Login or Register  to view this content.

Let me know what happens.

Hi Sonny

There's nothing in the code that clears Sheet4...is there NOTHING in Sheet4?

Hi Sonny

Are the Emails displaying properly...one Email per changed row??

Hi Sonny

Been sittin' here trying to understand this

keeps the latest row that is "late" so it only has 1 order at all times even when there are multiple rows reported 'late' .

Trying to understand 'cause this doesn't happen in my environment...it keeps all updated records in Sheet4 so as to

Please Login or Register  to view this content.

As I see it, there's only two things that can be causing this. I'm going to keep issue 2 until later...'cause I don't have the current knowledge to fix it. I'm going to deal with issue 1 and hopefully that'll resolve the problem.

Issue 1:
The current code assumes that Columns A and B in Sheet1 are populated . In your sample file they were not...so, in my testing, I populated them. I assumed, in the "Real World", they'd be populated...perhaps a bad assumption...perhaps not. Regardless...we're going to change the code such that the code will look at a Column that will ALWAYS be populated...Column C. This comes into play here

Please Login or Register  to view this content.

I'd like you to run the code in the attached...see if it resolves the current issue...keep me posted.