Showing posts with label Database Mail. Show all posts
Showing posts with label Database Mail. Show all posts

11/8/12

The Analytics Lesson from the Obama Campaign: Keep Your Data Organized, Secret

Time magazine just published a fascinating account of how President Obama's campaign team used data to microtarget voters. At HBR, we've been tracking the rise of Big Data in the private sector for some time, and see this as a useful case study of how one organization actually implemented those analytic principles to get results. I spoke over the phone with MIT's Andrew McAfee, a regular contributor and principal research scientist at the Center for Digital Business in the MIT Sloan School of Management. What follows is an edited version of our conversation.

Even though there's a push for data transparency, it seems like Obama's key to success was the opposite: to keep his data and algorithms as secret as possible. Organizationally, does it make sense to keep your data team siloed off from the rest of the team, or do you want them to work together more closely?

In this case, it's easier to identify the downsides of sharing that data more broadly. It could leak out, leak to the press, leak to the other candidate. If there is some secret sauce, you want to keep that close. If either the data asset itself or the algorithm on top of it seems to be cutting edge or proprietary, I wouldn't go shout it from the rooftops. You want to have the data team say to the volunteers, "Call up these people, knock on these doors, go to these neighborhoods." The volunteer doesn't need to know why; they just need to know they're knocking on the right doors.

That seems to be a move away from this movement we've been reading about about information wants to be shared, data wants to be free, everything should be transparent —

From any rational standpoint, that line is nonsense. That's like saying, "All money needs to be free," or if you're a trucking company, "All trucks need to be free." Data is an asset, like everything else.

In the spotlight on Big Data that we just ran in the magazine and online, we focused heavily on making the case that executives really do need to use it. We focused less on the execution piece of it. But it seems to me that having the data is only one part of the problem; you really need to know what to do about it.

There are two different questions to the execution piece. First, there's executing with the data resource and the computing resource itself. A lot of organizations aren't good at that because our data is fragmented. The amount of centralization and rationalization to take real advantage of big data is pretty daunting for a lot of organizations. To take the Obama campaign as an example, look at their move from having two databases in 2008 to one in 2012. I guarantee you that was not easy. That was technically pretty challenging, and it was organizationally super, super difficult — it means you have to convince one group to let go of their database and let go of being the sole keepers of that data. That's hard.

The second side of the execution, though, is the question of what do you do with the data. Which ads do you serve up, which doors do you knock on, which streets do you go down? Campaigns have been doing this for a long time; they know how to knock on doors. That's not the hard part. But to my eyes, what big data can do is help them be more efficient, by allowing them not to knock on every door on that street. You can say to the volunteer, "Just go knock on house numbers 14 and 18, and then skip the next six houses and go knock on house number 30."

Big data lets you be small. It lets you do really precise, targeted interventions. I see evidence of this over and over, in all kinds of organizations. It started with marketing, but that has led the way to operations and the supply chain also using those kinds of precise data-driven interventions. And then you can do that at scale.

So you're saying it lets you be small, but at scale? Are you then big again? [Laughs]

It's a very different kind of big; it's not mass production. "Mass customization" is an overused phrase and it's been around for a while, but in this era of big data we can really do that. It's no longer just configuring my car so it has the upholstery I want. It's that campaigns no longer need to do the kind of mass mailings they're always relying on; they can get the right kind of mailing to the right kind of voter. Maybe this one cares about women's rights, but that one cares about economic policy.

For the Obama campaign, that was at the heart of their ground game advantage. In our HBR article, we included an example from the health care sector. Over and over we're seeing this ability to be precise and differentiated, at scale and repeatedly, with a lot more efficacy.

How would this work in the business world, say in health care?

Take the example of Aetna. They have around 20 million lives under care. Because they're the company that pays the doctor, the lab, the pharmacy, and so on, they have all this information about my health. There are huge confidentially concerns, of course, but let's put that aside for a moment.

What that means is that they have better data about my health than any other player in the system; than my doctor, my hospital, my pharmacy. What Aetna can do at scale, in real time, over and over, is check across the millions of lives in their system and say, "Do we see any obvious gaps in care here?" They can then send a message to the doctor or to the patient. Now, we need to be careful about confidentiality, and we need to make sure the messages are phrased the right way so they're not ignored, but we can use this database to do mass scale interventions into health care delivery. And that means we can improve outcomes without having to rejigger the entire system.

Okay, so say everyone starts doing that. Then do you get to a place like we see now in baseball — where a few years ago, crunching the numbers gave some teams a big advantage, but now everyone does it and it confers less of one? Look at the Oakland A's. Using data used to give them a big advantage, but since every team started to do it, it really doesn't seem to give them much of one.

