Gửi email Outlook bằng Excel VBA Macro hoặc PowerShell

Có nhiệm vụ sắp xếp thư theo danh sách e-mail của người dùng trong bảng tính Excel. Mỗi thông báo e-mail phải chứa một số dữ liệu cụ thể cho từng người dùng và tệp cá nhân cũng phải được đính kèm. Trong bài viết này, chúng ta sẽ xem xét cách sử dụng cấu hình Outlook để tự động gửi e-mail đến danh sách người nhận từ tệp Excel bằng cách sử dụng macro VBA hoặc tập lệnh PowerShell.

Giả sử, bạn có một tệp Excel với các cột sau:

Email | Full Name | Last Password Change Date | Account status

Nhiệm vụ của tôi là sử dụng mẫu này để gửi email cho mọi người trong danh sách Excel:

Excel VBA Macro để gửi email qua Outlook

Đây là một macro gửi thư VBA (Visual Basic for Applications) nhỏ có thể được tạo trực tiếp trong tài liệu Excel.

Tạo macro mới: Chế độ xem -> Macro. Chỉ định tên của macro (send_email) và nhấp vào Tạo:

Sao chép và dán đoạn mã sau vào trình soạn thảo VBA xuất hiện (Tôi đã thêm tất cả các nhận xét cần thiết vào đó). Để tự động hóa việc gửi email, tôi sẽ sử dụng

CreateObject (“Outlook.Application”)

chức năng, cho phép tạo và sử dụng một đối tượng Outlook trong các tập lệnh VBA.

Sub send_email()
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
' Subject
strSubj = "Your account status on a-d.site domain"
On Error GoTo dbg
' Create a new Outlook object
Set olApp = CreateObject("Outlook.Application")
For iCounter = 2 To WorksheetFunction.CountA(Columns(1))
' Create a new item (email) in Outlook
Set olMailItm = olApp.CreateItem(0)
strBody = ""
useremail = Cells(iCounter, 1).Value
FullUsername = Cells(iCounter, 2).Value
Status = Cells(iCounter, 4).Value
pwdchange = Cells(iCounter, 3).Value
'Make the body of an email
strBody = "Dear " & FullUsername & vbCrLf
strBody = strBody & " Your account in a-d.site domain is in" & Status & “ state” & vbCrLf
strBody = strBody & "The date and time of the last password change is" & pwdchange & vbCrLf
olMailItm.To = useremail
olMailItm.Subject = strSubj
olMailItm.BodyFormat = 1
' 1 - text format of an email, 2 -  HTML format
olMailItm.Body = strBody
'Add an attachment (filename format is username@a-d.site.txt). Comment out the following line if you do not need the attachments
olMailItm.Attachments.Add ("C:\ps\" & useremail & ".txt")
olMailItm.Send
Set olMailItm = Nothing
Next iCounter
Set olApp = Nothing
dbg:
'Display errors, if any
If Err.Description <> "" Then MsgBox Err.Description
End Sub

Lưu tệp Excel này dưới dạng .xlsm (một định dạng sổ làm việc Excel hỗ trợ macro). Để gửi email, hãy chọn quy trình đã tạo (macro) mà bạn đã tạo và nhấp vào Chạy.

Macro VBA lặp qua tất cả các hàng trong bảng tính Excel, tạo và gửi thông báo tới từng người nhận trong danh sách. Thông báo e-mail đã gửi được lưu trữ trong thư mục Mục đã gửi trong Outlook.

olMailItm.Send

olMailItm.SentOnBehalfOfName = "yoursecondemail@a-d.site"

Gửi Email từ Outlook bằng PowerShell

Trong PowerShell, bạn có thể sử dụng lệnh ghép ngắn Send-MailMessage để gửi e-mail. Tuy nhiên, nó yêu cầu bạn xác thực với máy chủ thư và nó không hỗ trợ các phương thức xác thực hiện đại, chẳng hạn như OAuth và Microsoft Modern Authentication. Vì vậy, việc gửi e-mail sẽ dễ dàng hơn nhiều nếu bạn đã định cấu hình cấu hình Outlook trên máy tính của mình.

Dưới đây là ví dụ về tập lệnh PowerShell đọc dữ liệu từ tệp Excel và sử dụng cấu hình Outlook để gửi e-mail cho từng người dùng:

# open the Excel file
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\user_list.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Sheet1")
# Get the number of filled rows in an xlsx file
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Loop through all the rows in column 1, starting from the second row (these cells contain the usernames and e-mails).
for($i=2;$i -le $rowcount;$i++){
$useremail = $ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
$FullUsername =  $ExcelWorkSheet.Columns.Item(2).Rows.Item($i).Text
$Status =  $ExcelWorkSheet.Columns.Item(4).Rows.Item($i).Text
$pwdchange = $ExcelWorkSheet.Columns.Item(3).Rows.Item($i).Text
# Generate message body text
$strSubj = " Your account status on a-d.site domain "
$strBody = "Dear " + $FullUsername
$strBody = $strBody + " `r`n Your account in a-d.site domain is in " + $Status
$strBody = $strBody + "`r`n The date and time of the last password change is : " +  $pwdchange
$strfile="C:\ps\" + $useremail + ".txt"
# We assume that Outlook is running, if it is not you will need to start it with the command $outlook = new-object -comobject outlook.application
$outlook = [Runtime.InteropServices.Marshal]::GetActiveObject("Outlook.Application")
$email = $outlook.CreateItem(0)
$email.To = $useremail
$email.Subject = $strSubj
$email.Body =  $strBody
# Attach a file (if necessary)
$email.Attachments.add($strfile)
#send the e-mailmessage
$email.Send()
}
$ExcelWorkBook.close($true)

Tập lệnh PowerShell này giả định rằng Outlook đang chạy trên máy tính của bạn. Tập lệnh tạo chủ đề và nội dung của e-mail cho từng địa chỉ SMTP của người nhận trong tệp XLSX và đính kèm tệp. Sau đó gửi e-mail.