Solved

Excel Cell to Outlook "TO" field - VBA? or grunt work?

Posted on 2013-05-14
3
323 Views
Last Modified: 2013-05-15
I have about 200 cells which contain email addresses.  
Each cell contains one email address.
What would be the quickest way to get those cells into the "TO" of outlook.
0
Comment
Question by:brothertruffle880
  • 2
3 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39166434
Are we talking about one message with 200 recipients, or 200 messages (1 to each person)?

If the latter, I would use Word's mail merge.  It can take Excel as a data source, and produce emails rather than form letters.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39166443
If the former...

1) Add this UDF to your project:

Function ConcRange(Substrings As Range, Optional Delim As String = "", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False, _
    Optional TrimSpaces As Boolean = False)
    
    ' Function by Patrick Matthews, Matt Vidas, and rberke
    ' Revised based on suggestions from Dave Brett and Mark Hutchinson based on comments
    ' at http://www.experts-exchange.com/A_7811.html
    
    ' Concatenates a range of cells, using an optional delimiter.  The concatenated
    ' strings may be either actual values (AsDisplayed=False) or displayed values.
    ' If SkipBlanks=True, blank 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).  To use
    ' multiple areas, enclose the various ranges in parentheses, e.g.:
    ' =ConcRange((A1:A10,Q14:Q17,Z200:Z300),";")
    
    ' 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 in the Substrings
    ' range when it performs the concatenation.  If SkipBlanks=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
    
    ' TrimSpaces: Indicates whether leading or trailing spaces are removed from substrings
    ' prior to concatenation.  Multiple internal spaces are ignored, as the VBA Trim() and
    ' not Excel's TRIM() is used.  Space trimming is performed against actual or displayed
    ' value, as determined by the AsDisplayed argument.  If SkipBlanks and TrimSpaces are
    ' both True, "blank" is determined based on trimmed value or displayed text
    
    Dim CLL As Range
    
    If AsDisplayed And SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            If Trim(CLL.Text) <> "" Then
                ConcRange = ConcRange & (Delim & Trim(CLL.Text))
            End If
        Next
    ElseIf AsDisplayed And SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            If CLL.Text <> "" Then
                ConcRange = ConcRange & (Delim & CLL.Text)
            End If
        Next
    ElseIf AsDisplayed And Not SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & Trim(CLL.Text))
        Next
    ElseIf AsDisplayed And Not SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & CLL.Text)
        Next
    ElseIf Not AsDisplayed And SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            If Trim(CLL.Value) <> "" Then
                ConcRange = ConcRange & (Delim & Trim(CLL.Value))
            End If
        Next
    ElseIf Not AsDisplayed And SkipBlanks And Not TrimSpaces Then
        For Each CLL In Substrings.Cells
            If CLL.Value <> "" Then
                ConcRange = ConcRange & (Delim & CLL.Value)
            End If
        Next
    ElseIf Not AsDisplayed And Not SkipBlanks And TrimSpaces Then
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & Trim(CLL.Value))
        Next
    Else
        For Each CLL In Substrings.Cells
            ConcRange = ConcRange & (Delim & CLL.Value)
        Next
    End If
        
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
    
End Function

Open in new window



2) Use it in a formula like this:

=ConcRange(A1:A200,";")

3) Copy the result into the To field of your message
0
 

Author Comment

by:brothertruffle880
ID: 39168117
Thank you Matthew Patrick. It was the former.  One message to 200 recipients.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Set OWA language and time zone in Exchange for individuals, all users or per database.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now