Need help getting excel to send automatic emails based on cell value

Ticket Spreadsheet.xlsx

I have looked at various threads and whilst i can find similar solutions none are working

I want a ticketing system that automatically sends an email containing references to the address in column I when the corresponding cell B is changed to 'IN PROGRESS' then another (different email) when it is changed to resolved.

I want a MsgBox to appear asking: 'Would you like to email the customer?' Y/N so i can choose not to send the email. i want excel to just directly send the email via the exchange network IF POSSIBLE and not open outlook email; however i would be happy to run it through Outlook if it makes this work.



So when column B changes to 'IN PROGRESS' an message box appears.

If Yes is clicked an email is sent:

Email Subject: Ticket Number [COLUMN A] for [COLUMN J]

Email Msg: Thanks for returning your [COLUMN J] your ticket number is [COLUMN A] please quote this on all future emails. We aim to resolve this by [COLUMN C + 7 DAYS]. Thank You.


When column B is changed to 'RESOLVED' then it should send another email out:

Email Subject: Ticket Number [COLUMN A] for [COLUMN J] - RESOLVED

Email Msg: Your issue with your [COLUMN J], ticket number [COLUMN A] has now been resolved. Thank You.


My code is probably miles away from being right:

Please Login or Register  to view this content.

Thanks in advance.

the first code goes in the worksheet project editor area and the second create a module and put that in there...I suppose you can put them both in the worksheet project area...

NOTE: you need to fill in the FROM email address and the SMTP server name....

Please Login or Register  to view this content.

Please Login or Register  to view this content.

the recipient address means it's not a valid email address.....this does assume (sorry about that) that you are using a local email SMTP Server and a valid user on the that SMTP Server....you don't need a password because I assumed you where logged on as that user....the SMTP system should figure that out....mine does anyways....

So, Are you logging on as the user that is sending the email?

add these two lines in the SendEmail module toward the bottom with the other objmessage stuff

Please Login or Register  to view this content.