Solved

Excel Spreadsheet - Send email if certain value is selected.

Posted on 2016-07-14
14
98 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 29

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 14

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 14

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 14

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
 

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 14

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel VBA 4 27
Export all vba modules from outlook vbaproject.otm 7 22
Outlook 2013 License Question 8 28
Excel filter on tab not showing any entries? 5 22
Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

810 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