send file to a group of Outlook contacts

[robertvdb]

I have a list of about 1000 customers, to whom I want to send a PDF file by email (Outlook).

I do this by sending the mail to myself, and I put the customers in Bcc.

Because 1000 is too much to handle, I want the customers separated in groups of 50, resulting in 20 similar mails to send.

Can anyone help me to program this in VBA ?

Thanks in advance.

 

[Logit]

Sub SendEmail()
    Dim OutlookApp As Object
    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim cc_ As String
    Dim subject_ As String
    Dim body_ As String

     'Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")

     'Loop through the rows
    For Each cell In Columns("a").Cells.SpecialCells(xlCellTypeConstants)

        email_ = cell.Value
        subject_ = cell.Offset(0, 1).Value
        body_ = cell.Offset(0, 2).Value
        cc_ = cell.Offset(0, 3).Value

        'Create Mail Item and send it
        Set MItem = OutlookApp.CreateItem(0)
        With MItem
            .To = email_
            .CC = cc_
            .Subject = subject_
            .Body = body_
            If Not Cells(cell.Row, "E").Value = "" Then
                .Attachments.Add (Path & "\" & Cells(cell.Row, "E").Value)
            Else
            End If
               
            .Display '<-- Change to .Send when you are ready to auto-send email without viewing first
        End With
    Next
End Sub

Excel 2007 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Email Address	Subject	Email Body	Email in CC	Attach
2​	test@test.com	Fill your time sheet	Hello, Please make sure you complete time sheet. With regards,	test@testcc.com
3​	test1@test.com	Fill your time sheet	Hello, Please make sure you complete time sheet. With regards,	test1@testcc.com
4​	test2@test.com	Fill your time sheet	Hello, Please make sure you complete time sheet. With regards,	test2@testcc.com
5​
6​	test4@test.com	Fill your time sheet	Hello, Please make sure you complete time sheet. With regards,	test4@testcc.com
7​
8​	test6@test.com	Fill your time sheet	Hello, Please make sure you complete time sheet. With regards,	test6@testcc.com
9​						Place all attachment files in same folder as this
10​						workbook. Beginning in E2, enter only the name of
11​						the file. Examples show below :
12​
13​							Test.txt
14​							Money.xlsm
15​							Tickets.xlsx
16​
17​							Pics.jpg
18​
19​							Invoice.xlsx

Sheet: Sheet1

 

[robertvdb]

Thanks Logit !

but this doesn't solve the issue of having to divide the group of emails in subgroups of 15. So for each group 1-15 and 16-30 and 31-45 etcetera I want to send them a mail separately.