Tom’s Tutorials for Excel: Attach Workbook in Outlook Email to Multiple Recipients

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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
31 comments on “Tom’s Tutorials for Excel: Attach Workbook in Outlook Email to Multiple Recipients
  1. JP says:

    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)

  2. Tom Urtis says:

    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

  3. JP says:

    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.

    • DanaH says:

      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.

  4. William Luis says:

    Hi Tom,

    Great tutorial.
    However instead of sending the whole workbook, I need to send a sheet instead.
    How do I go about it?

  5. Alastair says:

    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?

  6. Lisa Geyer says:

    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.

    • Tom Urtis says:

      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.

  7. Akshay Kumar Jain says:

    Hi, how could we send different attachment file to multiple receipents through email address list in any Excel file

  8. Simona says:

    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

    • Tom Urtis says:

      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!

  9. Simona says:

    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.

    • Tom Urtis says:

      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.

  10. Simona says:

    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”

  11. Simona says:

    Every person listed should receive an email with a listing of his data (awards)

  12. Simona says:

    hi tom,
    Did my previous comment answer your question?
    Thanks
    Simona

    • Tom Urtis says:

      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.

  13. Simona says:

    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.

    • Tom Urtis says:

      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

  14. Irving says:

    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

    • Tom Urtis says:

      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?

      • Irving says:

        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

        • Tom Urtis says:

          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.

          • Irving says:

            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.

  15. Joel says:

    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

  16. Rajendra says:

    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 .

Leave a Reply

Your email address will not be published. Required fields are marked *

*