Solved

Email From Outlook using Email Addresses in Excel via Macro

Posted on 2013-01-24
2
512 Views
Last Modified: 2013-01-24
I would like to take a list of email addresses from Excel and place them in the "To" field of an Outlook email. In column A of the attached file is a list of email addresses that I would like to place into the "To" field of an Outlook email. One email with all addresses.

I would like to do this via a macro.

Any help would be much appreciated.
testfile.xlsx
0
Comment
Question by:woodsboro_kid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 350 total points
ID: 38815496
Here is a macro that checks to see if Outlook is running, creates a new email message, and adds as many email addresses as are present in column A of the current sheet. Attached is your file with the code included. If you click the Macros button on the View tab, you can run it using the EmailMsg routine. (Note that I had to save the file as .xlsm in order to save it with code included.)

If you want to have the macro add a subject or message body as well, you could add these lines after the while loop or really anywhere in the With statement block:

    .Subject = strSubject
    .Body = strMessage

(And either define the strSubject and strMessage variables and fill their values before that, or replace them with string literals.)

Public Sub EmailMsg()
Dim olApp As Object
Dim olNS As Object
Dim olMsg As Object
Dim olRecipList As Object
Dim msgresult As Integer
Dim strEmailSendTo As String
Dim i As Integer


On Error GoTo ErrSkip

    'Test to see if Outlook is running.
    On Error Resume Next    'Defer error trapping.
    Do
        Err.Clear
        Set olApp = GetObject(, "Outlook.Application")
        If Err.Number <> 0 Then msgresult = MsgBox("Error connecting to Outlook: " & Err.Description & _
            " (" & Err.Number & "). Please open Outlook and then retry generating the email notification.", vbRetryCancel)
    Loop While (Err.Number <> 0) And msgresult = vbRetry
    If Err.Number <> 0 Then GoTo ExitErr
    Err.Clear    'Clear Err object in case error occurred.

On Error GoTo ErrSkip

Set olNS = olApp.GetNamespace("MAPI")
Set olMsg = olApp.CreateItem(0)  'olMailItem = 0

With olMsg
    'Add the To recipient(s) to the message.
    i = 1
    strEmailSendTo = ActiveSheet.Range("A" & i).Value
    While Len(strEmailSendTo) > 0
        .Recipients.Add(strEmailSendTo).Type = 1 'olTo = 1
        i = i + 1
        strEmailSendTo = ActiveSheet.Range("A" & i).Value
    Wend

    'Resolve each Recipient's name.
    For Each olRecipList In .Recipients
      olRecipList.Resolve
    Next
    .Display
End With

ExitErr:
Exit Sub

ErrSkip:
    MsgBox "Error in EmailMsg function." & vbCrLf & Err.Number & ": " & Err.Description
    Resume ExitErr
End Sub

Open in new window

testfile-macro.xlsm
0
 

Author Closing Comment

by:woodsboro_kid
ID: 38815539
Perfect! Thanks a lot!
0

Featured Post

Technology Partners: 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

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!
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

726 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