Hoppa till huvudinnehåll

 Hur skickar jag e-post till flera mottagare i en lista från Excel via Outlook?

Om du har flera e-postadresser i en kolumn i kalkylbladet och nu vill du skicka ett e-postmeddelande till den här listan över mottagare från Excel direkt utan att öppna Outlook. Den här artikeln kommer jag att prata om hur man skickar e-post till flera mottagare från Excel samtidigt.

Skicka e-post till flera mottagare från Excel med VBA-kod

Skicka e-post till flera mottagare med aktuell arbetsbok som bilaga med hjälp av VBA-kod

pil blå höger bubbla Skicka e-post till flera mottagare från Excel med VBA-kod

Du kan använda en VBA-kod för att skicka meddelande till flera mottagare samtidigt, gör så här:

1. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

2. Klicka Insert > Modulernaoch klistra in följande kod i Modulfönster.

VBA-kod: Skicka e-post till flera mottagare

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. Och tryck sedan på F5 nyckel för att köra den här koden, kommer en snabbruta att dyka upp för att påminna dig om att välja adresslistan, se skärmdump:

doc skicka flera mottagare 1

4. Klicka sedan OKoch en Outlook Meddelande fönstret visas kan du se alla valda e-postadresser har lagts till i Till fält, och sedan kan du ange ämnet och komponera ditt meddelande, se skärmdump:

doc skicka flera mottagare 2

5. När du har avslutat meddelandet klickar du på Skicka knappen, och detta meddelande skickas till dessa mottagare i din kalkylbladslista.

pil blå höger bubbla Skicka e-post till flera mottagare med aktuell arbetsbok som bilaga med hjälp av VBA-kod

Om du behöver skicka ett meddelande till flera mottagare med aktuell arbetsbok som bilaga kan du tillämpa följande VBA-kod.

1. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

2. Klicka Insert > Modulernaoch klistra in följande kod i Modulfönster.

VBA-kod: Skicka e-post till flera mottagare med aktuell arbetsbok som bilaga

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. När du har klistrat in koden trycker du på F5 nyckel för att köra den här koden, och en snabbruta visas för att påminna dig om att du väljer adresserna du vill skicka meddelande till, se skärmdump:

doc skicka flera mottagare 3

4. Klicka sedan på OK och en Outlook Meddelande fönstret visas har alla e-postadresser lagts till i Till fält, och din nuvarande arbetsbok har också infogats som en bilaga, och sedan kan du skriva in ämnet och skriva ditt meddelande, se skärmdump:

doc skicka flera mottagare 4

5. Klicka sedan Skicka knappen för att skicka detta meddelande till mottagarlistan med den aktuella arbetsboken som bilaga.

Skicka personliga e-postmeddelanden till flera mottagare med olika bilagor:

Med Kutools för Excel's Skicka e-mail funktionen kan du snabbt skicka personliga e-postmeddelanden till flera mottagare med olika bilagor från Excel som du behöver. Samtidigt kan du CC eller Bcc meddelandena till en viss person också. Klicka för att ladda ner Kutools för Excel!

doc skicka personliga e-postmeddelanden 18 1

Relaterad artikel:

Hur skickar jag personliga massmeddelanden till en lista från Excel via Outlook?

Bästa kontorsproduktivitetsverktyg

🤖 Kutools AI Aide: Revolutionera dataanalys baserat på: Intelligent utförande   |  Generera kod  |  Skapa anpassade formler  |  Analysera data och generera diagram  |  Anropa Kutools funktioner.
Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup  |   VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar   |  Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler   |  Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser:  12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50+ Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

Beskrivning

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
Comments (20)
No ratings yet. Be the first to rate!
Diana Kay Miller
Diana Kay Miller
This comment was minimized by the moderator on the site
There is no "Upload Attachment" box on my end.
Reply
0
0
skyyang
skyyang     Diana Kay Miller
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.
Reply
Report
0
0
Diana
Diana
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
Reply
0
0
skyyang
skyyang     Diana
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!
Reply
Report
0
0
Diana
Diana     skyyang
This comment was minimized by the moderator on the site
Thank you but unfortunately it did not work. I still get the same results.
Reply
0
0
skyyang
skyyang     Diana
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!
Reply
Report
0
0
Diana
Diana     skyyang
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
Reply
0
0
skyyang
skyyang     Diana
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!
Report
0
0
Eugen
Eugen
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). 
Reply
Report
0
0
MandyZhou
MandyZhou     Eugen
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
View Attachments (1 / 5)
Reply
Report
0
0
Jose
Jose
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
Reply
0
0
steve
steve
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
Reply
0
0
Vinicius
Vinicius
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 ?
Reply
0
0
skyyang
skyyang     Vinicius
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
Reply
Report
0
0
James V
James V
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.
Reply
0
0
Robert Springer
Robert Springer
This comment was minimized by the moderator on the site
How can I do this using the BCC line?
Reply
0
0
skyyang
skyyang     Robert Springer
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!
View Attachments (1 / 5)
Reply
Report
0
0
Shashank
Shashank
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.
Reply
0
0
Jake
Jake
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
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations