Ga naar hoofdinhoud

 Hoe stuur ik e-mail naar meerdere ontvangers in een lijst vanuit Excel via Outlook?

Als u meerdere e-mailadressen in een kolom met een werkblad heeft, en u wilt nu rechtstreeks vanuit Excel een e-mail naar deze lijst met ontvangers sturen zonder Outlook te openen. In dit artikel zal ik het hebben over het tegelijkertijd verzenden van e-mail naar meerdere ontvangers vanuit Excel.

Stuur e-mail naar meerdere ontvangers vanuit Excel met VBA-code

Stuur e-mail naar meerdere ontvangers met de huidige werkmap als bijlage met behulp van VBA-code


pijl blauw rechts bel Stuur e-mail naar meerdere ontvangers vanuit Excel met VBA-code

U kunt een VBA-code gebruiken om een ​​bericht naar meerdere ontvangers tegelijk te verzenden, doe dit als volgt:

1. Houd de toets ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klik Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: stuur e-mail naar meerdere ontvangers

Sub sendmultiple()
'updateby Extendoffice
    Dim xOTApp As Object
    Dim xMItem As Object
    Dim xCell As Range
    Dim xRg As Range
    Dim xEmailAddr As String
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the addresses list:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xOTApp = CreateObject("Outlook.Application")
    For Each xCell In xRg
        If xCell.Value Like "*@*" Then
            If xEmailAddr = "" Then
                xEmailAddr = xCell.Value
            Else
                xEmailAddr = xEmailAddr & ";" & xCell.Value
            End If
        End If
    Next
    Set xMItem = xOTApp.CreateItem(0)
    With xMItem
        .To = xEmailAddr
        .Display
    End With
End Sub

3. En druk vervolgens op F5 toets om deze code uit te voeren, verschijnt er een promptvenster om u eraan te herinneren de adressenlijst te selecteren, zie screenshot:

doc meerdere ontvangers verzenden 1

4. Dan klikken OK, en een Outlook Bericht venster wordt weergegeven, kunt u zien dat alle geselecteerde e-mailadressen zijn toegevoegd aan het Naar veld, en dan kunt u het onderwerp invoeren en uw bericht opstellen, zie screenshot:

doc meerdere ontvangers verzenden 2

5. Klik na het afronden van het bericht op Verzenden knop, en dit bericht wordt naar deze ontvangers in uw werkbladlijst gestuurd.


pijl blauw rechts bel Stuur e-mail naar meerdere ontvangers met de huidige werkmap als bijlage met behulp van VBA-code

Als u een bericht naar meerdere ontvangers moet sturen met de huidige werkmap als bijlage, kunt u de volgende VBA-code toepassen.

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: stuur e-mail naar meerdere ontvangers met de huidige werkmap als bijlage

Sub EmailAttachmentRecipients()
'updateby Extendoffice
    Dim xOutlook As Object
    Dim xMailItem As Object
    Dim xRg As Range
    Dim xCell As Range
    Dim xEmailAddr As String
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the arresses list:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xOutlook = CreateObject("Outlook.Application")
    Set xMailItem = xOutlook.CreateItem(0)
    For Each xCell In xRg
        If xCell.Value Like "*@*" Then
            If xEmailAddr = "" Then
                xEmailAddr = xCell.Value
            Else
                xEmailAddr = xEmailAddr & ";" & xCell.Value
            End If
        End If
    Next
    With xMailItem
        .To = xEmailAddr
        .CC = ""
        .Subject = ""
        .Body = ""
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    Set xOutlook = Nothing
    Set xMailItem = Nothing
End Sub

3. Druk na het plakken van de code op F5 toets om deze code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren de adressen te selecteren waarnaar u een bericht wilt verzenden, zie screenshot:

doc meerdere ontvangers verzenden 3

4. Dan klikken OK knop, en een Outlook Bericht venster wordt weergegeven, zijn alle e-mailadressen toegevoegd aan het Naar veld, en je huidige werkmap is ook als bijlage ingevoegd, en dan kun je het onderwerp invoeren en je bericht opstellen, zie screenshot:

doc meerdere ontvangers verzenden 4

5. Dan klikken Verzenden om dit bericht naar de lijst met ontvangers te sturen met de huidige werkmap als bijlage.


Stuur gepersonaliseerde e-mails naar meerdere ontvangers met verschillende bijlagen:

