[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

Email From Outlook using Email Addresses in Excel via Macro

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
woodsboro_kid
Asked:
woodsboro_kid
1 Solution
 
telyni19Commented:
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
 
woodsboro_kidAuthor Commented:
Perfect! Thanks a lot!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now