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?
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
adogem

8/22/2022 - Mon
SOLUTION
reb73

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Patrick Matthews

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

ASKER
adogem

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
Patrick Matthews

adogem,

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

Regards,

Patrick
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
adogem

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
Patrick Matthews

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
ASKER
adogem

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Patrick Matthews

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
ASKER
adogem

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

Adrian
Patrick Matthews

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
adogem

fair do's
am onto it

Adrian
ASKER
adogem

Thanks very much for your patience