Solved

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

Posted on 2013-05-14
3
353 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help Creating PowerShell Script 4 35
Microsoft Outlook 2016/365 1 23
Determine dependencies 3 21
Formula returning #N/A 9 31
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

685 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