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
My task is to send such an e-mail from this template to each user in the list:
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:
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.
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
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.
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
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)) ?
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
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?
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
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
Is Outlook installed on your computer? Have you set up your mailbox profile?
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!
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?
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!
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.
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.
I think that you should try the following vbs code:
olMailItm.Body = strBody
olMailItm.Send
olMailItm.SentOnBehalfOfName = “sharedmbx@woshub.com”
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!
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
You can add the following send condition:
if Status = “Active” then
olMailItm.Send
end if
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.
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?
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.
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