Skip i'r prif gynnwys

Sut i anfon taflen waith yn unig trwy Outlook o Excel?

Awdur: Xiaoyang Wedi'i Addasu Diwethaf: 2020-05-22

Os ydych chi am e-bostio taflen waith sengl allan o lyfr gwaith yn Excel trwy Outlook, gallwch anfon y daflen waith fel atodiad, fel cynnwys y corff neu fel ffeil PDF. Ond a oes unrhyw ffyrdd cyflymach ichi ddelio â'r broblem hon yn Excel?

Anfonwch daflen waith sengl fel corff o Excel gydag Anfon at orchymyn Derbynnydd Post

Anfonwch daflen waith sengl fel atodiad gan Excel gyda chod VBA

Anfonwch daflen waith sengl fel ffeil PDF o Excel gyda chod VBA

swigen dde glas saeth Anfonwch daflen waith sengl fel corff o Excel gydag Anfon at orchymyn Derbynnydd Post

Mae Excel yn ein cefnogi i e-bostio'r daflen waith weithredol fel cynnwys y corff trwy ddefnyddio'r gorchymyn Anfon at Derbynnydd Post. Gallwch wneud fel a ganlyn:

Os ydych chi'n defnyddio Excel 2007, 2010 neu 2013, mae angen ichi ychwanegu hyn Anfonwch at Derbynnydd Post gorchymyn i'r Bar Offer Mynediad Cyflym gyntaf.

1. Cliciwch eicon y Addasu Bar Offer Mynediad Cyflym, a dewis Mwy o Orchmynion, gweler y screenshot:

doc-e-bost-sheet1

2. Ac yn y Dewisiadau Excel blwch deialog, dewiswch Gorchmynion Ddim yn y Rhuban yn y Dewiswch Orchmynion o gwymplen, yna dewiswch y Anfonwch at Derbynnydd Post opsiwn, a chlicio Ychwanegu >> botwm i ychwanegu'r gorchymyn hwn, o'r diwedd cliciwch OK i achub y gosodiad hwn. Gweler y screenshot:

doc-e-bost-sheet2

3. Mae adroddiadau Anfonwch at Derbynnydd Post mae gorchymyn wedi'i fewnosod yn y Bar Offer Mynediad Cyflym, gweler y screenshot:

doc-e-bost-sheet3

4. Yna cliciwch hwn Anfonwch at Derbynnydd Post botwm eicon, a blwch prydlon yn popio allan, yn y E-bost blwch prydlon, gwirio Anfonwch y ddalen gyfredol fel y corff negeseuon, a chliciwch OK. Gweler y screenshot:

doc-e-bost-sheet4

5. Ac mae blwch golygu e-bost yn cael ei arddangos uwchben data'r daflen waith, gallwch nodi'ch derbynwyr, eu pwnc a'u cyflwyno yn y blwch testun cyfatebol. Gweler y screenshot:

doc-e-bost-sheet5

6. Yna cliciwch Anfonwch y Shee hwnt anfon y daflen waith weithredol hon fel corff negeseuon at eich person penodol.

swigen dde glas saeth Anfonwch daflen waith sengl fel atodiad gan Excel gyda chod VBA

Os hoffech anfon e-bost at y daflen waith weithredol fel atodiad, gall y cod VBA canlynol ffafrio chi.

1. Gweithredwch eich taflen waith rydych chi am ei hanfon.

2. Daliwch i lawr y ALT + F11 allweddi, ac mae'n agor y Ffenestr Microsoft Visual Basic for Applications.

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

Cod VBA: anfonwch y daflen waith gyfredol fel atodiad gan Excel

Sub SendWorkSheet()
'Update 20131209
Dim xFile As String
Dim xFormat As Long
Dim Wb As Workbook
Dim Wb2 As Workbook
Dim FilePath As String
Dim FileName As String
Dim OutlookApp As Object
Dim OutlookMail As Object
On Error Resume Next
Application.ScreenUpdating = False
Set Wb = Application.ActiveWorkbook
ActiveSheet.Copy
Set Wb2 = Application.ActiveWorkbook
Select Case Wb.FileFormat
Case xlOpenXMLWorkbook:
    xFile = ".xlsx"
    xFormat = xlOpenXMLWorkbook
Case xlOpenXMLWorkbookMacroEnabled:
    If Wb2.HasVBProject Then
        xFile = ".xlsm"
        xFormat = xlOpenXMLWorkbookMacroEnabled
    Else
        xFile = ".xlsx"
        xFormat = xlOpenXMLWorkbook
    End If
Case Excel8:
    xFile = ".xls"
    xFormat = Excel8
Case xlExcel12:
    xFile = ".xlsb"
    xFormat = xlExcel12
End Select
FilePath = Environ$("temp") & "\"
FileName = Wb.Name & Format(Now, "dd-mmm-yy h-mm-ss")
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
Wb2.SaveAs FilePath & FileName & xFile, FileFormat:=xFormat
With OutlookMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "kte features"
    .Body = "Please check and read this document."
    .Attachments.Add Wb2.FullName
    .Send
