Loncat ke daftar isi utama

Bagaimana cara mengirim email melalui Outlook saat buku kerja disimpan di Excel?

Artikel ini berbicara tentang mengirim email melalui Outlook saat buku kerja tertentu disimpan di Excel. Harap lakukan seperti yang ditunjukkan tutorial.

Kirim email melalui Outlook saat buku kerja disimpan dengan kode VBA

Kirim email melalui Outlook saat buku kerja disimpan dengan kode VBA

Untuk mengirim email melalui Outlook saat buku kerja disimpan di Excel, lakukan hal berikut.

1. Harap simpan buku kerja sebagai Buku Kerja Excel Macro-Enabled pada awalnya. Klik File > Save As. di Save As kotak dialog, pilih folder untuk menyimpan buku kerja, beri nama di kotak Nama file, pilih Buku Kerja Excel dengan Makro Aktif dari Save as type daftar drop-down, dan kemudian klik Save tombol. Lihat tangkapan layar:

2. Buka Buku Kerja Excel Macro-Enabled yang baru saja Anda simpan, tekan lain + F11 tombol secara bersamaan untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi jendela, silakan klik dua kali Buku Kerja Ini di bilah kiri, lalu salin dan tempel kode VBA di bawah ini ke file Buku Kerja Ini jendela kode. Lihat tangkapan layar:

Kode VBA: Kirim email saat buku kerja disimpan

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
'Updated by Extendoffice 20181102
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xName As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailItem = xOutApp.CreateItem(0)
    xName = ActiveWorkbook.FullName
    With xMailItem
        .To = "Email Address"
        .CC = ""
        .Subject = "The workbook has been saved"
        .Body = "Hi," & Chr(13) & Chr(13) & "File is now updated."
        .Attachments.Add xName
        .Display
       '.send
    End With
    Set xMailItem = Nothing
    Set xOutApp = Nothing
End Sub

Note: Harap ganti Alamat email dengan alamat email penerima sejalan .To = "Alamat Email", dan ubah kolom Cc, Subjek, dan isi di kode VBA sesuai kebutuhan Anda.

3. tekan lain + Q tombol secara bersamaan untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

Mulai sekarang, saat Anda memperbarui buku kerja dan menyimpannya, email akan dibuat secara otomatis dengan melampirkan buku kerja yang diperbarui. Silakan klik Kirim tombol untuk mengirim email. Lihat tangkapan layar:

Note: Kode VBA hanya berfungsi saat Anda menggunakan Outlook sebagai program email Anda.

Artikel terkait:

Alat Produktivitas Kantor Terbaik

馃 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (17)
No ratings yet. Be the first to rate!
florin
florin
This comment was minimized by the moderator on the site
Dear All, can some one help me, I'm a novice in VBA coding, I have made some modification, but how can I do so send email if the workbook is saved, and if user name is different, like if user name of the station is glade2 then send email if the workbook is saved, else do not send.

Thank you so much for your support
Reply
0
0
crystal
crystal     florin
This comment was minimized by the moderator on the site
Hi florin,
Didn't get your point. What does your username represent?
Reply
Report
0
0
Florin Dan
Florin Dan     crystal
This comment was minimized by the moderator on the site
Hi crystal, thank you for your reply, usernane is enviroment usernane, and i have done it, i did use a if function and exit sub.
Thank you so much.
Reply
0
0
Brent
Brent
This comment was minimized by the moderator on the site
Hello - How can I include cell data in the email "cc" field?
Reply
0
0
crystal
crystal     Brent
This comment was minimized by the moderator on the site
Hi Brent,
Supposing you want to include the value in cell a7 in the email "cc" field, please try the below VBA.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

'Updated by Extendoffice 20200628

Dim xOutApp As Object

Dim xMailItem As Object

Dim xName As String

On Error Resume Next

Set xOutApp = CreateObject("Outlook.Application")

Set xMailItem = xOutApp.CreateItem(0)

xName = ActiveWorkbook.FullName

With xMailItem

.To = "Email Address"

.CC = Range("a7").Value

.Subject = "The workbook has been saved"

.Body = "Hi," & Chr(13) & Chr(13) & "File is now updated."

.Attachments.Add xName

.Display

'.send

End With

Set xMailItem = Nothing

Set xOutApp = Nothing

End Sub
Reply
Report
0
0
Lena
Lena
This comment was minimized by the moderator on the site
Hi! Thanks a lot for this guideline :-) I would like to do something more in this code - send an email based on the countries. It means that I have to create commands with if and select. Right? I have already delete the attachment from email. I would like to add the link with path into folder instead. But when macro run, command is not valid :-(
I appreciate each help how to add it there.
Reply
0
0
Mike
Mike
This comment was minimized by the moderator on the site
Is there a way to have the automated email be auto encrypted?
Reply
0
0
crystal
crystal     Mike
This comment was minimized by the moderator on the site
Hi Mike,
Sorry can't help to solve this problem. Thank you for your comment.
Reply
Report
0
0
mayle4pam
mayle4pam
This comment was minimized by the moderator on the site
How would this be handled for an Office 365 document. It is automatically saving.
Reply
0
0
Chris
Chris
This comment was minimized by the moderator on the site
Hi, nice article! One thing ive been trying to achieve with this is to attach the current state of the workbook to the email.

At the moment, it only sends the original state of the file and doesnt include any changes the user would have made.

Any ideas on how to implement this using a macro?
Reply
0
0
crystal
crystal     Chris
This comment was minimized by the moderator on the site
Hi Chris,
The code has been updated with the problem solved, please have a try. Thank you for your comment.
Reply
Report
0
0
Tin Hwee Lee
Tin Hwee Lee     crystal
This comment was minimized by the moderator on the site
Hi Chris,

I have chanced upon the same issue.
Currently the codes are used in the "beforesave" module.
Which means that the email will send the spreadsheet that is before saved.

There is another module "aftersave".
I applied the code in this module and it worked like a charm.
Reply
0
0
Bob
Bob
This comment was minimized by the moderator on the site
Dear all, I would like to ask how to attach functional link to certain folder on server, if I paste the link, it appears in the workbook just like plain text and so it doensn't work in received e-mail, how can I turn it into link, so recepiants can click on it?
I would like to use this way instead sending enclosed excel file.
Thanks for advice
Reply
Report
0
0
Grant
Grant     Bob
This comment was minimized by the moderator on the site
I put this in the body of the email and it worked for me...
"file:///Z:\dir1\dir2\dir3\Test1.xlsm"
Reply
0
0
crystal
crystal     Bob
This comment was minimized by the moderator on the site
Hi Robert,
Sorry can鈥檛 help with this, welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
Reply
Report
0
0
Antonio
Antonio
This comment was minimized by the moderator on the site
Gracias. Consulta: utilizando esta misma rutina, como podr铆a enviar el correo a un correo espec铆fico dependiendo el valor de otra celda?
Reply
0
0
Jermaine
Jermaine
This comment was minimized by the moderator on the site
How to automate the email notification in VBA based on Date range, without having to see the pop up for security permission to allow VBA to send the email.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
脳
Rate this post:
Reset
0   Characters
Suggested Locations