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

Question

I am trying to set up several buttons on an Excel form to email different groups of people  I made several ranges of cells on a separate worksheet to list the email addresses  For example  I want  quot Button A quot  to open Outlook and put the list of email addresses from  quot Worksheet  B  Cells D3-D6 quot   Then all that has to be done is hit  quot Send quot  in Outlook  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                EmailTo   Worksheets  quot Selections quot   Range  quot D3 D6 quot                  Set OutApp   CreateObject  quot Outlook Application quot        Set OutMail   OutApp CreateItem 0                 On Error Resume Next      With OutMail           To   EmailTo           CC    quot person1 email com person2 email com quot            BCC    quot  quot            Subject    quot RMA   quot   amp  Worksheets  quot RMA quot   Range  quot E1 quot             Body    quot Attached to this email is RMA   quot   amp  Worksheets  quot RMA quot   Range  quot E1 quot    amp   quot   Please follow the instructions for your department included in this form  quot            Attachments Add ActiveWorkbook FullName           You can add other files also like this           Attachments Add   quot C  test txt quot                          Display      End With      On Error Goto 0                Set OutMail   Nothing      Set OutApp   Nothing  End Sub

User · Answer

Both answers are correct  If you user  TO -method then the semicolumn is OK - but not for the addrecipients-method  There you need to split  e g                    Dim Splitter   As String                 Splitter   Split AddrMail   quot   quot                   For Each Dest In Splitter                      Recipients Add  Trim Dest                   Next

User · Answer

ToAddress    test test com  ToAddress1    test1 test com  ToAddress2    test test com  MessageSubject    It works    Set ol   CreateObject  Outlook Application   Set newMail   ol CreateItem olMailItem  newMail Subject   MessageSubject newMail RecipIents Add ToAddress  newMail RecipIents Add ToAddress1  newMail RecipIents Add ToAddress2  newMail Send

User · Answer

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  Is this what you are trying   TRIED AND TESTED   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  amp       amp  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    person1 email com person2 email com           BCC               Subject    RMA     amp  Worksheets  RMA   Range  E1            Body    Attached to this email is RMA     amp            Worksheets  RMA   Range  E1    amp               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

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

Examples related to excel

Examples related to email

Examples related to vba

Examples related to outlook