שליחת הודעות דואר אלקטרוני של Outlook באמצעות Excel VBA מאקרו או PowerShell

הייתה משימה לארגן דיוורים בהתאם לרשימת הודעות הדואר האלקטרוני של המשתמשים בגיליון אקסל. כל הודעת דואר אלקטרוני צריכה להכיל נתונים ספציפיים לכל משתמש ויש לצרף גם קובץ אישי. במאמר זה, נבחן כיצד להשתמש בפרופיל Outlook כדי לשלוח באופן אוטומטי דואר אלקטרוני לרשימת נמענים מקובץ Excel באמצעות מאקרו VBA או קובץ script של PowerShell.

נניח שיש לך קובץ Excel עם העמודות הבאות:

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

המשימה שלי היא להשתמש בתבנית זו כדי לשלוח דואר אלקטרוני לכל האנשים ברשימת Excel:

מאקרו VBA של Excel לשליחת דואר אלקטרוני דרך Outlook

להלן מאקרו דיוור קטן של VBA (Visual Basic for Applications) שניתן ליצור ישירות במסמך Excel.

יצירת מאקרו חדש: הצג -> פקודות מאקרו. ציין את שם המאקרו (send_email) ולחץ על צור:

העתק והדבק את הקוד הבא לעורך VBA שמופיע (הוספתי אליו את כל ההערות הדרושות). כדי להפוך את שליחת הודעות הדוא"ל לאוטומטית, אשתמש ב

CreateObject (“Outlook.Application”)

פונקציה, המאפשרת ליצור אובייקט Outlook ולהשתמש בו בתוך קבצי script של 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

שמור קובץ Excel זה בשם . xlsm (תבנית חוברת עבודה של Excel התומכת בפקודות מאקרו). כדי לשלוח הודעות דואר אלקטרוני, בחר את ההליך שנוצר (המאקרו) שיצרת ולחץ על הפעל.

המאקרו VBA חוזר על עצמו בכל השורות בגיליון האלקטרוני של Excel, יוצר ושולח הודעה לכל נמען ברשימה. הודעות דואר אלקטרוני שנשלחו מאוחסנות בתיקיה פריטים שנשלחו ב- Outlook.

olMailItm.Send

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

שליחת דואר אלקטרוני מ- Outlook באמצעות PowerShell

ב- PowerShell, באפשרותך להשתמש ב- cmdlet Send-MailMessage כדי לשלוח דואר אלקטרוני. עם זאת, הוא דורש אימות לשרת הדואר ואינו תומך בשיטות אימות מודרניות, כגון OAuth ואימות מודרני של Microsoft. אז זה הרבה יותר קל לשלוח דואר אלקטרוני אם יש לך פרופיל Outlook מוגדר במחשב שלך.

להלן דוגמה של קובץ script של PowerShell שקורא נתונים מקובץ Excel ומשתמש בפרופיל Outlook כדי לשלוח דואר אלקטרוני לכל משתמש:

# 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)

קובץ script זה של PowerShell מניח ש- Outlook פועל במחשב שלך. קובץ ה- Script יוצר את הנושא ואת גוף הדואר האלקטרוני עבור כל כתובת SMTP של נמען בקובץ XLSX ומצרף את הקובץ. לאחר מכן שולח את הדואר האלקטרוני.