Gửi email cho 5 đến 10 người cùng một lúc thường có thể quản lý được. Gửi email cho 30, 50 hoặc 500 người với tên, ngày tháng, ưu đãi, lời nhắc hoặc ghi chú theo dõi hơi khác nhau sẽ khiến quá trình này trở nên chậm chạp và dễ xảy ra lỗi. Đó là lúc Excel và VBA trở thành sự kết hợp mạnh mẽ. Với bảng tính có cấu trúc tốt và mã VBA, bạn có thể biến các hàng trong bảng tính thành email Outlook được cá nhân hóa, hoàn chỉnh với các dòng chủ đề, nội dung văn bản và tệp đính kèm tùy chọn tùy chỉnh.
Trong hướng dẫn này, chúng tôi sẽ trình bày cách xây dựng Trình tạo Email Excel bằng cách sử dụng VBA để tạo các thông báo Outlook được cá nhân hóa từ các hàng trong bảng tính. Mục tiêu là tự động hóa các email Outlook được cá nhân hóa dựa trên dữ liệu được lưu trữ trong mỗi hàng.
Bước 1:Thiết lập bảng tính Excel của bạn
Cấu trúc dữ liệu của bạn để VBA có thể đọc nó dễ dàng. Chúng ta sẽ giả sử một bảng tính đơn giản có tên “Dữ liệu” với các cột sau, bắt đầu từ hàng 2 và sử dụng hàng 1 cho tiêu đề:
Tạo một bảng tính như thế này:
Bạn có thể điều chỉnh các cột này cho phù hợp với trường hợp sử dụng của mình nhưng hãy giữ tiêu đề rõ ràng và nhất quán.
Bước 2:Thiết kế mẫu email của bạn
Trước khi viết bất kỳ mã VBA nào, hãy quyết định email sẽ trông như thế nào.
Đây là mẫu xác nhận đơn hàng đơn giản:
Mẫu chủ đề:
Your Order Confirmation - #{OrderID}
Mẫu nội dung:
<p>Dear {Name},</p>
<p>Thank you for your recent purchase!</p>
<p>We are happy to confirm your order details:</p>
<ul>
<li><strong>Order ID:</strong> {OrderID}</li>
<li><strong>Total Amount:</strong> {Amount}</li>
</ul>
<p>Your order is now being processed and should ship within 2–3 business days.</p>
<p>If you have any questions, feel free to reply to this email.</p>
<p>Best regards,<br>
Shamima<br>
Customer Support Team<br>
Your Company Name<br>
Phone: +880-XXX-XXXXXXX</p>
- Phần giữ chỗ trong dấu ngoặc nhọn là các giá trị được lấy từ mỗi hàng Excel.
- Trong VBA, bạn sẽ thay thế các phần giữ chỗ đó bằng các giá trị ô thực tế.
Nếu mẫu của bạn phức tạp, bạn có thể lưu trữ nó trong một trang tính riêng hoặc đọc nó từ một tệp. Để đơn giản, chúng tôi sẽ lưu trữ nó trong một ô.
Bước 3:Tìm hiểu tài liệu tham khảo Outlook
Trước khi viết bất kỳ mã nào, cần hiểu cách Excel có thể kết nối với thư viện đối tượng của Outlook.
- Mở Trình soạn thảo VBA.
- Đi tới Nhà phát triển tab>> chọn Visual Basic hoặc nhấn Alt + F11 .
- Chuyển tới Công cụ menu>> chọn Tài liệu tham khảo .
- Cuộn xuống và kiểm tra “Thư viện đối tượng Microsoft Outlook XX.X” nếu bạn muốn sử dụng các loại đối tượng dành riêng cho Outlook và liên kết sớm.
- Nhấp vào OK .
Tham chiếu này cung cấp cho VBA quyền truy cập vào mô hình đối tượng đầy đủ của Outlook, bao gồm các mục thư, tệp đính kèm và thư mục. Tuy nhiên, các ví dụ về mã trong hướng dẫn này sử dụng liên kết muộn với CreateObject(“Outlook.Application”) , vì vậy việc đặt tham chiếu Outlook là tùy chọn cho mã được hiển thị ở đây.
Lưu ý bảo mật: Việc tự động hóa email có thể kích hoạt các lời nhắc bảo mật của Outlook. Trong nhiều môi trường, hành vi này phụ thuộc vào cài đặt bảo mật Outlook và chính sách tổ chức. Luôn kiểm tra macro của bạn trong môi trường an toàn trước khi sử dụng macro với người nhận thực sự.
Bước 4:Viết mã VBA để tạo email Outlook được cá nhân hóa
- Đi tới Nhà phát triển tab>> chọn Visual Basic .
- Đi tới Chèn menu>> chọn Mô-đun .
- Dán mã VBA của bạn vào mô-đun mới.
- Tập lệnh này lặp qua các hàng và tạo email thông qua Outlook.
Mã VBA:
Sub SendPersonalizedEmails()
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim subject As String
Dim body As String
Dim recipient As String
Dim customerName As String
Dim orderID As String
Dim amount As String
On Error GoTo ErrorHandler
Set OutlookApp = CreateObject("Outlook.Application")
Set ws = ThisWorkbook.Sheets("Data") ' Change sheet name if different
' Find last row (based on column A - Email)
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
recipient = Trim(ws.Cells(i, 1).Value) ' Email
customerName = Trim(ws.Cells(i, 2).Value) ' Name
subject = Trim(ws.Cells(i, 3).Value) ' Subject
orderID = Trim(ws.Cells(i, 4).Value) ' OrderID
amount = Trim(ws.Cells(i, 5).Value) ' Amount
' Skip row if no valid email
If recipient = "" Or InStr(recipient, "@") = 0 Then GoTo NextRow
' Personalize subject
subject = Replace(subject, "#{OrderID}", orderID)
' Build body directly from cell values
body = "Dear " & customerName & "," & vbCrLf & vbCrLf & _
"Thank you for your order." & vbCrLf & vbCrLf & _
"Here are your order details:" & vbCrLf & _
"Order ID: " & orderID & vbCrLf & _
"Amount: " & amount & vbCrLf & vbCrLf & _
"We appreciate your business and will contact you if any further action is needed." & vbCrLf & vbCrLf & _
"Best regards," & vbCrLf & _
"Customer Support Team"
' Create & send email
Set OutlookMail = OutlookApp.CreateItem(0) ' olMailItem
With OutlookMail
.To = recipient
.Subject = subject
.Body = body
.Display ' Shows the email so you can review it manually
'.Send ' Uncomment when you're ready to send automatically
End With
Set OutlookMail = Nothing
NextRow:
Next i
MsgBox "All emails processed. Check Outlook for the displayed messages.", vbInformation
CleanUp:
Set OutlookMail = Nothing
Set OutlookApp = Nothing
Exit Sub
ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbCritical
Resume CleanUp
End Sub
Mã VBA này kết nối với Outlook và lặp qua từng hàng được sử dụng trong bảng tính. Nó đọc các giá trị như tên, email, chủ đề, ID đơn hàng và số tiền. Sau đó, nó xây dựng chủ đề và nội dung được cá nhân hóa bằng cách sử dụng trực tiếp các giá trị đó. Cuối cùng, nó có thể hiển thị email, lưu dưới dạng bản nháp với một sửa đổi nhỏ hoặc gửi nó. Đối với hầu hết quy trình công việc của doanh nghiệp, bắt đầu bằng .Display là tốt nhất vì nó cho phép bạn xem lại từng tin nhắn trước khi gửi đi.
Bước 5:Biến thông điệp của bạn thành hệ thống mẫu có thể tái sử dụng
Mã hóa cứng một nội dung email hoạt động nhưng Trình tạo Email Excel linh hoạt hơn sẽ lưu trữ mẫu bên trong một trang tính. Thêm một ô cho mẫu email, sau đó dán mẫu cơ sở vào ô đó dưới dạng văn bản. Sử dụng phần giữ chỗ trong dấu ngoặc nhọn để thay thế động:
- Chèn mã vào một Mô-đun khác .
Mã VBA:
Sub SendPersonalizedEmails_Template()
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim emailTemplate As String
Dim subject As String
Dim body As String
Dim recipient As String
Dim customerName As String
Dim orderID As String
Dim amount As String
On Error GoTo ErrorHandler
Set OutlookApp = CreateObject("Outlook.Application")
Set ws = ThisWorkbook.Sheets("Data") ' Change sheet name if different
' Find last row (based on column A - Email)
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Get template from cell F2 (adjust cell reference if you placed it elsewhere)
emailTemplate = ws.Range("F2").Value
If emailTemplate = "" Then
MsgBox "Email template is empty. Please add it to cell F2.", vbExclamation
Exit Sub
End If
For i = 2 To lastRow
recipient = Trim(ws.Cells(i, 1).Value)
customerName = Trim(ws.Cells(i, 2).Value)
subject = Trim(ws.Cells(i, 3).Value)
orderID = Trim(ws.Cells(i, 4).Value)
amount = Trim(ws.Cells(i, 5).Value)
' Skip row if no valid email
If recipient = "" Or InStr(recipient, "@") = 0 Then GoTo NextRow
' Personalize subject
subject = Replace(subject, "#{OrderID}", orderID)
' Personalize body
body = emailTemplate
body = Replace(body, "{Name}", customerName)
body = Replace(body, "{OrderID}", orderID)
body = Replace(body, "{Amount}", amount)
' Add more Replace() lines for other placeholders if needed
' Create & send email
Set OutlookMail = OutlookApp.CreateItem(0) ' olMailItem
With OutlookMail
.To = recipient
.Subject = subject
.HTMLBody = body
'.CC = "[email protected]"
'.Attachments.Add "C:\Invoices\" & orderID & ".pdf" ' Optional
.Display ' Shows the email so you can review it manually
'.Send ' Uncomment when you're ready to send automatically
End With
Set OutlookMail = Nothing
NextRow:
Next i
MsgBox "All emails processed. Check Outlook for the displayed messages.", vbInformation
CleanUp:
Set OutlookMail = Nothing
Set OutlookApp = Nothing
Exit Sub
ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbCritical
Resume CleanUp
End Sub
- Nhấp vào Chạy biểu tượng để xem trước các email theo mẫu.
Giải thích về mã khóa:
- Thiết lập Outlook: CreatObject(“Outlook.Ứng dụng”) tạo một phiên bản Outlook bằng cách sử dụng tính năng liên kết muộn, tính năng này linh hoạt hơn trên các phiên bản Office.
- Lặp qua các hàng: Macro tự động tìm hàng cuối cùng và lặp lại từ hàng 2 để bỏ qua tiêu đề.
- Cá nhân hóa mẫu: Thay thế chức năng hoán đổi các phần giữ chỗ, chẳng hạn như {Name với dữ liệu cụ thể theo hàng. Đây là cốt lõi của hệ thống mẫu dựa trên VBA. Thêm nhiều Thay thế dòng cho các trường bổ sung.
- Tạo email: Sử dụng .HTMLBody để có định dạng phong phú như văn bản in đậm và liên kết. Nếu mẫu của bạn là văn bản thuần túy, hãy sử dụng .Body thay vào đó.
- Đang gửi: .Gửi gửi tin nhắn tự động trong khi .Display mở nó ra để xem xét, điều này rất hữu ích trong quá trình thử nghiệm.
- Xử lý lỗi: Các ví dụ bao gồm xử lý lỗi cơ bản và bỏ qua các hàng thiếu địa chỉ email. Trong quá trình sản xuất, bạn có thể muốn thêm tính năng ghi nhật ký và xác thực chi tiết hơn.
Bước 6:Kiểm tra và chạy macro
- Lưu sổ làm việc của bạn dưới dạng .xlsm tập tin.
- Điền dữ liệu mẫu. Sử dụng địa chỉ email của riêng bạn để kiểm tra nhằm tránh gửi những tin nhắn không mong muốn.
- Gán macro cho một nút hoặc đi tới Nhà phát triển tab>> chọn Macro .
- Chọn GửiEmail được cá nhân hóa hoặc GửiPersonalizedEmails_Template>> nhấp vào Chạy .
- Outlook có thể nhắc cấp phép. Nếu có, chỉ cho phép truy cập sau khi xác nhận thiết lập thử nghiệm của bạn.
- Kiểm tra thư được hiển thị hoặc thư mục Mục đã gửi của bạn trong Outlook, tùy thuộc vào việc bạn có sử dụng .Display hay không hoặc .Gửi .
Mẹo kiểm tra: Bắt đầu với .Display thay vì .Gửi để bạn có thể xem trước email mà không cần gửi chúng.
Tùy chỉnh mẫu nâng cao
- Nhiều phần giữ chỗ: Nếu bạn có nhiều cột hơn, chẳng hạn như ngày đến hạn, hãy thêm các biến như dueDate =ws.Cells(i, 6).Value và sử dụng body =Thay thế(body, “{DueDate}”,dueDate) .
- Nội dung có điều kiện: Sử dụng Nếu câu lệnh cho các phần động:
If customField = "VIP" Then body = body & "<p>Exclusive offer for you!</p>" End If
- Mẫu từ tệp: Thay vì lưu trữ mẫu trong một ô, hãy đọc mẫu từ văn bản hoặc tệp HTML:
Dim filePath As String filePath = "C:\Template.html" Open filePath For Input As #1 emailTemplate = Input$(LOF(1), 1) Close #1
- Cải tiến HTML: Bạn có thể nhúng hình ảnh hoặc liên kết vào mẫu, chẳng hạn như
hoặc Nhấp vào đây
, sau đó thay thế {Link động.
Khắc phục sự cố thường gặp
- Không tìm thấy Outlook: Đảm bảo Outlook được cài đặt trên hệ thống của bạn.
- Lời nhắc bảo mật: Outlook có thể hạn chế quyền truy cập theo chương trình. Điều này được kiểm soát bởi Outlook và cài đặt bảo mật tổ chức, không chỉ cài đặt macro Excel.
- Lỗi khi gửi: Thêm xử lý lỗi như:
On Error GoTo ErrorHandler ' ... code ... ErrorHandler: MsgBox "Error: " & Err.Description
- Hiệu suất: Đối với danh sách email lớn, bạn có thể thêm khoảng thời gian trễ ngắn giữa các thư:
Application.Wait Now + TimeValue("00:00:01")
- Vấn đề hiển thị HTML: Kiểm tra mẫu cẩn thận và đảm bảo phần giữ chỗ không can thiệp vào cấu trúc HTML.
Thiết lập này có quy mô phù hợp cho việc nhắn tin được cá nhân hóa hàng loạt. Bạn có thể mở rộng hơn nữa bằng cách thêm tệp đính kèm, ghi lại email đã gửi vào Excel hoặc lưu trữ nhiều mẫu cho các tình huống khác nhau.
Kết luận
Bằng cách làm theo các bước này, bạn có thể xây dựng Trình tạo email Excel sử dụng VBA để tạo thư Outlook được cá nhân hóa từ các hàng trong bảng tính. Đây là một ví dụ thực tế về cách VBA có thể biến một bảng tính thành một công cụ tự động hóa email nhẹ. Thay vì viết các tin nhắn Outlook lặp đi lặp lại bằng tay, bạn có thể tạo các mẫu có thể sử dụng lại và để Excel điền các chi tiết được cá nhân hóa từ mỗi hàng. Điều đó làm cho quá trình nhanh hơn, nhất quán hơn và dễ dàng mở rộng quy mô hơn. Sau khi xây dựng phiên bản cơ bản, bạn có thể mở rộng phiên bản đó bằng định dạng HTML, nhiều mẫu, logic có điều kiện, xử lý tệp đính kèm và theo dõi trạng thái để tạo một hệ thống email được cá nhân hóa hoàn chỉnh.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!