Yes, you've got to move on. It's not going to give you an advantage forever, but if you are analytically oriented, you can push ahead and get finer-grained insight and advantage. In baseball, the science of sabermetrics has moved on. They're doing increasingly sophisticated things. Billy Beane and the A's were able to pick the low-hanging fruit and do really well for a while because they were able to do the simple things better. Now everyone has those insights so they've got to work harder. The marginal benefit might be less, but you've still got to do it. If you just go back to scouting players like you did in the 1990s, that's a great way to have a terrible team. Standing still is a very bad strategy.

What's interesting to me is that the volumes of data are exploding terribly quickly. The toolkit is also expanding by leaps and bounds. This is a real new arms race. You might not love it, and you might wish the world was predictable and calm and that Excel would get you through — but that would be a recipe for disaster.

http://blogs.hbr.org/hbr/hbreditors/2012/11/the_analytics_lesson_from_the.html

10/1/12

Sending mail from Excel with CDO

What is CDO doing

The example code is using CDOSYS (CDO for Windows 2000).
It does not depend on MAPI or CDO and hence is dialog free
and does not use your mail program to send email.


Briefly to explain, this code builds the message and drops it
in the pickup directory, and SMTP service running on the machine
picks it up and send it out to the internet.


Why using CDO code instead of Outlook automation or SendMail in VBA.

1: It doesn't matter what Mail program you are using (It only use the SMTP server).
2: It doesn't matter what Office version you are using (97…2007)
3: You can send a range/sheet in the body of the mail (some mail programs can’t do this)
4: You can send any file you like (Word, PDF, PowerPoint, TXT files,….)
5: No Security warnings anymore, really great if you are sending a lot of mail in a loop.


Read this!!!

This code will not work in Win 98 and ME.
You must be connected to the internet when you run a example.

It is possible that you get a Send error when you use one of the examples.
AFAIK : This will happen if you haven't setup an account in Outlook Express or Windows Mail.
In that case the system doesn't know the name of your SMTP server.
If this happens you can use the commented green lines in each example.
Don't forget to fill in the SMTP server name in each code sample where
it says "Fill in your SMTP server here"

When you also get the Authentication Required Error you can add this three lines.
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"


Don't remove the TextBody line in the code. If you do you can't open the attachment (bug in CDO).
If you don't want to have text in the body use this then .TextBody = ""

Note: It is always possible that your firewall block the code (Check your firewall settings)


Can you use CDO on your machine?

Let's try a basic example first.

The code below will send four text lines in the body of the mail to the person in this line
.To = "ron@debruin.nl"

Change ron@debruin.nl to your own mail address before you test the code.
If you read the information above you know that if you have a account in Outlook Express or
Windows Mail you can Run the code below after changing the mail address.
But if you not have a account in Outlook Express or Windows Mail you also need the commented
green lines in the code. Remove every ' before every green line and fill in the name of your SMTP server
where it says "Fill in your SMTP server here"

1) Open a new workbook
2) Alt F11 (to open the VBA editor)
3) Insert>Module
4) Paste the code in this module
5) Make your changes
6) Alt q to go back to Excel

When you use Alt F8 you can select the macro and press Run.
Now wait a moment and see if you receive the mail in your inbox.
Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    '    iConf.Load -1    ' CDO Source Defaults
    '    Set Flds = iConf.Fields
    '    With Flds
    '        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
    '                       = "Fill in your SMTP server here"
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    '        .Update
    '    End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .From = """Ron"" "
        .Subject = "New figures"
        .TextBody = strbody
        .Send
    End With

End Sub

Note: If you get this error : The transport failed to connect to the server
then try to change the SMTP port from 25 to 465



Use the GMail SMTP server from Google.
http://gmail.google.com

You can find the code in the workbook with examples that you can download below.
There is more information about the code in the workbook.
Note: You must have a Gmail account to try this example.




Download workbook with more examples

You can download a example workbook with eighth examples.
Download Example workbook with all the code

Attachment examples:
Module file1 = Workbook
Module file2 = One worksheet or more
Module file3 = Every sheet with a mail address in cell A1

Body examples:
Module body1 = Selection/Range or whole worksheet
Module body2 = Personalized Mail
Module body3 = Every sheet with a mail address in cell A1
Module body4 = Small text and text from a txt file

Note: the body examples in the workbook are using the function RangetoHTML in
the "bodyfunction" module of the workbook.

Gmail example:
Module gmail = Use the smtp.gmail.com server from Gmail to send mail



Tips and links


CDO sheet template

Check out this sheet template if you want to send every sheet to a different person.
Or want to send one or more sheets to one or more recipient.
http://www.rondebruin.nl/mail/templates.htm



Set importance/priority and request read receipt

For importance/priority and read receipt you can add this in the With iMsg part of the macro before .Send

' Set importance or Priority to high
.Fields("urn:schemas:httpmail:importance") = 2
.Fields("urn:schemas:mailheader:X-Priority") = 1

