Solved

Excel Spreadsheet - Send email if certain value is selected.

Posted on 2016-07-14
14
79 Views
Last Modified: 2016-08-23
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?
0
Comment
Question by:niccross
  • 4
  • 4
  • 3
  • +1
14 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41712623
Upload a sample workbook with some dummy data and with a layout as same as that of your original workbook.
0
 
LVL 13

Expert Comment

by:Alexei Kuznetsov
ID: 41712684
I can recommend the commercial product that can manage it, if you want.
0
 

Author Comment

by:niccross
ID: 41712977
0
 
LVL 13

Expert Comment

by:Alexei Kuznetsov
ID: 41713022
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.
0
 

Author Comment

by:niccross
ID: 41713039
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.
0
 
LVL 13

Expert Comment

by:Alexei Kuznetsov
ID: 41713047
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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:niccross
ID: 41713128
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
0
 
LVL 9

Accepted Solution

by:
Christopher Jay Wolff earned 500 total points
ID: 41713176
You may like this link at MSDN that covers a lot of VB when using email.
https://msdn.microsoft.com/en-us/library/ff458119(v=office.12).aspx

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.

=HYPERLINK("mailto:christopherjaywolff@yahoo.com?cc=joesmith@smithco.com&bcc=Mike@Mikeinc.com&subject=Notification%20email.&body=Spreadsheet%20cellA8%20value%20is:%20"&A8,"ClickHereToNotify")

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.
0
 
LVL 13

Expert Comment

by:Alexei Kuznetsov
ID: 41713362
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.
0
 

Author Comment

by:niccross
ID: 41713390
Unfortunately, its the way they want it. It's the last field they will end up selecting
0
 
LVL 9

Expert Comment

by:Christopher Jay Wolff
ID: 41717482
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.
http://www.exceluser.com/excel_help/questions/vba_textcols.htm

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.
http://www.howtogeek.com/204088/how-to-use-a-batch-file-to-make-powershell-scripts-easier-to-run/

And here is Microsoft's version of how to use the Send-MailMessage cmdlet in Powershell.
https://technet.microsoft.com/en-us/library/hh849925.aspx?f=255&MSPPError=-2147217396

And here is a helpful usage of Send-MailMessage with larger files getting piped.
https://chinnychukwudozie.com/2014/11/12/powershell-script-monitors-security-logs-and-sends-email-alerts/







Now we have to run the .bat file from Excel with VBA which is described here below using the Shell function.
https://www.experts-exchange.com/questions/20497707/Using-Shell-function-in-Excel-VBA.html

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-to-call-a-bat-file-through-excel-vba/7e968ef2-a290-426f-8780-ed9def3ab38d?auth=1



Is this any help?
0
 
LVL 9

Expert Comment

by:Christopher Jay Wolff
ID: 41751462
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?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now