?
Solved

Email From Outlook using Email Addresses in Excel via Macro

Posted on 2013-01-24
2
Medium Priority
?
514 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 1400 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Suggested Courses

800 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