Skip i'r prif gynnwys

 Sut i anfon e-bost at dderbynwyr lluosog mewn rhestr gan Excel trwy Outlook?

Os oes gennych sawl cyfeiriad e-bost mewn colofn o daflen waith, ac yn awr, rydych chi am anfon e-bost at y rhestr hon o dderbynwyr o Excel yn uniongyrchol heb agor yr Outlook. Yr erthygl hon, byddaf yn siarad am sut i anfon e-bost at dderbynwyr lluosog o Excel ar yr un pryd.

Anfon e-bost at dderbynwyr lluosog o Excel gyda chod VBA

Anfon e-bost at dderbynwyr lluosog gyda'r llyfr gwaith cyfredol fel atodiad trwy ddefnyddio cod VBA

swigen dde glas saeth Anfon e-bost at dderbynwyr lluosog o Excel gyda chod VBA

Gallwch ddefnyddio cod VBA i anfon neges at dderbynwyr lluosog ar unwaith, gwnewch fel a ganlyn:

1. Dal i lawr y ALT + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Ffenestr Modiwl.

Cod VBA: Anfon e-bost at dderbynwyr lluosog

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. Ac yna pwyswch F5 yn allweddol i weithredu'r cod hwn, bydd blwch prydlon yn popio allan i'ch atgoffa rhag dewis y rhestr cyfeiriadau, gweler y screenshot:

doc anfon derbynwyr lluosog 1

4. Yna cliciwch OK, ac Rhagolwg Neges ffenestr yn cael ei harddangos, gallwch weld bod yr holl gyfeiriadau e-bost a ddewiswyd wedi'u hychwanegu at y I maes, ac yna gallwch chi fynd i mewn i'r pwnc a chyfansoddi'ch neges, gweler y screenshot:

doc anfon derbynwyr lluosog 2

5. Ar ôl gorffen y neges, cliciwch anfon botwm, ac anfonir y neges hon at y derbynwyr hyn yn eich rhestr taflen waith.

swigen dde glas saeth Anfon e-bost at dderbynwyr lluosog gyda'r llyfr gwaith cyfredol fel atodiad trwy ddefnyddio cod VBA

Os oes angen i chi anfon neges at dderbynwyr lluosog sydd â'r llyfr gwaith cyfredol fel atodiad, gallwch gymhwyso'r cod VBA canlynol.

1. Daliwch i lawr y ALT + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Ffenestr Modiwl.

Cod VBA: Anfon e-bost at dderbynwyr lluosog gyda'r llyfr gwaith cyfredol fel atodiad

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. Ar ôl pasio'r cod, pwyswch F5 allwedd i redeg y cod hwn, ac mae blwch prydlon yn cael ei popio allan i'ch atgoffa rhag dewis y cyfeiriadau rydych chi am anfon neges atynt, gweler y screenshot:

doc anfon derbynwyr lluosog 3

4. Yna cliciwch OK botwm, ac Rhagolwg Neges ffenestr yn cael ei harddangos, mae'r holl gyfeiriadau e-bost wedi'u hychwanegu at y I maes, ac mae eich llyfr gwaith cyfredol wedi'i fewnosod fel atodiad hefyd, ac yna gallwch chi fynd i mewn i'r pwnc a chyfansoddi'ch neges, gweler y screenshot:

doc anfon derbynwyr lluosog 4

5. Yna cliciwch anfon botwm i anfon y neges hon at y rhestr o dderbynwyr gyda'r llyfr gwaith cyfredol fel atodiad.

Anfon e-byst wedi'u personoli at dderbynwyr lluosog gyda gwahanol atodiadau:

Gyda Kutools ar gyfer Excel's Anfon E-byst nodwedd, gallwch chi anfon e-byst wedi'u personoli yn gyflym at dderbynwyr lluosog gyda gwahanol atodiadau gan Excel trwy Outlook yn ôl yr angen. Ar yr un pryd, gallwch CC neu Bcc y negeseuon i berson penodol hefyd. Cliciwch i lawrlwytho Kutools ar gyfer Excel!

doc yn anfon e-byst wedi'u personoli 18 1

Erthygl gysylltiedig:

Sut i anfon e-byst torfol wedi'u personoli i restr o Excel trwy Outlook?

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad

Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
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