Preskoči na glavno vsebino

 Kako poslati e-pošto več prejemnikom na seznamu iz Excela prek Outlooka?

Če imate v stolpcu delovnega lista več e-poštnih naslovov in želite zdaj poslati e-poštno sporočilo na ta seznam prejemnikov iz Excela neposredno, ne da bi odprli Outlook. V tem članku bom govoril o tem, kako istočasno pošiljati e-pošto več prejemnikom iz Excela.

Pošljite e-pošto več prejemnikom iz Excela s kodo VBA

Pošljite e-pošto več prejemnikom s trenutnim delovnim zvezkom kot prilogo s kodo VBA


puščica modri desni mehurček Pošljite e-pošto več prejemnikom iz Excela s kodo VBA

Kodo VBA lahko uporabite za pošiljanje sporočil več prejemnikom hkrati, naredite naslednje:

1. Drži dol ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. klik Vstavi > Moduliin prilepite naslednjo kodo v Okno modula.

Koda VBA: pošljite e-pošto več prejemnikom

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. In nato pritisnite F5 tipko za zagon te kode, se bo pojavilo pozivno polje, ki vas bo opozorilo na izbiro naslova, glejte posnetek zaslona:

doc pošlje več prejemnikov 1

4. Nato kliknite OKin Outlook Sporočilo prikaže se okno, lahko vidite, da so bili vsi izbrani e-poštni naslovi dodani v Da polje, nato pa lahko vnesete zadevo in sestavite sporočilo, glejte posnetek zaslona:

doc pošlje več prejemnikov 2

5. Po končanem sporočilu kliknite Pošlji in to sporočilo bo poslano tem prejemnikom na seznamu delovnih listov.


puščica modri desni mehurček Pošljite e-pošto več prejemnikom s trenutnim delovnim zvezkom kot prilogo s kodo VBA

Če morate poslati sporočilo več prejemnikom s trenutnim delovnim zvezkom kot prilogo, lahko uporabite naslednjo kodo VBA.

1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Okno modula.

Koda VBA: Pošljite e-poštno sporočilo več prejemnikom s trenutnim delovnim zvezkom kot prilogo

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. Po lepljenju kode pritisnite F5 tipko za zagon te kode in pojavilo se bo pozivno polje, ki vas bo opozorilo na izbiro naslovov, na katere želite poslati sporočilo, glejte posnetek zaslona:

doc pošlje več prejemnikov 3

4. Nato kliknite OK in Outlook Sporočilo prikaže se okno, vsi e-poštni naslovi so bili dodani v Da polje in tudi vaš trenutni delovni zvezek je bil vstavljen kot priloga, nato pa lahko vnesete zadevo in sestavite sporočilo, glejte posnetek zaslona:

doc pošlje več prejemnikov 4

5. Nato kliknite Pošlji , da to sporočilo pošljete na seznam prejemnikov s trenutnim delovnim zvezkom kot prilogo.


Pošljite prilagojena e-poštna sporočila več prejemnikom z različnimi prilogami:

z Kutools za Excel's Pošlji e-pošto funkcija, lahko hitro pošljete prilagojena e-poštna sporočila več prejemnikom z različnimi prilogami iz Excela prek Outlooka, kot jih potrebujete. Hkrati lahko sporočila CC ali Bcc kopirate tudi določeni osebi. Kliknite za prenos Kutools za Excel!

doc pošilja prilagojena e-poštna sporočila 18 1


Povezani članek:

Kako poslati osebna množična e-poštna sporočila na seznam iz Excela prek Outlooka?

Najboljša pisarniška orodja za produktivnost

🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

Opis


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
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