met Kutools for Excel's Stuur emails functie kunt u snel gepersonaliseerde e-mails naar meerdere ontvangers sturen met verschillende bijlagen vanuit Excel via Outlook als u dat nodig hebt. Tegelijkertijd kunt u de berichten ook naar een specifieke persoon CC of Bcc sturen. Klik om Kutools voor Excel te downloaden!

doc gepersonaliseerde e-mails verzenden 18 1


Gerelateerd artikel:

Hoe stuur ik via Outlook gepersonaliseerde massa-e-mails naar een lijst vanuit Excel?

Beste Office-productiviteitstools

Populaire functies: Zoek, markeer of identificeer duplicaten   |  Verwijder lege rijen   |  Combineer kolommen of cellen zonder gegevens te verliezen   |   Ronde zonder formule ...
Super opzoeken: Meerdere criteria VLookup    VLookup met meerdere waarden  |   VOpzoeken over meerdere bladen   |   Fuzzy opzoeken ....
Geavanceerde vervolgkeuzelijst: Maak snel een vervolgkeuzelijst   |  Afhankelijke vervolgkeuzelijst   |  Multi-select vervolgkeuzelijst ....
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  |  Vergelijk bereiken en kolommen ...
Uitgelichte functies: Raster focus   |  Ontwerpweergave   |   Grote formulebalk    Werkmap- en bladbeheer   |  resource Library (Auto-tekst)   |  Datumkiezer   |  Combineer werkbladen   |  Cellen coderen/decoderen    Stuur e-mails per lijst   |  Super filter   |   Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen, ...)   |   50+ tabel Types (Gantt Chart, ...)   |   40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag, ...)   |   19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad, ...)   |   12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion, ...)   |   7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Gespleten cellen, ...)   |   ... en meer

Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen.  Klik hier om de functie te krijgen die u het meest nodig heeft...

kte tabblad 201905


Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
Comments (20)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
There is no "Upload Attachment" box on my end.
This comment was minimized by the moderator on the site
Hello, Diana,
If there is no "Upload Attachment" box, you should register first, and then the "Upload Attachment" option will be appeared.
To register, please go to the top of the article, and click Resgister button to start.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-register.png
I'm sorry for the inconvenience.
This comment was minimized by the moderator on the site
I'm trying to get excel to send an email to multiple recipients and can get everything I need but it refuses to put the email address in the TO box. Here is the code I've been working with. Can anyone help me figure out what I'm doing wrong? Thanks so much!

Sub Macro1()
Dim rngCell As Range
Dim rngMyDataSet As Range
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
Dim EmailRecipient As Range
Dim Signature As String
Application.ScreenUpdating = False
With ActiveSheet
If .FilterMode Then .ShowAllData
Set Rng = .Range("AK6", .Cells(.Rows.Count, 1).End(xlUp))
End With
For Each rngCell In Rng
If rngCell.Offset(0, 6) > 0 Then

ElseIf rngCell.Offset(0, 5) > Evaluate("Today() +7") And _
rngCell.Offset(0, 5).Value <= Evaluate("Today() +30") Then
rngCell.Offset(0, 6).Value = Date

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "According to my records, your contract " & Range("A6").Value & " is due for review on " & rngCell.Offset(0, 5).Value & vbNewLine & _
"Please review this contract prior to the pertinent date and email me with any changes you make to this contract. If it is renewed, please fill out the Contract Cover Sheet which can be found in the Everyone folder and send me the new original contract."
EmailSendTo = rngCell.Offset(0, 0).Value
EmailSubject = Sheets("sheet1").Range("A6").Value
Signature = "C:\Documents and Settings\" & Environ("rmm") & _
"\Application Data\Microsoft\Signatures\rm.htm"
On Error Resume Next
With OutMail
.To = EmailSendTo
.CC = ""
.BCC = ""
.Subject = EmailSubject
.Body = strbody
.Display
Send_Value = Mail_Recipient.Offset(i - 1).Value
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End If

Next rngCell
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Hello, Diana,
Maybe you can apply the below code:

Sub Macro1()
Dim rngCell As Range
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
Dim Signature As String
Application.ScreenUpdating = False
On Error Resume Next
With ActiveSheet
  If .FilterMode Then .ShowAllData
  Set Rng = .Range("AK6", .Cells(.Rows.Count, 1).End(xlUp))
End With
Set OutApp = CreateObject("Outlook.Application")
For Each rngCell In Rng
  If rngCell.Offset(0, 6) > 0 Then
    If rngCell.Offset(0, 5).Value > Evaluate("Today() +7") And _
       rngCell.Offset(0, 5).Value <= Evaluate("Today() +30") Then
      rngCell.Offset(0, 6).Value = Date
    End If
    Set OutMail = OutApp.CreateItem(0)
    MailBody = "According to my records, your contract " & Range("A6").Value & " is due for review on " & rngCell.Offset(0, 6).Value & vbNewLine & _
               "Please review this contract prior to the pertinent date and email me with any changes you make to this contract. If it is renewed, " & _
               "please fill out the Contract Cover Sheet which can be found in the Everyone folder and send me the new original contract."
    
    EmailSendTo = rngCell.Offset(2, 6).Value   'Please specify the row and column number of the addresses in the filtered data range,please change the number 2 and 6 to your need
    EmailSubject = Sheets("sheet1").Range("A6").Value
    Signature = "C:\Documents and Settings\" & Environ("rmm") & _
                "\Application Data\Microsoft\Signatures\rm.htm"
    With OutMail
      .To = EmailSendTo
      .CC = ""
      .BCC = ""
      .Subject = EmailSubject
      .Body = MailBody
      .Recipients.ResolveAll
      .Display
    End With
  End If
Next rngCell
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



EmailSendTo = rngCell.Offset(2, 6).Value, you should change the number 2 and 6 to the row and column number based on your data range, this range contains the email addresses you want to send to.

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Thank you but unfortunately it did not work. I still get the same results.
This comment was minimized by the moderator on the site
Hi, Diana,
In this case, please provide a screenshot or attachment file of the worksheet data so that we can determine where the problem is.
Or you can describe your problem more clearly and detailed.
Thank you!
This comment was minimized by the moderator on the site
Below is the current code I'm using but it will not put each email address in the TO box, only the first email address in all of them. Also does the same thing with the SUBJECT and in the email message, it just uses the same thing again and again. I'm not sure how to attach the spreadsheet to this email.

Sub Macro1()
Dim rngCell As Range
Dim rngMyDataSet As Range
Dim Rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As Range
Dim EmailRecipient As Range
Dim Signature As String
Application.ScreenUpdating = False
With ActiveSheet
If .FilterMode Then .ShowAllData
Set Rng = .Range("AJ6", .Cells(.Rows.Count, 1).End(xlUp))
End With
For Each rngCell In Rng
If rngCell.Offset(0, 6) > 0 Then

ElseIf rngCell.Offset(0, 5) > Evaluate("Today() +7") And _
rngCell.Offset(0, 5).Value <= Evaluate("Today() +120") Then
rngCell.Offset(0, 6).Value = Date

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "According to my records, your " & Range("A6").Value & " contract is due for review " & rngCell.Offset(0, 5).Value & _
". It is important you review this contract ASAP and email me with any changes made. If it is renewed, please fill out the Contract Cover Sheet which can be found in the Everyone folder and send me the cover sheet along with the new original contract."
EmailSendTo = Sheets("sheet1").Range("AJ6").Value
EmailSubject = Sheets("sheet1").Range("A6").Value
Signature = "C:\Documents and Settings\" & Environ("rmm") & _
"\Application Data\Microsoft\Signatures\rm.htm"
On Error Resume Next
With OutMail
.To = EmailSendTo
.CC = ""
.BCC = ""
.Subject = EmailSubject
.Body = strbody
.Display
Send_Value = Mail_Recipient.Offset(i - 1).Value
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End If

Next rngCell
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Hello,
You can insert your workbook as an attachment here, please see the below screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-attachment-1.png
Thank you!
This comment was minimized by the moderator on the site
Is it possible to pimp the code for choosing the CCs from a list the same way after choosing the TOs? With the existing code its not possible to choose any CCs the same way like the TOs (main adresses). 
This comment was minimized by the moderator on the site
Hello Eugen,Glad to help. It is possible to pimp the code for choosing the CCs from a list the same way after choosing the TOs. And the code is basically the same with the TOs VBA code. Only one change should be made. Just change the  ".To = xEmailAddr" to ".Cc = xEmailAddr". Please see the screenshot. And you can choose the CCs and the TOs from a list at the same time. Just make the ".To = xEmailAddr" and ".Cc = xEmailAddr" all included in the VBA code. Please paste the following code in the Module Window.
Sub sendmultiple()
'updateby Extendoffice
Dim xOTApp As Object
Dim xMItem As Object
Dim xCell As Range
Dim xRg As Range
Dim xEmailAddr As String
Dim xTxt As String
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the addresses list:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xOTApp = CreateObject("Outlook.Application")
For Each xCell In xRg
If xCell.Value Like "*@*" Then
If xEmailAddr = "" Then
xEmailAddr = xCell.Value
Else
xEmailAddr = xEmailAddr & ";" & xCell.Value
End If
End If
Next
Set xMItem = xOTApp.CreateItem(0)
With xMItem
.To = xEmailAddr
.Cc = xEmailAddr
.Display
End With
End Sub

