Send Outlook Emails Using Excel VBA Macro or PowerShell

There was a task to organize mailings in accordance with the list of user e-mails in an Excel spreadsheet. Each e-mail message should contain some data specific to each user and a personal file should also be attached. In this article, we’ll look at how to use the Outlook profile to automatically send an e-mail to a list of recipients from Excel file using a VBA macro or PowerShell script.

Suppose, you have an Excel file with the following columns:

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

My task is to use this template to email everyone in the Excel list:

Excel VBA Macro to Send Email Through Outlook

Here’s a small VBA (Visual Basic for Applications) mailing macro that can be created directly in an Excel document.

Create a new macro: View -> Macros. Specify the name of the macro (send_email) and click Create:

Copy and paste the following code to the VBA editor that appears (I have added all the necessary comments to it). To automate the sending of emails, I’ll use the CreateObject (“Outlook.Application”) function, which allows an Outlook object to be created and used within VBA scripts.

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 woshub.com 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 woshub.com 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 [email protected]). 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

Save this Excel file as .xlsm (an Excel workbook format that supports macros). To send emails, select the created procedure (the macro) you have created and click Run.

The VBA macro iterates through all the rows in the Excel spreadsheet, generates and sends a message to each recipient in the list. Sent e-mail messages are stored in the Sent Items folder in Outlook.

Send an Email from Outlook Using PowerShell

In PowerShell, you can use the Send-MailMessage cmdlet to send e-mail. However, it requires that you authenticate to the mail server, and it doesn’t support modern authentication methods, such as OAuth and Microsoft Modern Authentication. So it’s much easier to send an e-mail if you have an Outlook profile configured on your computer.

Here is an example of a PowerShell script that reads data from an Excel file and uses an Outlook profile to send an e-mail to each user:

# 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 woshub.com domain "
$strBody = "Dear " + $FullUsername
$strBody = $strBody + " `r`n Your account in woshub.com 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)

This PowerShell script assumes that Outlook is running on your computer. The script generates the subject and body of the e-mail for each recipient SMTP address in the XLSX file and attaches the file. Then sends the e-mail.