End With
Wb2.Close
Kill FilePath & FileName & xFile
Set OutlookMail = Nothing
Set OutlookApp = Nothing
Application.ScreenUpdating = True
End Sub

Nodyn: Yn y cod uchod, gallwch newid y wybodaeth ganlynol i'ch angen eich hun.

  • .i = ""
  • .CC = ""
  • .BCC = ""
  • .Subject = "nodweddion kte"
  • .Body = "Gwiriwch a darllenwch y ddogfen hon."

4. Yna cliciwch F5 allwedd i redeg y cod hwn, a bydd blwch prydlon yn popio allan, cliciwch Caniatáu pan fydd y bar cynnydd wedi'i orffen, ac yna mae'r daflen waith gyfredol wedi'i hanfon at eich derbynnydd fel atodiad.

doc-e-bost-sheet6

swigen dde glas saeth Anfonwch daflen waith sengl fel ffeil PDF o Excel gyda chod VBA

Weithiau, mae angen i chi anfon eich adroddiad taflen waith at eraill ond ddim eisiau i eraill ei addasu. Yn yr achos hwn, gallwch anfon y daflen waith fel ffeil PDF o Excel.

1. Gweithredwch eich taflen waith rydych chi am ei hanfon.

2. Daliwch i lawr y ALT + F11 allweddi, ac mae'n agor y Ffenestr Microsoft Visual Basic for Applications.

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

Cod VBA: anfonwch y daflen waith gyfredol fel ffeil PDF o Excel

Sub SendWorkSheetToPDF()
'Update 20131209
Dim Wb As Workbook
Dim FileName As String
Dim OutlookApp As Object
Dim OutlookMail As Object
On Error Resume Next
Set Wb = Application.ActiveWorkbook
FileName = Wb.FullName
xIndex = VBA.InStrRev(FileName, ".")
If xIndex > 1 Then FileName = VBA.Left(FileName, xIndex - 1)
FileName = FileName & "_" + ActiveSheet.Name & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileName
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "kte features"
    .Body = "Please check and read this document."
    .Attachments.Add FileName
    .Send
End With
Kill FileName
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

Nodyn: Yn y cod uchod, gallwch newid y wybodaeth ganlynol i'ch angen.

  • .i = ""
  • .CC = ""
  • .BCC = ""
  • .Subject = "nodweddion kte"
  • .Body = "Gwiriwch a darllenwch y ddogfen hon."

4. Yna pwyswch F5 allwedd, a bydd blwch prydlon yn popio allan, cliciwch Caniatáu ar ôl i'r bar cynnydd ddod i ben, yna mae'r daflen waith weithredol wedi'i hanfon at y person penodol fel ffeil PDF.

doc-e-bost-sheet6

Nodiadau:

1. Dim ond pan fyddwch chi'n defnyddio Outlook fel eich rhaglen bost y mae'r dulliau hyn ar gael.

2. Ar ôl anfon y daflen waith gyfredol, gallwch fynd i'ch Camre i sicrhau bod yr e-bost wedi'i anfon yn llwyddiannus.

Creu Rhestr Bostio Yna Anfon E-byst
Mae adroddiadau Kutools ar gyfer Excel's Creu Rhestr Bostio ac anfon Negeseuon e-bost gall cyfleustodau greu rhestr bostio yn gyflym mewn taflen waith, yna anfon yr un pwnc, yr un cynnwys a'r un atodiadau i gyfeiriadau e-bost lluosog.
Ewch i Lawrlwytho
Treial Am Ddim 30 diwrnod
rhestr bostio doc 1
saeth doc i lawr
rhestr bostio doc 2

Erthyglau cysylltiedig:

Sut i anfon llyfr gwaith cyfredol trwy Outlook o Excel?

Sut i anfon / e-bostio ystod o gelloedd trwy ragolygon gan Excel?

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 (34)
No ratings yet. Be the first to rate!
Philip
Philip
This comment was minimized by the moderator on the site
I've found the code you post for Typhaine and it works very well for me.
So thank's very much.
Philip.
Reply
0
0
Typhaine
Typhaine
This comment was minimized by the moderator on the site
Bonjour,

Est-il possible d'utiliser le code pour joindre deux feuilles du fichier Excel dans le mail ?

Merci d'avance.
Reply
0
0
skyyang
skyyang     Typhaine
This comment was minimized by the moderator on the site
Hello, Typhaine
To send multiple sheets, please apply the below code:
Note: In the code, you should change the sheet names to your own.
Sub Mail_Sheets_Array()
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim TheActiveWindow As Window
    Dim TempWindow As Window
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set Sourcewb = ActiveWorkbook
    With Sourcewb
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array("Sheet1", "Sheet2")).Copy
    End With
    TempWindow.Close
    Set Destwb = ActiveWorkbook
    With Destwb
        If Val(Application.Version) < 12 Then
           
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            
            Select Case Sourcewb.FileFormat
            Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
            Case 52:
                If .HasVBProject Then
                    FileExtStr = ".xlsm": FileFormatNum = 52
                Else
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If
            Case 56: FileExtStr = ".xls": FileFormatNum = 56
            Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        End If
    End With
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

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

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "KTE features"
            .Body = "Please check and read this document"
            .Attachments.Add Destwb.FullName
           
            .Display
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With
   
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
Potocki
Potocki
This comment was minimized by the moderator on the site
Hej,

