How to send email to multiple recipients with addresses stored in Excel?

by

You have to loop through every cell in the range "D3:D6" and construct your To string. Simply assigning it to a variant will not solve the purpose. EmailTo becomes an array if you assign the range directly to it. You can do this as well but then you will have to loop through the array to create your To string

CODE

Option Explicit

Sub Mail_workbook_Outlook_1()
     'Working in 2000-2010
     'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object
    Dim emailRng As Range, cl As Range
    Dim sTo As String
    
    Set emailRng = Worksheets("Selections").Range("D3:D6")
    
    For Each cl In emailRng 
        sTo = sTo & ";" & cl.Value
    Next
    
    sTo = Mid(sTo, 2)
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = sTo
        .CC = "[email protected];[email protected]"
        .BCC = ""
        .Subject = "RMA #" & Worksheets("RMA").Range("E1")
        .Body = "Attached to this email is RMA #" & _
        Worksheets("RMA").Range("E1") & _
        ". Please follow the instructions for your department included in this form."
        .Attachments.Add ActiveWorkbook.FullName
         'You can add other files also like this
         '.Attachments.Add ("C:\test.txt")

        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

More Related Contents:

  1. Sending mail using Outlook where the Send method fails
  2. How to send mail when the .Send does not work?
  3. How to Send Email When Computer is Locked?
  4. How to copy Outlook mail message into excel using VBA or Macros
  5. Copy Excel range as Picture to Outlook
  6. Paste Excel range in Outlook
  7. Update excel sheet based on outlook mail [closed]
  8. Email a single attachment from folder of files each to a different person
  9. How to add an attachment to an email using VBA in Excel
  10. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  11. Finding a workbook in one of multiple Excel instances
  12. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  13. Change HTML email body font type and size in VBA
  14. Save excel file multiple times with file names in another workbook.
  15. Function to convert column number to letter?
  16. Read/Parse text file line by line in VBA
  17. Excel VBA: Answer gets “stuck”
  18. filter out multiple criteria using excel vba
  19. Excel VBA autofilter all but three
  20. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  21. Excel VBA code to copy a specific string to clipboard
  22. How to use FindWindow to find a visible or invisible window with a partial name in VBA
  23. How do you get a Range to return its Name?
  24. In Excel, can I use a hyperlink to run vba macro?
  25. Excel VBA – exit for loop
  26. Macro to export MS Word tables to Excel sheets
  27. VBA WinHTTP to download file from password proteced https website
  28. Excel Hide/Show all tabs on Ribbon except custom tab
  29. Macros Not Showing Up in Macro Table
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment