Creating multiple e-mails with separate attachments

[daryl83]

Hi All,

so I have two issues with the code below:

1. I am attempting to create a macro that e-mail seperate e-mail addresses with separate attachments based on the cells in the homesheet. However, when I run it, I get a 'select method of worksheet failed' error at

Parent.Select 

. When I attempt again to run it, it works. I'd prefer it if it worked first time round everytime.

2. Then when the code runs, it does everything I want except there is a divider in the e-mail body between the 'Intro' and the rest of the body which I'd like to remove.

Appreciate any advice on this.

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()

    sn = Sheets("Contacts").Cells(1).CurrentRegion.Value
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
  
        For i = 2 To UBound(sn)
        ActiveWorkbook.EnvelopeVisible = True
        Set Sendrng = Sheets(sn(i, 1)).Range("A1:M71")
        With Sendrng
            .Parent.Select
            With .Parent.MailEnvelope
                .Introduction = sn(i, 4)
                With .Item
                    .To = sn(i, 2)
                    .Subject = sn(i, 3)
                    .Attachments.Add sn(i, 5)
                    .Send
                End With
            End With
        End With
    Next
    ActiveWorkbook.EnvelopeVisible = False
    With Application
        .Goto Sheets("Contacts").Range("A1")
        .ScreenUpdating = False
        .EnableEvents = False
    End With
End Sub 

[Logit]

.
I've not had much success with the MailEnvelope version of creating an email. If you are interested, here is another macro that pretty much does what you want.
You can tweek it to your specifications:

Please Login or Register  to view this content.

[daryl83]

This is much more straightforward. Thanks!

[Logit]

You are welcome

[Mercurioneo]

Hello,

Thank you both, this code is perfect for me but I would need something more, sorry but I am very new in VBA and I would need to add a column B for the CC as well. Could you please adapt this code for this. It would be great.

Thanks again.

Edid: Not needed yet, I have already done it. Thanks

[Logit]

.
See the attached with changes

[Mercurioneo]

Thanks, I did it in the same way, but I appreciate your help a lot.

[Logit]

.
Does it work for you ? It works here. Just want to make certain.

[Mercurioneo]

Yes it worked. I cannot attach my file because it seems disabled for me

[Logit]

Great ! Cheers

[Mercurioneo]

Great ! Cheers

Hello Logit,

I am attaching the link to my final file, I have create a new code to create L3 files that have to contain some pivot tables in another sheet. I prefer your code it is very fast compared with mine and more optimized, but I wasn't able to create the pivot tables with your code. Could you please adapt my code (Sub L3creation) to work as good as yours (subL4creation)

Thanks in advance, if it is not possible or you are busy thanks anyway for your time.

https://www.dropbox.com/s/i80pve8eha...test.xlsm?dl=0