Muszę wysłać zakres (stały) arkusza jako obraz w treści maila jednocześnie dodając cały arkusz jako plik/załącznik. Czy jest to możliwe?
Reply
Report
0
0
Jose
Jose
This comment was minimized by the moderator on the site
Hi the program worked just fine till 2021, I tried to run it  today, but it does send the email. As does notshow any errors
Reply
0
0
Bhattacharjee
Bhattacharjee
This comment was minimized by the moderator on the site
This is to inform you that i have an VBA code for send email from outlook with the help of excel vba,now i want to put "MDD Code" & " MDD Name" as well as Cells(i, 1) & Cells(i, 2) in a table like that.

MDD Code MDD Name
M123 Joydip

I am sending you the VBA Code,Request you for help.
VBA Code
----------------------------------------------------------------------------------------------------------------------------------------------
Sub sendmail()

Dim olapp As Outlook.Application

Dim olmail As Outlook.MailItem

For i = 2 To 35

Application.ScreenUpdating = False

Set olapp = New Outlook.Application

Set olmail = olapp.CreateItem(olMailItem)

With olmail

olmail.To = Cells(i, 4).Value

olmail.CC = Cells(i, 6).Value

olmail.Subject = Cells(i, 7).Value

olmail.HTMLBody = "Dear Partner ," & _

"
Please find the attchment." & _

"

MDD Code : " & Cells(i, 1) & _

"
MDD Name : " & Cells(i, 2) & _

"






Joydip Bhattacharjee" & _

"
Company" & _

"
MIS" & _

"
Country" & _

"
Contact No : 7602066491"







olmail.Attachments.Add Cells(i, 8).Value

'On Error Resume Next

'olmail.Attachments.Add Cells(i, 9).Value

'On Error Resume Next

'olmail.Attachments.Add Cells(i, 10).Value

'On Error Resume Next

'olmail.Attachments.Add Cells(i, 11).Value

'On Error Resume Next

'olmail.Attachments.Add Cells(i, 12).Value

'On Error Resume Next

'olmail.Attachments.Add Cells(i, 13).Value

'On Error Resume Next

'olmail.Attachments.Add Cells(i, 14).Value

'On Error Resume Next

olmail.Send

End With

Set olmail = Nothing

Next

End Sub
Reply
Report
0
0
mehmet
mehmet
This comment was minimized by the moderator on the site
merhaba ben bunu belirli periyotta otomatik mail atmasını nasıl ayarlayabilirim
Reply
0
0
Oscar Eseisa
Oscar Eseisa
This comment was minimized by the moderator on the site
Excelent code. Thanks!
Reply
0
0
Dave
Dave
This comment was minimized by the moderator on the site
Anyway I can easily send an excel worksheet through my outlook without all this ?? I can send the worksheet context, but no the workbook as an attachment. On my work computer I can send from word and excel, but am having trouble at home.
Reply
0
0
Richard
Richard
This comment was minimized by the moderator on the site
Hi! Is it possible to use this code, but instead of sending straight away it opens up the mail?
Reply
0
0
Sunny
Sunny     Richard
This comment was minimized by the moderator on the site
You can try this code:
Sub SendWorkSheet()
'Update 20180109
Dim xFile As String
Dim xFormat As Long
Dim Wb As Workbook
Dim Wb2 As Workbook
Dim FilePath As String
Dim FileName As String
Dim OutlookApp As Object
Dim OutlookMail As Object
On Error Resume Next
Application.ScreenUpdating = False
Set Wb = Application.ActiveWorkbook
ActiveSheet.Copy
Set Wb2 = Application.ActiveWorkbook
Select Case Wb.FileFormat
Case xlOpenXMLWorkbook:
xFile = ".xlsx"
xFormat = xlOpenXMLWorkbook
Case xlOpenXMLWorkbookMacroEnabled:
If Wb2.HasVBProject Then
xFile = ".xlsm"
xFormat = xlOpenXMLWorkbookMacroEnabled
Else
xFile = ".xlsx"
xFormat = xlOpenXMLWorkbook
End If
Case Excel8:
xFile = ".xls"
xFormat = Excel8
Case xlExcel12:
xFile = ".xlsb"
xFormat = xlExcel12
End Select
FilePath = Environ$("temp") & "\"
FileName = Wb.Name & Format(Now, "dd-mmm-yy h-mm-ss")
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
Wb2.SaveAs FilePath & FileName & xFile, FileFormat:=xFormat
With OutlookMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "kte features"
.Body = "Please check and read this document."
.Attachments.Add Wb2.FullName
.Display
' .Send
End With
Wb2.Close
Kill FilePath & FileName & xFile
Set OutlookMail = Nothing
Set OutlookApp = Nothing
Application.ScreenUpdating = True
End Sub

Please let me know if it works for you, thank you.
Reply
Report
0
0
Sherry
Sherry
This comment was minimized by the moderator on the site
This code works good, however, does anyone know a way to automate a field as an alert for the email to go automatically based on a date column?
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