send file to a group of Outlook contacts

robertvdb

New Member
Joined
Jan 10, 2021
Messages
19
Office Version
  1. 2010
Platform
  1. Windows
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.

1617197049448.png
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,111
VBA Code:
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 AddressSubjectEmail BodyEmail in CCAttach
2
test@test.comFill your time sheetHello,

Please make sure you complete time sheet.

With regards,
test@testcc.com
3
test1@test.comFill your time sheetHello,

Please make sure you complete time sheet.

With regards,
test1@testcc.com
4
test2@test.comFill your time sheetHello,

Please make sure you complete time sheet.

With regards,
test2@testcc.com
5
6
test4@test.comFill your time sheetHello,

Please make sure you complete time sheet.

With regards,
test4@testcc.com
7
8
test6@test.comFill your time sheetHello,

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

New Member
Joined
Jan 10, 2021
Messages
19
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,970
Messages
5,678,895
Members
419,788
Latest member
Mukund2903

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top