Cách gửi email từ bảng tính Excel bằng tập lệnh VBA
Để gửi email từ Microsoft Excel, chỉ cần một vài tập lệnh đơn giản. Thêm chức năng này vào bảng tính của bạn và bạn có thể hoàn thành nhiều việc hơn trong Excel.
Macro Excel có thể thực hiện nhiều điều tương tự mà các tập lệnh VBA có thể làm mà không cần nhiều kiến thức về lập trình. VBA cho phép bạn triển khai các quy trình nâng cao hơn, như tạo báo cáo bảng tính với tất cả thông tin PC của bạn.
Bạn muốn xem hướng dẫn này dưới dạng video? Chúng tôi đã có bạn bảo hiểm!
Mục Lục
Tại sao phải gửi Email từ Excel?
Có rất nhiều lý do khiến bạn có thể muốn gửi email từ bên trong Microsoft Excel.
Có thể bạn có nhân viên cập nhật tài liệu hoặc bảng tính hàng tuần và bạn muốn nhận được thông báo qua email khi họ cập nhật. Hoặc bạn có thể có một bảng tính các địa chỉ liên hệ và bạn muốn gửi một email cho tất cả họ cùng một lúc.
Có thể bạn đang nghĩ rằng việc tạo kịch bản cho một chương trình phát sóng email từ Excel sẽ phức tạp. Đó hoàn toàn không phải là trường hợp. Kỹ thuật trong bài viết này sử dụng một tính năng đã có trong Excel VBA từ lâu, Đối tượng dữ liệu cộng tác (CDO).
CDO là một thành phần nhắn tin được sử dụng trong Windows từ những thế hệ hệ điều hành rất sớm. Nó từng được gọi là CDONTS, sau đó với sự ra đời của Windows 2000 và XP, được thay thế bằng “CDO cho Windows 2000.” Thành phần này đã được bao gồm trong cài đặt VBA của bạn trong Microsoft Word hoặc Excel và đã sẵn sàng để sử dụng.
Việc sử dụng thành phần này làm cho việc gửi email từ bên trong các sản phẩm Windows với VBA trở nên cực kỳ dễ dàng. Trong ví dụ này, bạn sẽ sử dụng thành phần CDO trong Excel để gửi email gửi kết quả từ một ô Excel cụ thể.
Bước 1: Chuẩn bị tài khoản Gmail của bạn
Để gửi email từ Microsoft Excel, chúng tôi sẽ sử dụng Gmail, mặc dù bạn có thể tùy chỉnh macro bên dưới để hoạt động với các ứng dụng email khác. Lưu ý rằng Gmail không còn cho phép ứng dụng của bên thứ ba truy cập nữa, có nghĩa là trước tiên bạn sẽ phải bật xác thực 2 bước của Gmail.
Từ trang Bảo mật trong tài khoản Google của bạn, trong Đăng nhập vào Googlenhấp chuột Mật khẩu ứng dụng. Trên màn hình Mật khẩu ứng dụng, hãy tìm Chọn ứng dụng menu thả xuống và chọn Thư. Từ Chọn thiết bịlựa chọn Máy tính Windows. Sau đó nhấp vào PHÁT RA.
Ghi lại mật khẩu ứng dụng gồm 16 ký tự; bạn sẽ cần nó khi định cấu hình macro.
Bước 2: Tạo Macro VBA
Mẹo: Trước khi bắt đầu, hãy lưu tệp Excel mà bạn sẽ làm việc với Macro-Enabled Workbooktức là ở định dạng XLSM.
Đầu tiên, chúng ta sẽ cần tab Nhà phát triển Excel. Nếu bạn không thấy nó, đây là cách để bật nó:
- Đi đến Tệp> Tùy chọn.
- Dưới Tùy chỉnh Ribbon> Tab chínhkiểm tra Nhà phát triển quyền mua.
- Nhấp chuột ĐƯỢC RỒI để lưu các thay đổi của bạn.
Bên trong tab Nhà phát triển của Excel, hãy nhấp vào Chèn trong hộp Điều khiển, rồi chọn một nút lệnh.
Vẽ nó vào trang tính và sau đó tạo macro mới cho nó bằng cách nhấp vào Macro trong dải băng Nhà phát triển.
Khi bạn nhấp vào Tạo ra nó sẽ mở trình soạn thảo VBA.
Thêm tham chiếu vào thư viện CDO bằng cách điều hướng đến Công cụ > Người giới thiệu trong trình soạn thảo.
Cuộn xuống danh sách cho đến khi bạn tìm thấy Microsoft CDO cho Thư viện Windows 2000. Đánh dấu hộp kiểm và nhấp vào ĐƯỢC RỒI.
Khi bạn nhấp vào ĐƯỢC RỒI, hãy ghi lại tên của hàm mà bạn đang dán tập lệnh. Bạn sẽ cần nó sau.
Bước 3: Định cấu hình Macro của bạn
Bây giờ bạn đã sẵn sàng để tạo các đối tượng thư và thiết lập tất cả các trường cần thiết để gửi email. Hãy nhớ rằng trong khi nhiều trường là tùy chọn, Từ và Đến Mục này bắt buộc. Dán tất cả các đoạn mã bên dưới vào Mô-đun1 (Mã) cửa sổ.
Đây là mã hoàn chỉnh trông như thế nào:
Sub Send_Emails()
Dim NewMail As CDO.Message
Dim mailConfig As CDO.Configuration
Dim fields As Variant
Dim msConfigURL As String
On Error GoTo Err:
'early binding
Set NewMail = New CDO.Message
Set mailConfig = New CDO.Configuration
'load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
'Set All Email Properties
With NewMail
.From = "username@gmail.com"
.To = "username@gmail.com"
.CC = ""
.BCC = ""
.Subject = "Send Email From an Excel Spreadsheet"
.TextBody = "This is the body of your email. And here is some added data:" & Str(Sheet1.Cells(2, 1))
.Addattachment "c:dataemail.xlsx" 'Optional file attachment; remove if not needed.
.Addattachment "c:dataemail.pdf" 'Duplicate the line for a second attachment.
End With
msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
With fields
.Item(msConfigURL & "/smtpusessl") = True 'Enable SSL Authentication
.Item(msConfigURL & "/smtpauthenticate") = 1 'SMTP authentication Enabled
.Item(msConfigURL & "/smtpserver") = "smtp.gmail.com" 'Set the SMTP server details
.Item(msConfigURL & "/smtpserverport") = 465 'Set the SMTP port Details
.Item(msConfigURL & "/sendusing") = 2 'Send using default setting
.Item(msConfigURL & "/sendusername") = "username@gmail.com" 'Your gmail address
.Item(msConfigURL & "/sendpassword") = "password" 'Your password or App Password
.Update 'Update the configuration fields
End With
NewMail.Configuration = mailConfig
NewMail.SendMsgBox "Your email has been sent", vbInformation
Exit_Err:
'Release object memory
Set NewMail = Nothing
Set mailConfig = Nothing
End
Err:
Select Case Err.Number
Case -2147220973 'Could be because of Internet Connection
MsgBox "Check your internet connection." & vbNewLine & Err.Number & ": " & Err.Description
Case -2147220975 'Incorrect credentials User ID or password
MsgBox "Check your login credentials and try again." & vbNewLine & Err.Number & ": " & Err.Description
Case Else 'Report other errors
MsgBox "Error encountered while sending email." & vbNewLine & Err.Number & ": " & Err.Description
End Select
Resume Exit_Err
End Sub
Và đây là các phần và trường bạn cần tùy chỉnh:
- Với NewMail: Phần này chứa tất cả các tham số để gửi email của bạn, bao gồm cả nội dung email của bạn. Các .Từ trường cần chứa địa chỉ Gmail của bạn, nhưng bạn có thể tự do đặt các trường khác theo cách bạn muốn. Đối với phần nội dung, bạn có thể ghép các thành phần của thư lại với nhau bằng cách sử dụng & chuỗi để chèn dữ liệu từ bất kỳ trang tính Microsoft Excel nào ngay vào thư email, giống như được hiển thị ở trên. Bạn cũng có thể đính kèm một hoặc nhiều tệp.
- Với các trường: Đây là nơi bạn định cấu hình cài đặt SMTP cho tài khoản Gmail của mình. Rời khỏi máy chủ SMTP và smtpserverport các trường như khi sao chép mã. Nhập tên người dùng Gmail của bạn và mật khẩu ứng dụng gồm 16 chữ số vào các trường tương ứng.
Bước 4: Kiểm tra Macro của bạn
Trong trình chỉnh sửa VBA, đi tới Run> Run Sub / User Form hoặc nhấn F5 để kiểm tra macro. Nếu email của bạn không thành công, bạn sẽ thấy thông báo lỗi. Nếu không, bạn sẽ thấy xác nhận rằng email của bạn đã được gửi thành công.
Nếu bạn nhận được một lỗi đọc Quá trình vận chuyển không kết nối được với máy chủđảm bảo bạn đã nhập đúng tên người dùng, mật khẩu, máy chủ SMTP và số cổng trong các dòng mã được liệt kê bên dưới Với các lĩnh vực.
Bước 5: Kết nối nút lệnh với tập lệnh của bạn
Để kết nối nút lệnh của bạn với tập lệnh này, hãy vào trình chỉnh sửa mã và nhấp đúp vào Sheet1 để xem mã VBA cho trang tính đó. Chọn nút của bạn, ví dụ: CommandButton1từ menu thả xuống ở bên trái và xác định hành động ở bên phải; Nhấp chuột làm. Sau đó gõ tên của hàm mà bạn đã dán đoạn mã ở trên; trong ví dụ của chúng tôi, đó là Send_Emails.
Khi bạn quay lại trang tính của mình ngay bây giờ, hãy nhấp vào nút để gửi email.
Dưới đây là một ví dụ về những gì thư sẽ trông như thế nào trong hộp thư đến của bạn:
Tiến xa hơn và tự động hóa toàn bộ quy trình
Tất cả đều tốt và tốt khi có thể gửi email từ Excel chỉ bằng một nút bấm. Tuy nhiên, bạn có thể muốn sử dụng chức năng này thường xuyên, trong trường hợp đó, bạn nên tự động hóa quy trình. Để làm như vậy, bạn sẽ cần thực hiện thay đổi đối với macro. Đi tới Trình soạn thảo Visual Basic và sao chép và dán toàn bộ mã bạn đã tập hợp cho đến nay.
Tiếp theo, nhấp đúp vào ThisWorkbook từ VBAProject hệ thống cấp bậc.
Từ hai trường thả xuống ở đầu cửa sổ mã, hãy chọn Sách bài tập và chọn Mở từ menu thả xuống Phương thức.
Dán tập lệnh email ở trên vào Sub Workbook_Open ().
Điều này sẽ chạy macro bất cứ khi nào bạn mở tệp Excel.
Tiếp theo, mở ra Bảng kế hoạch. Bạn sẽ sử dụng công cụ này để yêu cầu Windows tự động mở bảng tính theo các khoảng thời gian đều đặn, lúc đó macro của bạn sẽ chạy, gửi email.
Từ Hoạt động menu, chọn Tạo Tác vụ Cơ bản … và làm việc theo cách của bạn thông qua trình hướng dẫn cho đến khi bạn đến Hoạt động màn hình.
Lựa chọn Bắt đầu một chương trình và bấm vào Tiếp theo. Sử dụng Duyệt qua để tìm vị trí của Microsoft Excel trên máy tính của bạn hoặc sao chép và dán đường dẫn vào Chương trình / kịch bản đồng ruộng. Sau đó, nhập đường dẫn đến tài liệu Microsoft Excel của bạn vào Thêm đối số đồng ruộng. Hoàn thành trình hướng dẫn và lập lịch của bạn sẽ được thực hiện.
Bạn nên chạy thử nghiệm bằng cách lên lịch hành động trong vài phút trong tương lai, sau đó sửa đổi nhiệm vụ khi bạn có thể xác nhận rằng nó đang hoạt động.
Ghi chú: Bạn có thể phải điều chỉnh cài đặt Trung tâm tin cậy của mình để đảm bảo rằng macro chạy đúng cách.
Để làm như vậy, hãy mở bảng tính và điều hướng đến Tập tin > Tùy chọn > Trung tâm Tin tưởng. Từ đây, hãy nhấp vào Cài đặt Trung tâm Tin cậy …và trên màn hình tiếp theo, hãy đặt quay số radio thành Không bao giờ hiển thị thông tin về nội dung bị chặn.
Làm cho Microsoft Excel hoạt động cho bạn
Microsoft Excel là một công cụ cực kỳ mạnh mẽ, nhưng học cách tận dụng tối đa nó có thể hơi đáng sợ. Nếu bạn muốn thực sự thành thạo phần mềm, bạn cần phải hiểu rõ về VBA và đó không phải là nhiệm vụ nhỏ.
Tuy nhiên, kết quả đã tự nói lên điều đó. Với một chút kinh nghiệm VBA, bạn sẽ sớm có thể làm cho Microsoft Excel tự động thực hiện các tác vụ cơ bản, giúp bạn có thêm thời gian để tập trung vào những vấn đề cấp bách hơn.