Send Outlook Email Using Excel VBA Macro or PowerShell Ranjan.info

The Excel spreadsheet had the function of organizing mailings according to a list of user e-mails. Each e-mail message must contain data unique to each user and must also have an individual file attached. In this article, we will see how to use Outlook profile to automatically send e-mail from Excel file to a list of recipients using VBA macro or PowerShell script.

Important, The Outlook mail profile on your computer must be configured for both methods of sending email. This mailbox (and this e-mail address) will be used to send messages.

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 an excel list:

Subject: Status of your account on woshub.com domain
Body: Dear %FullUsername%,
Your account in woshub.com domain is in %status% status
The date and time of the last password change is %pwdchange%

excel vba macro to send email through outlook

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

Create a new macro: Look , macro, Specify the name of the macro (send email) and click create,

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

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 email, select the created procedure (macro) you just created and click Run.

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

Send an email from Outlook using PowerShell

In PowerShell, you can use the Send-MailMessage cmdlet to send email. 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 sending e-mail is very easy if you have Outlook profile configured on your computer.

Here’s an example of a PowerShell script that reads data from an Excel file and uses an Outlook profile to send 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 e-mail.