Hope it can solve your problem. Have a nice day.Sincerely,Mandy
This comment was minimized by the moderator on the site
I have this Code, my problem is that it creates one email for each time the condition is not complete, but i want to put all the info that dont reach the condition in only one email

Sub EnviarCorreo()
Dim OutApp As Object
Dim OutMail As Object
Dim lLastRow As Long
Dim lRow As Long
Dim sSendTo As String
Dim sSendCC As String
Dim sSendBCC As String
Dim sSubject As String
Dim sTemp As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

' Change the following as needed
sSendTo = ""
sSendCC = ""
sSendBCC = ""
sSubject = "Due date reached"

Set OutMail = OutApp.CreateItem(0)

lLastRow = Cells(Rows.Count, 3).End(xlUp).Row
For lRow = 3 To lLastRow
If Cells(lRow, 9) <> "S" Then
If Cells(lRow, 2) <= Date Then

On Error Resume Next
With OutMail
.To = sSendTo
If sSendCC > "" Then .CC = sSendCC
If sSendBCC > "" Then .BCC = sSendBCC
.Subject = sSubject

sTemp = "Hello!" & vbCrLf & vbCrLf
sTemp = sTemp & "The due date has been reached "
sTemp = sTemp & "for this project:" & vbCrLf & vbCrLf


'THIS IS WHAT I WANT TO REPEAT ON EMAIL BODY
' Assumes project name is in column B
sTemp = sTemp & "ID:"
sTemp = sTemp & " " & Cells(lRow, 1)
sTemp = sTemp & " Description: "
sTemp = sTemp & " " & Cells(lRow, 5)
sTemp = sTemp & " Please take the appropriate"
sTemp = sTemp & " action." & vbCrLf & vbCrLf
sTemp = sTemp & " Thank you!" & vbCrLf
'UNTIL HERE



.Body = sTemp
' Change the following to .Send if you want to
' send the message without reviewing first
.Display
End With
Set OutMail = Nothing

Cells(lRow, 9) = "S"
Cells(lRow, 10) = "E-mail sent on: " & Now()
End If
End If
Next lRow
Set OutApp = Nothing
End Sub
This comment was minimized by the moderator on the site
Morning,


I am new to trying to write and use macros in excel. My first attempt was to try and create a subset mass email from a large master list. I cut and pasted the first routine, then tried to use it all it did was highlight the cells I requested. no outlook email was created, what did I do wrong? To expand upon my actual request, I really want to target emails by zip code or other subsets. how do I create a macro that will search a column for a given zip code and create an email with all recipients found?

thank you

Steve
This comment was minimized by the moderator on the site
Hi ! Every month i should send the same e-mail for diferent providers, but they should not be in the same e-mail..... how could i send the same e-mail for diferent destinations without everyone in the same e-mail ?
This comment was minimized by the moderator on the site
Hello, Vinicius,
To send same email to multiple recipients separately, may be the following article can help you, please view it.
https://www.extendoffice.com/documents/excel/3560-excel-send-personalized-email.html
This comment was minimized by the moderator on the site
Any way to use this to send from a shared email? I cannot seem to inset a .SendOnBehalfOf field.
This comment was minimized by the moderator on the site
How can I do this using the BCC line?
This comment was minimized by the moderator on the site
Hi, Robert,
After running the code, the new message window will be opened, you just need to insert the BCC line under the Option tab, see the following screenshot:


Hope it can help you, thank you!
This comment was minimized by the moderator on the site
Hello, Thank you for the code. Is there a way i can create a command button on the excel and then by clicking on that button the same excel sheet can be sent to multiple recipients as an attachment.
This comment was minimized by the moderator on the site
Hi, The VBA code is working well for me thank you. Is there any way I could create a cell with a button of sorts which triggers the "select mailing list" pop up? Jake
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations