Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • 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 2012
    • 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 / Sending Emails from Excel using VBA Macro and Outlook

May 15, 2018 MS OfficeOutlook

Sending Emails from Excel using VBA Macro and Outlook

I had got a task of sending emails to the users listed in an Excel spreadsheet. Each email had to contain some information individual for each user. I tried to do it using a VBA macro in Excel that sent emails from a configured Outlook profile on the computer. Here is my solution.

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 send such an e-mail from this template to each user in the 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%

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 made relevant comments to it). To automate the sending of emails, I’ll use CreateObject (“Outlook.Application”) function that allows to create and use an Outlook object inside VBA scripts.

Important. Outlook profile must be configured on the sending computer. This mailbox (and email address) will be used to send emails.

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 = 1 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
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 (a format of Excel workbook supporting macros). To send emails, select the created procedure (the macro) and click Run.

run vba macro

The macro will go through all rows on the Excel worksheet one by one, generate and send an email to the each recipient in the list.

19 comments
4
Facebook Twitter Google + Pinterest
previous post
Windows 10 1803 Update Creates an Additional OEM Partition
next post
How to Check Who Reset the Password of a User in Active Directory

Related Reading

Granting Send As and Send on Behalf Permissions...

December 21, 2022

How to Manually Configure Exchange or Microsoft 365...

December 5, 2022

Outlook Attachment Preview Error: This File Cannot Be...

November 16, 2022

Outlook: Your Server Does Not Support the Connection...

October 20, 2022

Configure Auto-Reply (Out of Office) Message in Exchange...

October 20, 2022

19 comments

Atanu Sarkar December 18, 2019 - 5:07 am

Hi,

Regardung – Sending Emails from Excel using VBA Macro and Outlook

How to use it as rowwise instead of columns wise – For iCounter = 1 To WorksheetFunction.CountA(Columns(1)) ?

Reply
admin January 15, 2020 - 9:26 am

You can try to use use the following vba code:
'get columns count
LastCol = ActiveSheet.UsedRange.Columns.Count
'loop through columns
For i = iCounter To LastCol
useremail = Cells(1,iCounter).Value
.........
next

Reply
Graham January 2, 2020 - 5:28 pm

This is just what I needed thank you, i the email address was in column G i beleive id change to :-useremail = Cells(iCounter, 7).Value?
What if there is a gap between 2 rows? how I make it to continue looking down all the column?

Reply
admin January 15, 2020 - 9:31 am

Do you mean that there are empty cells?
You can add an additional condition for checking that the cell is not empty:
For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
if Cells(iCounter, 7).Value <>“” then
{your email generation code}
next

Reply
Mustafa Aquil January 3, 2020 - 9:22 pm

I used this coding above and it only works if the coding for sending the email is on display.
The coding: olMailItm.Send is giving me an error saying that the outlook does not recognize. It only works on olMailItm.Display

Can anyone help ?

Thank you

Reply
admin January 15, 2020 - 9:28 am

Is Outlook installed on your computer? Have you set up your mailbox profile?

Reply
Tracy April 6, 2020 - 5:27 pm

I too am getting the error saying the that Oulook does not recognize one or more names. I went through each row to verify all address were correct (also checking for spaces and extra characters) but found no errors. I’m using Outlook 2016. PS. This macro was EXACTLY what I was looking for due to increased workload, so thank you!

Reply
Tracy April 7, 2020 - 1:49 am

I figured this out as I forgot about having headers in my excel sheet. Now just need to figure out what I can and can’t type in my string. Thanks again for this. Any chance you might come up with a tutorial to add attachments to this macro?

Reply
Jordo January 27, 2020 - 8:22 pm

Hi I have an issue using this code. Why would the program run through a number of rows sending emails and then suddenly stop? I get no error message, yet the program didn’t finish all the rows. Thanks!

Reply
admin January 28, 2020 - 5:26 am

Check your email addresses, maybe some of them are empty, or contain errors. The script has an error handling block (dbg :). It should return an error code.

Reply
Ragnhild Steder March 19, 2020 - 1:05 pm

Hi,
I have used your code and adjusted it to my excel sheet and information in the code/mail and it work perfect 🙂 But now we would like to send this mail from a shared mailbox. I have googled all over to try to find the right code (Sentonbehalfofname, SendUsingAccount = OutApp.Session.Accounts.Item(2) etc) but I don’t know where to put it in the code, and what to write. Can you help me? I am not good in this macro coding 🙂 I have access to the shared mailbox and in my outlook I see both my mail and the shared one, but no matter what I do it stills sends out from my mail adr.

Reply
admin March 31, 2020 - 10:15 am

I think that you should try the following vbs code:
olMailItm.Body = strBody
olMailItm.Send
olMailItm.SentOnBehalfOfName = “sharedmbx@woshub.com”

Reply
Ragnhild Steder April 1, 2020 - 8:47 am

Hi,
Thanks for your answer. I had to put it in here:

strBody = strBody & “text” & vbCrLf
olMailItm.SentOnBehalfOfName = “sharedmbx@xx..no”
olMailItm.To = useremail

Then it worked!

Reply
Chhanda March 20, 2020 - 1:02 pm

Hi,
It is a great help.
thanks a lot.
I want to send the mail for specific conditions
Say, for Status =”Active”
Can you please advise how do I revise the code

Thanks again
Regards

Reply
admin April 2, 2020 - 10:16 am

You can add the following send condition:
if Status = “Active” then
olMailItm.Send
end if

Reply
Paula November 17, 2020 - 4:03 pm

Hi
Trying to send an email to one person with one line of text. I’ve taken out all the bits that I don’t need, and amended other as necessary. When I run the macro, it gets to the olMailItm.Send and then crashes. Any ideas what I am doing wrong.

Reply
RK August 13, 2021 - 2:37 pm

I got an OLE message in excel and my outlook froze. This is when I was testing with one email. Any tips how to get around it?

Reply
Debayan Roy January 19, 2022 - 6:25 pm

I am using a vba code, but for each mail a prompt appears where it prompts that “Another program is wanting to send a mail”. I have to manually click allow/deny which defeats the purpose of automation.

Reply
FAROG AHMED April 14, 2022 - 6:00 am

From the above mentioned process we can able to send the multiple emails at a time but how do attached excel to each email using the VBA micro in the same email as mentioned

Reply

Leave a Comment Cancel Reply

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

  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023
  • Adding Trusted Root Certificates on Linux

    January 9, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • FAQ: MS Office 2013 KMS and Volume License Activation
  • Office 2016 vs. Office 365: Differences and Licensing
Footer Logo

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


Back To Top