Sending email whenever a cell is changed is MS Excel 2010

[shaeto]

Hi Everybody,

I've just started to learn about VBA and still struggling to find my way out.
I would like to know if there is a way to send an email whenever the value of a cell or range of cells is changed in MS Excel 2010.
I currently have an excel workbook (please see attached) with dates in different rows.
There are a certain amount of information there in other cells relative to a planning.

Say when cell Z5 is changed to 2 and a 'Y' is placed in cell C5 then an email is opened, containing the information as below:

sample email.jpg

The same thing must happen whenever any cell under the dates are changed to another value
In sheet 2 of the workbook, I've defined a value corresponding to each one of the letters and number found in sheet 1.

Maybe my approach is wrong, any feedback and comments are most welcome.

Thank you beforehand for your advise and help.

Kind regards,

Shameem

[GaidenFocus]

Use this to create a new email

Right click Sheet1 tab at the bottom and hit view code

Please Login or Register  to view this content.

Then in a new Module put the following code

Please Login or Register  to view this content.

[GaidenFocus]

In the body you can put in the message you want. To put the next set of text on a new line use vbNewLine

example:

Please Login or Register  to view this content.

would come out to:

Hey, I am sending an E-Mail.
So read it.

[Solus Rankin]

I think they would like to send dates from the calendar set up on his spreadsheet.

Also, whenever C5 is changed to yes, the other ranges don't have to be "2". They will differ and change the body of the email.

I don't see an attachment in your picture of the email. Are you wanting to attach them spreadsheet to the email?

Would it be okay to use a dropdown validation menu in column C instead of typing Y? It may reduce the number of emails sent accidentally.

[Solus Rankin]

Will all calendars be set up with 2 months stacked on top of each other?

[shaeto]

Thanks for your feedback and comments GaidenFocus.
I shall try and revert.

Cheers

Shameem

[shaeto]

Hi XeRo Solus,

Thanks a lot for your post.
I'm attaching the picture here.
sample email.jpg
Actually you are right about having a dropdown validation menu.
I shall try this one too.

Thanks a lot

Cheers

Shameem

[shaeto]

Hello XeRo Solus,

You are right about the calendar.
I will actually have all the months of the year in the worksheet but i'm not sending the whole worksheet by email, only the value found in the cell whenever same is changed.

Thanks

Shameem

[Solus Rankin]

The only problem I foresee, if you use validation list comboboxes in column C, it will not trigger the worksheet_change event to fire the macro. You would have to use a button or someone other method.

I'm not sure this is a setback though. Sending emails based on a worksheet change event has the potential to send a bunch of unnecessary emails.

[Solus Rankin]

I was working on a solution to the body of your email but I haven't found a clear path. Since you're dates are a combination of two different cells with no way to reconcile what cell is changing with the month its changing in, its going to be difficult. To get all the information you'd like to into the email you might have to change the format of your calendar.

[shaeto]

Thanks a lot XeRo Solus,
I really appreciate your comments and feedback.
I was thinking same thing about the calendar format too.
I am giving it a thought and hope to come out wuth something.
Please do let me know if you have any idea about what might be the best approach.

Thanks again

Cheers

Shameem