+ Reply to Thread
Results 1 to 14 of 14

VBA - Send multiple emails with multiple different attachments

  1. #1
    Registered User
    Join Date
    11-02-2020
    Location
    Montreal
    MS-Off Ver
    o365
    Posts
    10

    VBA - Send multiple emails with multiple different attachments

    Hi,

    I'm not really good at VBA. I was able to change a macro find on the internet (see below)
    I'd like to send multiple attachments. For now, 2 but might be more in the future.
    both attachments (PDF file, for now) must be sent to a specific group (TO + CC + BCC). 2 differents files per group
    Files paths change every month.
    Is that possible to add that in my current macro?

    Thanks a lot for your help


    Sub Send_email_EOMDocuments()
    Dim Email_Address_1 As String
    Dim Subject As String
    Dim Email_Address_2 As String
    Dim CC As String
    Dim message As String
    Dim filename As String
    Dim outlookapp As Object
    Dim outlookmailitem As Object
    Dim myAttachments As Object
    Dim Path As String
    Dim lastrow As Integer
    Dim Attachment As String
    Dim x As Integer

    x = 3

    Do While Sheet1.Cells(x, 1) <> ""

    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.CreateItem(0)
    Set myAttachments = outlookmailitem.Attachments
    Path = Sheet1.Cells(x, 5)
    Email_Address_1 = Sheet1.Cells(x, 1)
    Email_Address_2 = Sheet1.Cells(x, 2)
    CC = Sheet1.Cells(x, 3)
    Subject = Sheet1.Cells(x, 4)
    filename = Sheet1.Cells(x, 6)
    Attachment = Path + filename

    outlookmailitem.To = Email_Address_1
    outlookmailitem.CC = Email_Address_2
    outlookmailitem.BCC = CC
    outlookmailitem.Subject = Subject
    outlookmailitem.Body = "Please find your end of month documents attached "

    myAttachments.Add (Attachment)
    outlookmailitem.Display
    outlookmailitem.send

    lastrow = lastrow + 1
    Email_Address_1 = ""
    x = x + 1

    Loop

    Set outlookapp = Nothing
    Set outlookmailitem = Nothing


    MsgBox "All the documents have been sent"

    End Sub

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: VBA - Send multiple emails with multiple different attachments

    Currently you have one filename in cells(x,6). Where is the name of the second file you want to attach? It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    11-02-2020
    Location
    Montreal
    MS-Off Ver
    o365
    Posts
    10

    Re: VBA - Send multiple emails with multiple different attachments

    I can't attach a file because I'm new.

    I'd like to add :
    cells(x,7) = Path doc 2
    cells(x,8) = FileName Doc 2

    I have
    cells(x,5) = Path Doc 1
    cells(x,6) = FileName Doc 1

    Thanks for your help

  4. #4
    Registered User
    Join Date
    11-02-2020
    Location
    Montreal
    MS-Off Ver
    o365
    Posts
    10

    Re: VBA - Send multiple emails with multiple different attachments

    Sub Send_email_EOMDocument()
    Dim Email_Address_1 As String
    Dim Subject As String
    Dim Email_Address_2 As String
    Dim CC As String
    Dim message As String
    Dim filename_1 As String
    Dim filename_2 As String
    Dim outlookapp As Object
    Dim outlookmailitem As Object
    Dim myAttachments As Object
    Dim Path_1 As String
    Dim Path_2 As String
    Dim lastrow As Integer
    Dim Attachment As String
    Dim x As Integer


    x = 3

    Do While Sheet1.Cells(x, 1) <> ""

    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.CreateItem(0)
    Set myAttachments = outlookmailitem.Attachments
    Path_1 = Sheet1.Cells(x, 5)
    Path_2 = Sheet1.Cells(x, 7)
    Email_Address_1 = Sheet1.Cells(x, 1)
    Email_Address_2 = Sheet1.Cells(x, 2)
    CC = Sheet1.Cells(x, 3)
    Subject = Sheet1.Cells(x, 4)
    filename_1 = Sheet1.Cells(x, 6)
    filename_1 = Sheet1.Cells(x, 8)
    Attachment = Path + filename


    outlookmailitem.To = Email_Address_1
    outlookmailitem.CC = Email_Address_2
    outlookmailitem.BCC = CC
    outlookmailitem.Subject = Subject
    outlookmailitem.Body = "Please find your end of month documents attached"


    myAttachments.Add (Attachment)
    outlookmailitem.Display
    outlookmailitem.send

    lastrow = lastrow + 1
    Email_Address_1 = ""
    x = x + 1

    Loop


    Set outlookapp = Nothing
    Set outlookmailitem = Nothing


    MsgBox "All the documents have been sent"

    End Sub

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: VBA - Send multiple emails with multiple different attachments

    Try this macro. It assumes that the paths in cells(x,5) and cells(x,7) include the "\" at the end and the doc names include the extension (xlsx, xlsm, xls).
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-02-2020
    Location
    Montreal
    MS-Off Ver
    o365
    Posts
    10

    Re: VBA - Send multiple emails with multiple different attachments

    Thanks.

    It doesn't work
    Invalid procedure call or argument.
    When I do "Debug" .Attachments.Add Path1 & Doc1 is highlighted in yellow.

    Why do I have to change all my macro. I thought if I add Dim filename_2 As String and Dim Path_2 As String it will work.

  7. #7
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: VBA - Send multiple emails with multiple different attachments

    As I mentioned in my previous post:
    It assumes that the paths in cells(x,5) and cells(x,7) include the "\" at the end and the doc names include the extension (xlsx, xlsm, xls).
    Have you checked that the paths look something like this: C:\Test\ and the doc names look something like this: MyDoc.xlsx ? I tested the macro on a dummy file and it worked properly.

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: VBA - Send multiple emails with multiple different attachments

    I also forgot to mention that I have commented out the line to send the emails for testing purposes, so the macro will only display the emails. To send them as well, remove the apostrophe at the beginning of this line of code:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    11-02-2020
    Location
    Montreal
    MS-Off Ver
    o365
    Posts
    10

    Re: VBA - Send multiple emails with multiple different attachments

    I was a code 18 ;-( my VPN was disconnected. Thanks for your macro!! It'll help me.
    But I'd also like to understand why mine doesn't work...

    Sub Send_email_EOMDocument()
    Dim Email_Address_1 As String
    Dim Subject As String
    Dim Email_Address_2 As String
    Dim CC As String
    Dim message As String
    Dim filename_1 As String
    Dim filename_2 As String
    Dim outlookapp As Object
    Dim outlookmailitem As Object
    Dim myAttachments As Object
    Dim Path_1 As String
    Dim Path_2 As String
    Dim lastrow As Integer
    Dim Attachment_1 As String
    Dim Attachment_2 As String
    Dim x As Integer


    x = 3

    Do While Sheet1.Cells(x, 1) <> ""

    Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.CreateItem(0)
    Set myAttachments = outlookmailitem.Attachments
    Path_1 = Sheet1.Cells(x, 5)
    Path_2 = Sheet1.Cells(x, 7)
    Email_Address_1 = Sheet1.Cells(x, 1)
    Email_Address_2 = Sheet1.Cells(x, 2)
    CC = Sheet1.Cells(x, 3)
    Subject = Sheet1.Cells(x, 4)
    filename_1 = Sheet1.Cells(x, 6)
    filename_2 = Sheet1.Cells(x, 8)
    Attachment_1 = Path_1 + filename_1
    Attachment_2 = Path_2 + filename_2


    outlookmailitem.To = Email_Address_1
    outlookmailitem.CC = Email_Address_2
    outlookmailitem.BCC = CC
    outlookmailitem.Subject = Subject
    outlookmailitem.Body = "Please find your end of month documents attached"


    myAttachments.Add (Attachment)
    outlookmailitem.Display
    outlookmailitem.send

    lastrow = lastrow + 1
    Email_Address_1 = ""
    x = x + 1

    Loop


    Set outlookapp = Nothing
    Set outlookmailitem = Nothing


    MsgBox "All the documents have been sent"

    End Sub

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: VBA - Send multiple emails with multiple different attachments

    You are very welcome. I'm not sure why your macro didn't work and it's hard to tell without seeing your original file. Perhaps you can compare the two macros to see the difference.

  11. #11
    Registered User
    Join Date
    11-02-2020
    Location
    Montreal
    MS-Off Ver
    o365
    Posts
    10

    Re: VBA - Send multiple emails with multiple different attachments

    Thanks again!!!

    I want to improve myself with macro so here my file...

    Have a good day!
    Attached Files Attached Files

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: VBA - Send multiple emails with multiple different attachments

    Your macro worked for me except I couldn't test the attachments because I don't have those documents and paths. However, it seems to me that it should work for you.

  13. #13
    Registered User
    Join Date
    11-02-2020
    Location
    Montreal
    MS-Off Ver
    o365
    Posts
    10

    Re: VBA - Send multiple emails with multiple different attachments

    The error message is: Cannot add the attachment, no data source was provided

  14. #14
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: VBA - Send multiple emails with multiple different attachments

    Try:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Send Multiple Emails and Attachments Based on Criteria
    By ShakJames in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-18-2018, 09:44 AM
  2. Macros to send multiple attachments to multiple emails in Outlook
    By melvern28 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-27-2018, 07:44 AM
  3. [SOLVED] Excel Macro to Send Multiple Emails w/ Attachments using MS Outlook 2007
    By arnel_10 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2017, 08:09 PM
  4. Send multiple emails with different attachments if attachments found
    By AnkitJain in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2015, 05:35 AM
  5. Send personalized mass emails with multiple attachments
    By land_land in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2014, 01:45 AM
  6. VBA to send multiple emails with different attachments
    By gillyr7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-03-2014, 02:33 PM
  7. Problem using a macro to send multiple emails with separate attachments
    By joshthorntonwhite in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2013, 07:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1