Excel Spreadsheet - Send email if certain value is selected.

I have this worksheet that has column that is labeled Customer Satisfaction. The options are YES and NO Elevated. If NO Elevated is selected I would like it to send an email with the information from that row's ticket number data. Ideally it send it without opening outlook but if it has to then it does.

We are using Exchange 2013 so it could be just sent internally.

Anyone know how to accomplish this?
Who is Participating?
Christopher Jay WolffConnect With a Mentor Wiggle My Legs, OwnerCommented:
You may like this link at MSDN that covers a lot of VB when using email.

I cannot quickly see if it's possible to use the mailto command from VB.  I cannot run VB with my Excel.  If you can, then the syntax for mailto is to put the following in the appropriate cell.


Open in new window

The above example has chris@yahoo.com in the to box, joesmith@smithco.com in the CC box and Mike@MikeInc.com in the BCC box, subject is "Notification email.", and the body is :
Spreadsheet cell A8 value is: [value of A8].  The hyperlink reads "ClickHereToNotify"

The next example

=hyperlink(ʺmailto:ʺ&C6&ʺ ; ʺ&C8&ʺ?subject=Shift Swap Notice&body=ʺ&C7,ʺClick2SendEmail to ʺ&C8&ʺ ; ʺ&C6)

Open in new window

will have the email address from cell C6 in the To box, followed by a semicolon, and a second email address from cell C8, and subject of "Shift Swap Notice", and the body is the value in cell C7.  The Hyperlink reads "Click2SendEmail to ??????@?????.com ; ????@????.com"

In these cases, the user selects NO elevated and has to click the hyperlink in a easily located obvious cell.  Then Outlook pop up the filled out email and user has to click send.

I hope that's useful to you in some way.

If you can launch bat or powershell from VBA then can do in powershell without dealing with Outlook app.  Using Send-MailMessage command similar to mailto.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Upload a sample workbook with some dummy data and with a layout as same as that of your original workbook.
Alexei KuznetsovMicrosoft Outlook MVPCommented:
I can recommend the commercial product that can manage it, if you want.
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

niccrossAuthor Commented:
Alexei KuznetsovMicrosoft Outlook MVPCommented:
Ok, ReliefJet Essentials for Outlook provides the Mail Merge with Attachments utility. It can easily handle your spreadsheet. Just add one more column that will have non-empty value if you have "NO Elevated" for "Customer satisfaction" column. Say, "No Elevated" and specify a formula for it. After that just save it as CSV and configure the "Mail Merge" utility to use it. Use the "Skip row if the value is not empty" option to specify the "No Elevated" table field. Specify other columns for recipient name, address, subject, etc. Run the utility and it will send all messages as configured.
niccrossAuthor Commented:
The ReliefJet is one solution but it seems like it requires a save and then send as a file. Versus being able to send just a text email with the information.
Alexei KuznetsovMicrosoft Outlook MVPCommented:
It doesn't require to send as a file. You just provide the CSV as a datasource for mailing. You can attach any files (if needed) or you can just send text emails with the information. You can watch the tutorial video, it shows the general idea.

By the way, I recommend this tool because I'm one of the developers, so feel free to ask any further questions.
niccrossAuthor Commented:
It doesn't seem as if it's live interaction. The end user needs to be able to select NO elevated and then it instantly send an email without user interaction
Alexei KuznetsovMicrosoft Outlook MVPCommented:
The end user needs to be able to select NO elevated and then it instantly send an email without user interaction
A-ha! This is that. It was not clear in your question. I thought that you need to process the completed Excel spreadsheet by sending messages to required recipients... And by the way, are you sure you really want to send messages right away on selecting the value in the cell? I'm pretty sure that completing these fields and processing the spreadsheed by schedule is much more convenient.
niccrossAuthor Commented:
Unfortunately, its the way they want it. It's the last field they will end up selecting
Christopher Jay WolffWiggle My Legs, OwnerCommented:
Hi.  Finally had time to open the example.  Trying to clarify.  Do you want the email that gets sent to list all the ticket numbers that match with "NO elevated" ??  I figure you wouldn't want a separate email for every "NO elevated" value in the data, correct?  Or maybe you do, and it gets sent to the email address in H2?

If the type of work-arounds I listed above are not helpful, seems like VBA calling a .bat that launches powershell or something would be best.  Maybe someone here knows better.

Here is a link to read a text file or write a text file with VB in Excel.

So now we have a way to get your data list of ticket numbers, etc., into a file.

Then we have to know how to run a powershell script from a .bat file.  Here is a description of that.

And here is Microsoft's version of how to use the Send-MailMessage cmdlet in Powershell.

And here is a helpful usage of Send-MailMessage with larger files getting piped.

Now we have to run the .bat file from Excel with VBA which is described here below using the Shell function.


Is this any help?
Christopher Jay WolffWiggle My Legs, OwnerCommented:
Hmm.  Would anyone care if I requested the points go to me?  Trying to stick up for myself here.  There are some good email command syntaxes I've put into my posts here.  They are proven and functional.  In the original question the author said

 " Ideally it send it without opening outlook but if it has to then it does."  

Some of my proven solutions open Outlook, but the author says if it has to then it does.  In my examples I included methods for putting Excel cell values into the body of the email as the author requested, and I know it works.

Also, in my post ID: 41713176  the link to Microsoft opens to part 1, at the bottom, you click for part two and see how to use VB to email a range as here below.  But you have to read the links.

Screen Grab of Email From Excel With VB.
I also included links for proven solutions to use VB to launch a batch file to run a powershell script if the author prefers to send the email with powershell rather than Outlook, or directly with VB as in the referenced "part 2" above.

I thought I read on EE somewhere a year ago or so, that the rule of thumb was, in order to get full credit, an answerer doesn't have to write the code for the question author, simply do the guide or correct or suggest, type of thing.

How do we feel about this, eh?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.