Link to home
Start Free TrialLog in
Avatar of adogem
adogem

asked on

How do I send a single email from outlook to multiple addressees in an excel database

I wish to send a single email to lots of addresses stored in an excel sheet. I have no idea how to set up or run macros and have been confused by stuff I have read. Can anyone help?
SOLUTION
Avatar of reb73
reb73
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The code sample below shows how to send either a single email or individual messages.  It assumes that
the addresses are in Column A, starting with A1.
Option Explicit 
Sub SendOneEmail()
    
    Dim Counter As Long
    Dim LastR As Long
    Dim olApp As Object
    Dim olMsg As Object
    
    LastR = Cells(Rows.Count, 1).End(xlUp).Row
    Set olApp = CreateObject("Outlook.application")
    
    Set olMsg = olApp.CreateItem(0)
    With olMsg
        .To = ConcRange(Range([a1], "a" & LastR), ";", False, True)
        .Subject = "subject"
        .Body = "body"
        .Send
    End With
    
    Set olMsg = Nothing
    Set olApp = Nothing
    
    MsgBox "Done"
    
End Sub 
Sub SendManyEmails()
    
    Dim Counter As Long
    Dim LastR As Long
    Dim olApp As Object
    Dim olMsg As Object
    
    LastR = Cells(Rows.Count, 1).End(xlUp).Row
    Set olApp = CreateObject("Outlook.application")
    
    For Counter = 1 To LastR
        Set olMsg = olApp.CreateItem(0)
        With olMsg
            .To = Cells(Counter, 1)
            .Subject = "subject"
            .Body = "body"
            .Send
        End With
    Next
    
    Set olMsg = Nothing
    Set olApp = Nothing
    
    MsgBox "Done"
    
End Sub 
Function ConcRange(Substrings As Range, Optional Delim As String = "", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False)
    
    ' Function by Patrick Matthews, Matt Vidas, and rberke 
    ' Concatenates a range of cells, using an optional delimiter.  The concatenated
    ' strings may be either actual values (AsDisplayed=False) or displayed values.
    ' If NoBlanks=True, blanks cells or cells that evaluate to a zero-length string
    ' are skipped in the concatenation
    
    ' Substrings: the range of cells whose values/text you want to concatenate.  May be
    ' from a row, a column, or a "rectangular" range (1+ rows, 1+ columns)
    
    ' Delimiter: the optional separator you want inserted between each item to be
    ' concatenated.  By default, the function will use a zero-length string as the
    ' delimiter (which is what Excel's CONCATENATE function does), but you can specify
    ' your own character(s).  (The Delimiter can be more than one character)
    
    ' AsDisplayed: for numeric values (includes currency but not dates), this controls
    ' whether the real value of the cell is used for concatenation, or the formatted
    ' displayed value.  Note for how dates are handled: if AsDisplayed is FALSE or omitted,
    ' dates will show up using whatever format you have selected in your regional settings
    ' for displaying dates.  If AsDisplayed=TRUE, dates will use the formatted displayed
    ' value 
    ' SkipBlanks: Indicates whether the function should ignore blank cells (or cells with
    ' nothing but spaces) in the Substrings range when it performs the concatenation.
    ' If NoBlanks=FALSE or is omitted, the function includes blank cells in the
    ' concatenation.  In the examples above, where NoBlanks=False, you will see "extra"
    ' delimiters in cases where the Substrings range has blank cells (or cells with only
    ' spaces)
    
    Dim CLL As Range
    
    For Each CLL In Substrings.Cells
        If Not (SkipBlanks And Trim(CLL) = "") Then
            ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
        End If
    Next CLL 
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
    
End Function

Open in new window

Avatar of adogem
adogem

ASKER

I hate to sound like a divit, but how do I actually operate this macro. I have created it in outlook, but when I run it it tells me:

error
user defined - type not defined and highlights the following exert

Function ConcRange(Substrings As Range, Optional Delim As String = "", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean =

and how do I tell it which excel doc to execute this on? etc
adogem,

That code as written actually goes into the Excel file that has the address information.

Regards,

Patrick
Avatar of adogem

ASKER

Ok so far so good!
that all works fine in terms of sending an email, but being the divit that I am, how do I change the" body " bit so that I can actually control and edit the email I am about to send to all these addresses.... sorry to be a bore

thanks
Adrian
Adrian,

If you could elaborate on what you want to do with the email body, I can give more specific instructions.

That said, if you are sending individual messages and customizing the body to each recipient, then a MailMerge
is a viable option...

Patrick
Avatar of adogem

ASKER

Sorry
I'll be more succinct
I wish to send out an identical email, that is a flyer for a theatre production, and I wish it to go to lots of different theatres in a database that I have in an excel doc
I have the email already prepared in outlook (edited in word)

Adrian
Adrian,

If you already have the email written in Word, then I now urge you to go the MailMerge route.  Word has a
reasonably easy to use wizard that will guide you through the process.

Patrick
Avatar of adogem

ASKER

Patrick
you are of course right! I tried and tested it and it's just what I need. Thanks for your time mate!

Adrian
Adrian,

I appreciate the points, but in fairness reb73 did suggest the MailMerge a few seconds before I did.  In this
case, I think a split between me and reb73 would have been a more appropriate outcome.

If you agree, please use the request attention link to ask a Moderator to reopen this question, so that you can
enter a split.

Regards,

Patrick
Avatar of adogem

ASKER

fair do's
am onto it

Adrian
Avatar of adogem

ASKER

Thanks very much for your patience