• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Attach separate worksheets to designated email addresses

KathrynJ

New Member
My workbook has multiple worksheets, one for each client. I want to send a separate email to each client, and include their respective worksheet as an attachment. One of the worksheets lists all of the client names and email addresses. I need help with a macro to match the client names to worksheet names, and then send the emails.
 
Hi,

If you are using Outlook, you can try the following (sample attached):
Code:
Sub Send_Email()

    Dim c As Range
    Dim TempWB As String
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object

    For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells
        Sheets(c.Value).Copy
        TempWB = ThisWorkbook.Path & "\Temp.xlsx"
        ActiveWorkbook.SaveAs Filename:=TempWB
        ActiveWorkbook.Close False

        Set OutLookApp = CreateObject("Outlook.application")
        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
                .To = c.Offset(, 1)
                .CC = "Your CC here"
                .Subject = "Your Subject here"
                .HTMLBody = "Your Body content here"
                .Attachments.Add TempWB
                .Display
    '            .Send
        End With
    
        Kill TempWB
    Next c

End Sub

Hope this helps
 

Attachments

  • Sample.xlsm
    18.6 KB · Views: 13
Back
Top