' Request read receipt
.Fields("urn:schemas:mailheader:return-receipt-to") = "ron@debruin.nl"
.Fields("urn:schemas:mailheader:disposition-notification-to") = "ron@debruin.nl"

' Update fields
.Fields.Update


Changing the To line

If you want to mail to all E-mail addresses in a range then use this code
instead of .To = "ron@debruin.nl"

The example below will use the cells from sheets("Sheet1") in ThisWorkbook (workbook with the code)
It is possible that you must use ActiveWorkbook or something else in your code to use it.
    Dim cell As Range
    Dim strto As String
    On Error Resume Next
    For Each cell In ThisWorkbook.Sheets("Sheet1") _
        .Range("A1:A10").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    On Error GoTo 0
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

Change the To line to .To = strto


Or to more people
.To = "Jon@something.com;ron@something.com"

Or you can use a address in a cell like this
.To = Sheets("Sheet1").Range("C1").Value



Change the Body line


Plain text :

Note: see also the example in the workbook to send all text from a txt file (Module body4)

If you want to add more text to the body then you can use the code below.
Instead of .TextBody = "This is the body text" use .TextBody = strbody then.

Dim strbody As String
strbody = "Hi there" & vbNewLine & vbNewLine & _
    "This is line 1" & vbNewLine & _
    "This is line 2" & vbNewLine & _
    "This is line 3" & vbNewLine & _
    "This is line 4"


Or use this if you want to use cell values

Dim cell As Range
Dim strbody As String
For Each cell In Sheets("Sheet1").Range("C1:C20")
    strbody = strbody & cell.Value & vbNewLine
Next


Or this one

Dim strbody As String
With Sheets("Sheet1")
    strbody = "Hi there" & vbNewLine & vbNewLine & _
        .Range("A1") & vbNewLine & _
        .Range("A2") & vbNewLine & _
        .Range("A3") & vbNewLine & _
        .Range("A4")
End With




Links

.TextBody = "file://Yourcomputer/YourFolder/Week2.xls"

'If there are spaces use %20
.TextBody = "file://Yourcomputer/YourFolder/Week%202.xls"

'Example for a file on a website
.TextBody = "http://www.rondebruin.nl/files/EasyFilter.zip"



HTML text :

If you want to create emails that are formatted you can use HTMLBody (Office 2000 and up) instead of TextBody. You can find a lot of WebPages on the internet with more HTML tags examples.

.HTMLBody = "

Dear Ron de Bruin

" & _
"Please visit this website to download an update.
" & _
"Ron's Excel Page"



Tip: Or send a complete webpage, instead of HTMLBody or TextBody use

.CreateMHTMLBody "http://www.rondebruin.nl/copy1.htm"

Or file on your computer
.CreateMHTMLBody "file://C:/test.htm"



Copy the cells as values

If you want to paste as values the sheet must be unprotected!!!!!
Or Unprotect and Protect the sheet in the Sub also.

See this page for example code that you can use
http://www.rondebruin.nl/values.htm



Test if you are online

You can use code like this in your subroutine to avoid errors if you run the code
when you are not online (example below is for a dial up connection)

For checking other connections check out this great website.
http://vbnet.mvps.org/

Public Declare Function InternetGetConnectedState _
                         Lib "wininet.dll" (lpdwFlags As Long, _
                                            ByVal dwReserved As Long) As Boolean

Function IsConnected() As Boolean
    Dim Stat As Long
    IsConnected = (InternetGetConnectedState(Stat, 0&) <> 0)
End Function

Sub Test()
' Randy Birch
    If IsConnected = True Then
        MsgBox "Copy your mail code here"
    Else
        MsgBox "You can't use this subroutine because you are not online"
    End If
End Sub


Links to more information about CDO for windows 2000


MSDN
Search for "CDO for Windows 2000" on MSDN

Paul R. Sadowski
http://www.paulsadowski.com/WSH/cdo.htm

www.aspfaq.com
http://www.aspfaq.com/show.asp?id=2026

http://www.rondebruin.nl/cdo.htm

6/18/10

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

Today in this article I would discuss about the Database Mail which is used to send the Email using SQL Server. Previously I had discussed about SQL SERVER – Difference Between Database Mail and SQLMail. Database mail is the replacement of the SQLMail with many enhancements. So one should stop using the SQL Mail and upgrade to the Database Mail. Special thanks to Software Developer Monica, who helped with all the images and extensive testing of subject matter of this article.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

Step 1) Create Profile and Account:

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='PinalProfile',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker, read more at SQL SERVER – Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below:

SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO

Status can be verified using sysmail_sentitems table.

After sending mail you can check the mail received in your inbox, just as I received as shown below.

Let me know what you think about this tutorial.

Reference : Pinal Dave (http://blog.SQLAuthority.com)