Tom’s Tutorials for Excel: Attach Workbook in Outlook Email to Multiple Recipients
With a macro from Excel, you can create an email in Outlook that will attach your active workbook and fill in the To field with a list of multiple recipients. Suppose you have a workbook that you email to the same people on a regular basis. On a hidden worksheet, you can list all the recipients’ email addresses, such as I have done in column A of Sheet3.
This macro will create an email in Outlook, attach your workbook, and write your list of recipients in the To field as seen in the following picture.
Sub EmailAttachmentRecipients()
'Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
'Declare a String variable for the recipient list, and an Integer variable
'for the count of cells in column A that contain email addresses.
Dim strTo As String
Dim i As Integer
strTo = ""
i = 1
'Loop through the recipient email addresses to build a continuous string
that separates recipient addresses by a semicolon and a space.
With Worksheets("Sheet3") 'change sheet name where list is kept.
Do
strTo = strTo & .Cells(i, 1).Value & "; "
i = i + 1
Loop Until IsEmpty(.Cells(i, 1))
End With
'Remove the last two characters from the recipient string, which are
'an unnedded semicolon and space.
strTo = Mid(strTo, 1, Len(strTo) - 2)
'Display the email message with the attached active workbook.
With objMailItem
.to = strTo
.CC = "CEO@yourcompany.com; President@yourcompany.com"
.Subject = "Example attachment to multiple recipients"
.Body = _
"Hello everyone," & Chr(10) & Chr(10) & _
"Here's an example for attaching the active workbook" & Chr(10) & _
"to an email with multiple recipients."
.Attachments.Add ActiveWorkbook.FullName
.Display 'Change to Send if you want to just send it.
End With
'Release object variables from system memory.
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing
End Sub
It looks like you have an unused reference to the Inbox. Either way, if you want to get the default Inbox, the best way is to use the GetDefaultFolder method, because the Inbox might not be the first folder.
Set objInbox = objNameSpace.GetDefaultFolder(olFolderInbox)
Hey Jimmy, thanks for the comment. Your point about the unused reference to the objInbox variable is well taken. I left it in because it is a holdover from this as-yet unposted macro that first checks to see if Outlook is already open, and if so, Outlook is activated. If Outlook happens to be closed, an Outlook application is created using late binding.
Sub OpenOutlook()
‘Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Set objOutlook = CreateObject(“Outlook.Application”)
Set objNameSpace = objOutlook.GetNamespace(“MAPI”)
Set objInbox = objNameSpace.Folders(1)
‘Activate Outlook if is already open, or display Outlook’s
‘application object interface if it is closed.
On Error Resume Next
AppActivate (“Outlook”)
If Err.Number 0 Then objInbox.Display
Err.Clear
End Sub
So I have a question for you. Substituting my line
Set objInbox = objNameSpace.Folders(1)
with your suggestion of
objNameSpace.GetDefaultFolder(olFolderInbox)
in the above macro, how would you recommend getting that GetDefaultFolder method to work reliably by applying it there, because other than how I wrote it above, it is erroring for me no matter how I’ve declared olFolderInbox and onjInbox.
Thanks again for your comment.
Tom
Sorry for the delay in responding, I don’t see any “subscribe to comments” feature on your site so I had no idea you replied.
My bad, since you are late bound, any built-in Outlook constants (like olFolderInbox) must be converted to their numeric equivalents.
Use the Object Browser (press F2 in Outlook’s VBA editor or set an early bound reference to the Outlook object library in Excel by going to Tools > References, then press F2) and search for “olFolderInbox”. Look at the bottom for the “Const olFolderInbox = n” statement.
You can either paste the whole Const statement into your code, or change “olFolderInbox” to its numeric equivalent.
Hey Jimmy, thanks for the tip. And thanks also for reminding me about notifying commenters when replies are posted, which I’ll look into implementing on my site asap.
Hi Tom,
Great tutorial.
However instead of sending the whole workbook, I need to send a sheet instead.
How do I go about it?
I posted an example of that on my Twitter feed here:
https://twitter.com/TomUrtis/status/948648967541108736
This macro is great and exactly what I needed! Two questions possibly…
1. is there a way of setting it to just send a PDF version of the current sheet?
2. Is there a way to have information from a certain cell in the email? We’re using this for sites to request additional money so if they put ‘950’ in the site cell, can this be copied and used in the subject line (e.g. – Leverage request – Site 950) rather than them needing to update this manually?
Thank you. Extremely helpful and easy to apply. Thank you for the comments in the code that helped me customize this to my specific needs.
Thank you for the nice comment, Lisa! I always welcome suggestions for examples of how to do this or that in Excel. I also post daily Excel tips on Twitter, @TomUrtis.
Hi, how could we send different attachment file to multiple receipents through email address list in any Excel file
I do not understand the exact meaning of your question. Please give an example of what you are working with and what the scenario would be, and your expected result.
Hi Tom,
I have an excel sheet with multiple line items for each recipient. Using the mail merge in word will send an email for each line of excel data (same person ends up receiving one email for each line item), but if I want to send only one email with multiple line items in it. How do i do that?
Thanks
Hello Simona – –
Can you please clarify a couple points:
(1)
Is your question an Excel question or a Word question? I ask because you mentioned the mail merge feature in Word and I’m now sure from which application you want the macro to run.
(2)
Can you also please clarify what you mean by “line item” and what it means “to send only one email with multiple line items in it.” I’m not clear on what exactly you are referring to, which maybe your answer to my first question will help me understand.
Thanks!
Hi Tom,
Thank you for looking into this.
This is an excel question. I referenced the word mail merge as an example.
My problem is that I want to send emails to a list of people (I have sent you a sample via email)
the same person is listed on my list more than once. I know how to generate a mail merge in word using a excel list and emailing from outlook, but the limitation of word is that will send one email for each instance a person is listed. I would like to send one email for each person listed in the excel list and the email to contain all the info that is listed for that particular recipient in several excel lines.
I hope this helps to explain my problem, I am having a difficult time explaining because i just discover that this may be doable with VBA, got your book, Excel VBA, and started to study but is a big learning curb for such a complex problem. I m still a beginner and looks like it will take me a few months before I can understand to do it.
Not totally clear. In your attachment you have 4 people’s names in column A, each of those 4 are listed several times. In column B is the same email address for every entry. The CC list is also the same for every line.
What exactly should be emailed? The range of data belonging to each person? Do you want it as a picture? I don’t understand what the actual email should look like based on the spreadsheet you sent.
I have listed the same email for everyone because I was testing and didn’t want to send test emails directly to recipients. The original spreadsheet will have the email of each person and all emails will cc to the same email address as listed in the spreadsheet.
For the body of the email I was thinking to say something like this:
“Subject line: Patent awards for Q4 2018
Dear First Name,
This is to let you know that you will be receiving patent awards as per below schedule:
‘listing here all awards that belong to that particular person as a table’
If you have question or need assistance, please let us know
Thank you”
You did not answer my questions.
Please re-read my last reply.
Every person listed should receive an email with a listing of his data (awards)
hi tom,
Did my previous comment answer your question?
Thanks
Simona
Not without a full example like I asked, such as if you want it as actual data or as a picture, and should it be embedded into the body of the email or as an attachment, and which fields (columns) to include or not include.
Sorry about that, I am not very good at this.
Will try again_
__Every person listed in the excel list should receive an email with their info.
The info from the excel should be embedded in the body of the email, the fields to be included should be the ’employee name’, ‘award amount'(s), ‘file number’ and ’employee id’.
If a person has more than one award, all of them should be listed in the body of the email, if possible to look like a little table in the body of the email.__
Let me know if this is acceptable.
Thank you for your patience and for your help with this.
Run this when your worksheet with the table is active. This will put all the ranges you want to email on each sheet to have a record of them. From here where I indicate in the code, loop through the sheets to embed the respective ranges for emails sent to their recipient variables.
Sub MultipleMailMerge()Dim LastRow&, xRow&, strName$
Dim EmailPrimary$, EmailCC$, asn$
Dim area As Range, EmployeeArea As Range
asn = ActiveSheet.Name
Dim iCounter%
iCounter = 1
Application.ScreenUpdating = False
Range("A1").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For xRow = LastRow To 2 Step -1
If Cells(xRow, 1).Value <> Cells(xRow - 1, 1).Value Then Rows(xRow).Resize(1).Insert
Next xRow
Columns(2).Hidden = True
Range(Columns(5), Columns(8)).Hidden = True
Columns(11).Hidden = True
For Each area In Sheets(asn).Columns(1).SpecialCells(2).Areas
With area
strName = .Cells(1, 1).Value
EmailPrimary = .Cells(1, 2).Value
EmailCC = .Cells(1, 1).Offset(0, 10).Value
Set EmployeeArea = .Resize(.Rows.Count, 11)
If .Row > 1 Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = strName
Sheets(asn).Range("A1:K1").SpecialCells(12).Copy Range("A1")
EmployeeArea.SpecialCells(12).Copy Range("A2")
'Outlook code goes here
iCounter = iCounter + 1
End If
End With
Set EmployeeArea = Nothing
Next area
With Sheets(asn)
.Cells.EntireColumn.Hidden = False
.Columns(1).SpecialCells(4).EntireRow.Delete
End With
Application.ScreenUpdating = True
End Sub
Hello Tom,
Perhaps you can help me. I have written code to send specific users specific worksheets from a workbook, but I would like to be able to send the worksheets based on information contained in a worksheet called EmailList. That list contains 4 fields, B and C are name and title info, D is the Email address of the recipient and A is a location code which is also the name of the worksheet I want to send. There in is my problem. I cannot figure out how to make a macro that will compare field A to the available worksheets and automatically send the correct worksheet to the correct recipient(s).
This is what I have now for the specific worksheet to be sent to specified recipient(S).
Sub Emailreport()
Dim oApp As Object
Dim oMail As Object
Dim WB As Workbook
Dim FileName As String
Dim wSht As Worksheet
Dim shtName As String
Application.ScreenUpdating = False
‘ Make a copy of the worksheet
‘ and save it to a temporary file
Sheets(“ASH”).Select
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = WB.Worksheets(1).Name
On Error Resume Next
Kill “C:\” & FileName
On Error GoTo 0
WB.SaveAs FileName:=”F:\” & FileName
‘Create and show the Outlook mail item
Set oApp = CreateObject(“Outlook.Application”)
Set oMail = oApp.CreateItem(0)
With oMail
‘Uncomment the line below to hard code a recipient
.To = “XXXXXX_XXXXXXXXX@XXXXXX.com”
.CC = “xxxx@xxxx.com”
.Subject = “MONTHLY WARRANTY RECOVERY UPDATE”
.HTMLBody = .HTMLBody & “”
.HTMLBody = .HTMLBody & “”
.Attachments.Add WB.FullName
.Display
End With
‘Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False
‘Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
Sheets(“Recovery_All”).Select
End Sub
So you are saying that the same recipient email address can appear many times in column D, and each time the name appears, a different sheet name appears in column A to be sent to that recipient? If so, do you want each sheet sent separately or in a single workbook? Or, is it a 1 to 1 relationship where there would only be 1 sheet per recipient address to be sent?
You are correct, if a recipient is to receive multiple worksheets then Column A will show the location code “this is also the worksheet name” and their Email will show up in the corresponding column D. Sending a single worksheet to all of its recipients seems to me to be the way to go, thus I currently hard code each recipient into to the .To= line. I am open to suggestions that will speed up the process.
Thank you
Just to make sure I understand the actual potential results of what you are saying, you wrote this:
“Sending a single worksheet to all of its recipients seems to me to be the way to go, thus I currently hard code each recipient into to the .To= line.”
I see you are open to suggestions, but first, taking this literally that you wrote, is for example an email address shows up 100 times in that list, that recipient would receive 100 separate emails? Whether or not anyone’s email address would actually show up 100 times is not the point of my question. I just want to make sure that this is what is currently coded to do.
If so (or if not), it seems to me that a workbook to get created to hold those 100 (or however many) sheets and attached as a single email would be my suggestion. If I were the recipient, I can say for sure that I would not appreciate getting separate emails, UNLESS each sheet is so independent of the others that it should not be associated with anything else. You asked for a suggestion, and that would be mine, assuming I’m accurately interpreting your workbook.
I think you hit the nail on the head.
With that I am guessing I will need to search the EmailList for matching recipient addresses. Create a workbook. Move associated location sheets to that workbook. Attach to an Email. Send. Rinse and repeat for each email address.
Hi Tom,
I’m wondering if you can help me.
I’ve recently doctored up a code that splits lines from one “master file’ into multiple files based on the value of a nominated cell grouping all line with the set value in its own report. The files are then automatically saved in a newly created folder. I would like to now attach each report to its own email and send each email to a list of recipients specific to each report. (hope that makes sense) Example. if I print 10 reports I would like the code to create 10 new emails, attach each report to its own email, then send the email to 10 different recipients based on each report with the body of each email containing a generic statement.
Current Code below.
Any help would be appreciated
Sub breakMyList()
‘ This macro takes values in the range myList
‘ and breaks it in to multiple lists
‘ and saves them to separate files.
Dim cell As Range
Dim Path As String
Dim d As String
Path = “C:\Users\Public”
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each cell In Range(“vendorlist”)
[valvendor] = cell.Value
Range(“RawData”).AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range(“Criteria”), copyToRange:=Range(“Extract”), unique:=False
Range(Range(“Extract”), Range(“Extract”).End(xlDown)).Copy
Workbooks.Add
ActiveSheet.Paste
If Len(Dir(Path, vbDirectory)) = 0 Then
MsgBox “Path does not exist.”, vbCritical
Exit Sub
End If
d = Format(Date, “ddmmyyyy”)
If Len(Dir(Path & d, vbDirectory)) = 0 Then MkDir (Path & d)
ActiveWorkbook.SaveAs Filename:=Path & d & “\” & cell.Value & Format(Now, “dmmmyyyy”) & “.xlsx”, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range(Range(“Extract”), Range(“Extract”).End(xlDown)).ClearContents
Next cell
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Maybe I am missing it, but where is the list of email addresses referenced.
Hi,
I need solution in Excel VBA for sending email through outlook to multiple recipients(different emails) where a condition “Yes” or ‘Send reminder” meets.
Also subject should also be picked from excel range .
I was with you up until
“where a condition “Yes” or ‘Send reminder” meets.”
What does that mean?
Hi,
I have a question. I am fairly new to VBA coding. I will be using the same workbook sending to same email but I am wanting to change the subject everytime. Is it possible to have the subject be a specific cell and if so how would I go about doing so? Thanks in advance for your help.
If I understand correctly, you could replace this
Subject = “Example attachment to multiple recipients”
with this, if the cell holding the message is ell A1.
Subject = Range(“A1”).Value
I need to Send Multiple Emails ( Bulk) with Multiple Attachments via Outlook 365 from Spreadsheet information.
Conditions:-
1 From Group email address from spreadsheet
2 To email address from spreadsheet
3 CC email address from spreadsheet
4 BCC email address from spreadsheet
5 Subject line from spreadsheet
6 Body of the email from spreadsheet
7 Attachments first browse from folder the files from spreadsheet( Multiple Attachments)
8 Send Email automatically and we need confirmation sent successfully or failed.
Hello Harsha – –
Thank you for contacting me. I will be traveling during December and unable to respond with a solution until after the new year. May I recommend 2 links for you to get assistance before that.
One recommendation is to ask your question on a popular Excel forum that I also contribute to when I get the chance, at https://www.mrexcel.com/board/forums/excel-questions.10/. There are many Excel experts who monitor that forum around the clock, so you will have your question seen right away, whereas I cannot get to it for another month. The forum is free and easy to join.
The other recommendation is to take a look at Ron DeBruin’s website. Ron is another Excel MVP and he has many VBA examples for how to do this or that with Excel for Outlook. The links are
https://docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2010/ff458119(v=office.11)?redirectedfrom=MSDN
and
https://docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2010/ff519602%28v%3doffice.11%29