Drop down menu to automatically send email

[JONNY981]

Hello,

I am trying to create a sheet of a list of jobs, I will fill in the sheet and when the job is completed somebody else will select 'Complete' from the drop down menu in column E.

Is there a way that I can get excel to automatically send an email when 'complete' is selected? I need to send an email to the email address which I have matched to the initials in the 'Raised By' column. I need the email to contain the info from the cells i.e description, ref no. and date raised.

Thanks!

[Steffen Thomsen]

Hi,

Try this.

Place the code in the sheet code:

Please Login or Register  to view this content.

Steffen Thomsen

[Richard Buttrey]

Hi,

One way would be as per the attached.
It assumes your email client is MS Outlook. Note Outlook Express will not work.

In the code module comment out the 'Display instruction when you're happy it's working OK and uncomment the .Send instruction.

Regards

[JONNY981]

Thanks for the help.

I have used Richard's way, although I am having a few problems transferring the code from the test sheet to my actual sheet. I.e I will have to add a few more names and email addresses and the data will be in different columns.

Do you mind explaining what I need to change in order to do this?

Thanks.

[Steffen Thomsen]

Hi Jonny981,

You have code in 2 places you need to copy,

1 - From sheet1 code module
2 - From Module 1

These two you need to copy into the respective modules in your workbook.

The part you need to change to address different cells

is the following in Sheet1 module

Please Login or Register  to view this content.

Hope you get it to work

Steffen Thomsen

[Richard Buttrey]

Hi,

The Worksheet Change event currently detects a change in column E - see second line of code below. Change that as appropriate.

If the columns are in the same relative position you won't need to change anything else in this code. If they are set out differently you'll need to alter the row and column co-ordinates in the "Target.Cells(r,c)" lines of code

Make sure that you have the three cells, presently M1:M3 named the same. i.e. put the formulae wherever you like but name them "LastSel", "RowNo" and "eMail".

Please Login or Register  to view this content.

[bmasella]

Richard this would work for me as well...

Can I ask if there might be a way to this that would summarize by the "Raised By" and then sent either with a button press or perhaps on the save of the worksheet...

In my example I would have a similar worksheet but it could have 20 entries by 1 person and 6 by someone else and 1 by a 3rd... I'd like to only send 1 email to each person and have their 20, 6 or 1 entry respectively.

Great example... thanks for your help!!!!

[Richard Buttrey]

Hi,

Yes indeed.

You'd need to hold a list of email addresses.
You'd then need a macro containing a loop which would automatically filter your data by individual and extract their records to a separate area. Then within the loop call the macro that raises the Outlook email.

You could either have a button to start this process or make it dependent on an event like the saving or close of the workbook.

If you need any specific code then upload your workbook with a note as to which records you need to issue.

Regards

[bmasella]

Sounds exactly like what I am looking for.
Here is a rough approximation of what I would like to try to do... The concept is rehearsal notes. I would populate a worksheet like sheet 2 with notes from a rehearsal and then I would like to distribute those notes to each individual. Individual information is found on sheet 1 with the "key" between the two sheets being the character. Since some notes are delivered in person I would need a way to flag those - ie the Distributed column. If that field is not blank the row should not be emailed.

[Steffen Thomsen]

Hi bmasella,

According to the forum rules you have to start a new post describing your problem.
If it's like this, then link to it in your thread.

Steffen Thomsen

[Richard Buttrey]

Quite right Steffen. I'd missed this change of poster name.

Bmasella. Would you start another thread pleaseas Steffen mentions. Include your requirements and we can no doubt pick it up there.

Regards

[bmasella]

Sorry guys I didn't realize this especially since it really is the same thing as the original post just an extension of what it does.

[Steffen Thomsen]

I have posted a reply in your thread.

[JONNY981]

Thanks again, although If I try to copy and paste anything in the sheet then I get a run time error. any ideas why?