Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / MS Office / Send Outlook Emails Using Excel VBA Macro or PowerShell

April 12, 2023 MS OfficeOutlookPowerShell

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.

Contents:
  • Excel VBA Macro to Send Email Through Outlook
  • Send an Email from Outlook Using PowerShell

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

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

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

Sending Email to a List of Recipients Using Excel and Outlook

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

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

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:

send_email excle macro

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

vba macro to send email to the list of of recipients in excel spreadsheet

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.

run vba macro

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.

You must grant SendAs/Sent on behalf permissions if you want to send an email on behalf of another user or shared mailbox (if you are using Exchange) and add the following code to the script (before olMailItm.Send).

olMailItm.SentOnBehalfOfName = "[email protected]"

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)

powershell script to send email from outlook configured profile

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.

20 comments
5
Facebook Twitter Google + Pinterest
previous post
Fixing the Read-Only File System Error on Linux
next post
0x80244010 Exceeded Max Server Round Trips: Windows Update Error

Related Reading

Installing Language Pack in Windows 10/11 with PowerShell

September 15, 2023

Configure Email Forwarding for Mailbox on Exchange Server/Microsoft...

September 14, 2023

How to View and Change BIOS (UEFI) Settings...

September 13, 2023

How to Create UEFI Bootable USB Drive to...

September 11, 2023

Managing Windows Firewall Rules with PowerShell

August 31, 2023

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • How to Use Ansible to Manage Windows Machines

    September 25, 2023
  • Installing Language Pack in Windows 10/11 with PowerShell

    September 15, 2023
  • Configure Email Forwarding for Mailbox on Exchange Server/Microsoft 365

    September 14, 2023
  • How to View and Change BIOS (UEFI) Settings with PowerShell

    September 13, 2023
  • How to Create UEFI Bootable USB Drive to Install Windows

    September 11, 2023
  • Redirect HTTP to HTTPS in IIS (Windows Server)

    September 7, 2023
  • Add an Additional Domain Controller to an Existing AD Domain

    September 6, 2023
  • How to Install an SSL Certificate on IIS (Windows Server)

    September 5, 2023
  • Managing Windows Firewall Rules with PowerShell

    August 31, 2023
  • Fixing ‘The Network Path Was Not Found’ 0x80070035 Error Code on Windows

    August 30, 2023

Follow us

  • Facebook
  • Twitter
  • Telegram
Popular Posts
  • Fix: Signature Button Not Working in Outlook 2019/2016/365
  • Outlook Keeps Asking for Password on Windows
  • Configuring KMS License Server for Office 2021/2019/2016 Volume Activation
  • How to Completely Uninstall Previous Versions of Office with Removal Scripts
  • How to Extend Office 2021/2019/2016 & Office 365 Trial Period
  • Installing an Open Source KMS Server (Vlmcsd) on Linux
  • Blank Sign-in Screen in Office 365 Apps (Outlook, Teams, etc.)
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top