Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to send an e-mail message to a lot of users attaching on it some personal data, like passwords, that cannot be sent to others.

The users and personal data are organized on columns on an Excel file.

I would like to send an e-mail automatically to each user based on that file, i.e., a message for each row, and using certain message template.

On Excel menu, I couldn't find such option...

Any help?



Recipient   Password fjsdjg kjasdh laldwk kdfljf

E-mail message body template:

Hello friends!

I created a survey on Google Docs named "XXX", with the
following link: http://XXX

To avoid double submitting or answers from people out of
our group, I created a password below for you. Please,
enter that on related field to validate your form.


share|improve this question
Which email program do you use? Or perhaps more importantly, how do you send email using your email address? Is SMTP supported? – allquixotic Aug 7 '12 at 14:32
@allquixotic I can use any free e-mail manager. Do you recommend one for me? I have a lot of addresses with SMTP support, but I can create one for that purpose. – kokbira Aug 7 '12 at 14:52
up vote 1 down vote accepted

The best way to do this is with the "Mail Merge" feature in Microsoft Word under the mailings tab. This will let you send a message to the e-mail addresses listed on your spread sheet, as it gives you the option to select a spreadsheet to pull information off of for the mail merge.

share|improve this answer
If I have 500 recipient addresses I think it will spend much time. I'm searching for a more automatic way, like doing a few mouse clicks not related with the recipient quantity to send the e-mails... If I do as you suggested, I have to repeat that procedure 500 times.. – kokbira Aug 7 '12 at 14:56
this is what mail merge is used for. It will slam through the e-mails listed on your spreadsheet and send them the same template with the specific information that you have selected for each user to see. The most I have ever sent a mail merge to is around 700 people but it only took a minute or two for that to happen. – TheRandomSuit Aug 7 '12 at 14:59
Nice. I'll test it today. – kokbira Aug 7 '12 at 15:42
the mail merge wizard is the easiest way, it walks you through step by step. – TheRandomSuit Aug 7 '12 at 15:44

You can also do this from VBA using Microsoft Outlook (or you can replace the SendEmail function in the code with any other mail sending implementation you want). Just open up the Developer tab, Visual Basic, and put this in the ThisWorkbook module.

Option Explicit

Public Mail_Object As Object

Sub SU_458659()
    Dim numofrows As Integer
    Dim i As Integer
    Dim ws As Worksheet
    Dim startRow As Integer
    Dim emailColumn As Integer
    Dim passwordColumn As Integer

    'TWEAKABLE: Change this to the first row to process
    startRow = 2 'Assuming row 1 is header

    'TWEAKABLE: Change ActiveSheet to a sheet name, etc. if you don't want it to run on the currently "active" (selected) sheet
    Set ws = ActiveSheet

    'TWEAKABLE: Change this to the row number ("A" is 1) of the email address
    emailColumn = 1

    'TWEAKABLE: Change this to the row number ("B" is 2) of the password field
    passwordColumn = 2

    'Get the number of rows in the sheet
    numofrows = ws.Range("A1").Offset(ws.Rows.Count - 1, 0).End(xlUp).Row

    'Shouldn't have to tweak anything in here
    For i = startRow To numofrows
        Dim emailCell As Range
        Dim passwordCell As Range
        Set emailCell = ws.Cells(i, emailColumn)
        Set passwordCell = ws.Cells(i, passwordColumn)
        If Not IsEmpty(emailCell) Then
            Dim email As String
            Dim password As String
            email = CStr(emailCell.Value)
            password = CStr(passwordCell.Value)
            SendEmail email, password
        End If
    Next i
End Sub

Sub SendEmail(email As String, password As String)
    Dim emailSubject As String
    Dim emailSendFrom As String
    Dim emailCc As String
    Dim emailBcc As String
    Dim prePassword As String
    Dim postPassword As String
    Dim Mail_Single As Variant

    If Mail_Object Is Nothing Then Set Mail_Object = CreateObject("Outlook.Application")

    'TWEAKABLE: Email subject
    emailSubject = "CHANGE THIS"

    'TWEAKABLE: The 'from' email address
    emailSendFrom = ""

    'TWEAKABLE: The CC: field (just make it the empty string "" if you don't want a CC
    emailCc = ""

    'TWEAKABLE: The BCC: field (just make it the empty string "" if you don't want a BCC)
    emailBcc = ""

    'TWEAKABLE: The email body BEFORE the password
    prePassword = "Your password is: """

    'TWEAKABLE: The email body AFTER the password - vbCrLf is a newline like hitting Enter
    postPassword = """." & vbCrLf & vbCrLf & "Have fun!"

    On Error GoTo debugs
    Set Mail_Single = Mail_Object.CreateItem(0)
    With Mail_Single
    .Subject = emailSubject
    .To = email
    .cc = emailCc
    .BCC = emailBcc
    .Body = prePassword & password & postPassword

    'TWEAKABLE: Remove the following three lines before ".send" to remove message box confirmation
    Dim retval As Variant
    retval = MsgBox("Do you want to send an email to " & email & " with the password " & password & "?", vbYesNo, "Confirmation")
    If retval = vbNo Then Exit Sub

    End With
    If Err.Description <> "" Then MsgBox Err.Description
End Sub
share|improve this answer
i'll test it... – kokbira Aug 7 '12 at 15:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .