• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
You are here: Home
Lost password?
sp_Search
Advanced Search
Advanced Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search

Please confirm you want to mark all posts read

Mark all topics read

sp_MobileMenu Actions
Actions
sp_LogInOut
Log In
sp_Search

Search Forums

sp_RankInfo
Ranks Information
Avatar

New/Updated Topics

General Excel Questions & Answers

  Customer Order From Facebook Page.

  Grey out master list when selected in dropdown

Dashboards & Charts

  Connecting 2 separate pivot charts or data sets to calculate…

VBA & Macros

  Send Email Code Error

  Direccionar de un rango a una celda especifica

  how can i add windows media player in excel 2016

  Changing shape colours

  Need VBA code to find unique group of Repetation follow by B…

Power Query

  Need help describing what I want to accomplish

  Opening several PDFs from a Sharepoint site, and validating …

Power Pivot

  Slicer not greyed out for items without data

Power Query

  pq_7.01_parameter_tables

Power BI

  How identify the URL to connect power bi with Project online

Excel Expert

  Excel Dashboard

  new and deleted entries

Select Forum

  Rules and Guides

Forum Rules and Guides

  Public Forums - For Registered Users

General Excel Questions & Answers

Dashboards & Charts

VBA & Macros

Power Query

Power Pivot

  Course Members Only

Excel Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Power BI

Excel

Word

Outlook

Excel Expert

Excel for Customer Service Professionals

Excel Analysis Toolpak

Excel Tables

Excel for Operations Management

Financial Modelling

Advanced Excel Formulas

Pivot Tables Quick Start

ForumsVBA & Macros
sp_TopicIcon
VBA to Send Bulk Emails from Excel in Outlook using OFT template.
Avatar
Sherry Fox
Poinciana, FL
Posts: 68
Level 0
February 2, 2023 - 3:13 am

1

I was able to file a simple VBA code that corresponded to my needs. A list with 2 columns, A for the Names (only so users can add to it and manage it), and B a list of email addresses. I have attached my Excel template, with my 2 email addresses for testing. However the test never made it that far. I am gettin g an error message when I run it stating "Compile Error: Variable Not Defined". Attached is a copy of the error message.

I also have a second request too. There is now an outlook template file (.OFT) used to generate this email. Can you add that in so rather than a "blank" email template, the template of "Change Notification.oft" is used? I can adjust the file path to my computer.
.

Option Explicit

Sub SendEmailList()

On Error GoTo ErrHandler

' Set Outlook object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

' Create email object.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)

Dim myDataRng As Range

' We'll now set a range.
Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)

Dim cell As Range
Dim iCnt As Integer ' Its just a counter.
Dim sMail_ids As String ' To store recipients email ids.

' Run a loop to extract email ids from the 2nd column.
For Each cell In myDataRng
If Trim(sMail_ids) = "" Then
sMail_ids = cell.Offset(1, 0).Value
Else
sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
End If
Next cell

Set myDataRng = Nothing ' Clear the range.

With objEmail
.to = sMail_ids ' Assign all email ids to the property.
.Subject = "This is a test message from Arun Banik"
.Body = "Hi, there. Hope you are doing well."
.Display ' Display outlook message window.
End With

' Clear all objects.
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:
'
End Sub

Annotation-2023-02-01-115432.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Annotation-2023-02-01-115432.png (53 KB)
sp_AnswersTopicSeeAnswer
Avatar
Velouria
London or thereabouts
Posts: 613

Level 4
February 2, 2023 - 5:52 pm

2

Using a template actually fixes the first error (you hadn't defined olMailItem) too:

Option Explicit
Sub SendEmailList()

On Error GoTo ErrHandler

' Set Outlook object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

' Create email object.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItemFromTemplate("path to your OFT file here")

Dim myDataRng As Range

' We'll now set a range.
Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)

Dim cell As Range
Dim iCnt As Integer ' Its just a counter.
Dim sMail_ids As String ' To store recipients email ids.

' Run a loop to extract email ids from the 2nd column.
For Each cell In myDataRng
If Trim(sMail_ids) = "" Then
sMail_ids = cell.Offset(1, 0).Value
Else
sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
End If
Next cell

Set myDataRng = Nothing ' Clear the range.

With objEmail
.to = sMail_ids ' Assign all email ids to the property.
.Subject = "This is a test message from Arun Banik"
.Body = "Hi, there. Hope you are doing well."
.Display ' Display outlook message window.
End With

' Clear all objects.
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:
'
End Sub

Avatar
Sherry Fox
Poinciana, FL
Posts: 68
Level 0
February 7, 2023 - 1:55 am

3

Velouria,

That works perfect, thanks so much. I have a second very similar project that would contain one mass list

Column A - Name
Column B - Email address
Those are the same as previous

Trigger would be Column F. Does it contain "x" (NOT case sensitive") If "x" is listed, generate email to address in column B . If Column F is blank or contains any other text, then do nothing. Also it is likely this list will exceed 500 (Outlook's max), so a loop must be incorporated to allow multiple emails to auto-generate until all names with "X" in column F have been generated.

This macro will be duplicated for columns G, H & I as triggers too (separate buttons for user to click). An d because of the list being greater than the Outlook max (it will probably be about 3K total email addresses within the company). That is why the loop must be created.

And can it "send" so the user does not have to click the send button? And finally, can it remove the signature so that is not sent?

Avatar
Velouria
London or thereabouts
Posts: 613

Level 4
February 9, 2023 - 6:01 pm

4

Is the signature part of the template?

 

Do you need the code to send one email per person, or simply start a new email for each 500 (or so) addresses?

Avatar
Sherry Fox
Poinciana, FL
Posts: 68
Level 0
February 10, 2023 - 8:23 am

5

@Velouria,

No the signature is NOT part of the template.  But it gets added when MY email is used.  I will do a test with a user tomorrow for one of her Shared Inboxes to see if the signature shows.  It would be 1 email for 500 people, than the loop would send another email to the next 500.  I just found out there is another criteria (of course).  Now although I do not have access to Shared Inboxes, both my users needing this macro do.  Now this could be done one of 2 ways:

1)  A drop down menu to select the correct Shared Inbox to use

2) It could be "hard coded" into the VBA (just comment out this portion please so I can personalize it as needed)

 

If it is hard coded, I will clone the macro for each of the different shared email addresses.  But that won't matter, as the template will ALSO vary.

Avatar
Velouria
London or thereabouts
Posts: 613

Level 4
February 10, 2023 - 7:11 pm

6

This is getting close to consultancy work (which I don't do any more) rather than forum Q&A...

 

I can't test this as I don't use shared mailboxes, but I think something like this should at least get you started:

 

Sub SendEmailList()

On Error GoTo ErrHandler

' Set Outlook object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

Dim myDataRng As Range

' We'll now set a range.
Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)

Dim cell As Range
Dim iCnt As Integer ' Its just a counter.
Dim sMail_ids As String ' To store recipients email ids.

' Run a loop to extract email ids from the 2nd column.
For Each cell In myDataRng
If Len(cell.Value) <> 0 Then
If Trim(sMail_ids) = "" Then
sMail_ids = cell.Offset(1, 0).Value
Else
sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
End If
iCnt = iCnt + 1
If iCnt = 500 Then
SendEmail objOutlook, sMail_ids
sMail_ids = vbNullString
iCnt = 0
End If
End If
Next cell

ErrHandler:
'
End Sub
Sub SendEmail(objOutlook As Object, ListOfAddresses As String)
' Create email object.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItemFromTemplate("path to your OFT file here")

Dim MailAccount As Object
Set MailAccount = objOutlook.session.acccounts("account display name here")

With objEmail
Set .SendUsingAccount = MailAccount
.to = ListOfAddresses ' Assign all email ids to the property.
.Subject = "This is a test message from Arun Banik"
.Body = "Hi, there. Hope you are doing well."
' .Display ' Display outlook message window.
.Send ' Send the email
End With

End Sub

sp_AnswersTopicAnswer

The following users say thank you to Velouria for this useful post:

Philip Treacy
Avatar
Sherry Fox
Poinciana, FL
Posts: 68
Level 0
February 11, 2023 - 12:38 am

7

Velouria,

 

Thanks so much.  It was not my intention, and I feel so bad that I kept going back for more.  But for every inch you had helped me provide to my boss he wanted another yard.  Please accept my apology.  That was my problem to deal with, and I should not have requested so much assistance from you as the game change.  Thanks so very much for all your time.

Avatar
Hans Hallebeek
the Netherlands
Posts: 70
Level 0
February 11, 2023 - 4:55 pm

8

Sorry for budding in, but this forum (and (two) other ones I checkout, help wher I can  out or post questions) is exactly for that. You can ask, anyone can pick it up and help where he/she can.

Knowledge is something we should always share and it's like programming, when you receive an answer that works you always realize there's a what-if and you want to go one more step.

If you do not ask a question when seeking a result, you will never get an answer, imho, don't apologize 

Avatar
Sherry Fox
Poinciana, FL
Posts: 68
Level 0
February 14, 2023 - 2:17 am

9

@Hans,

Thanks so much.  I really felt bad, and I felt so much better after your post.

Avatar
Velouria
London or thereabouts
Posts: 613

Level 4
February 14, 2023 - 6:34 pm

10

Apologies - it was not my intention to make you feel bad. I just wanted to point out that there are generally limits to what you'll get from a forum.

Avatar
Sherry Fox
Poinciana, FL
Posts: 68
Level 0
February 15, 2023 - 12:22 am

11

Thanks Velouria. Appreciate all your help.

Forum Timezone:
Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Lawrence Smith
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)

Devices in use: Desktop (7), Phone (4)

Forum Stats:
Groups: 3
Forums: 24
Topics: 6206
Posts: 27202
Member Stats:
Guest Posters: 49
Members: 31875
Moderators: 3
Admins: 4
© Simple:Press

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.