Send multiple files in a folder

[shreeram.maroo]

Hi,

I have a folder wherein i am having multiple PDF files, approx 1000. These files weren't created through mail merge feature. I need to send these files to 1000 email IDs respectively.

I have an excel wherein i have listed the file name and email ID to which the file needs to be shared. Is there any shortcut or a way around to send 1000 mails at one go ?

Regards
Shreeram

[HansV]

It would be possible to create a macro that uses Outlook to send the PDF files, but you'd have to do that in small batches - if you try to send 1000 email messages in short succession, your provider will probably block it as spam.

[shreeram.maroo]

Hi Hans,

I can try for batch of 200 if this size is feasible. Can you pls guide me on the macro part.

[HansV]

Do the filenames in the worksheet include the folder path, or are they just the filenames?

[shreeram.maroo]

Just file names.

[HansV]

Here is a macro. It will work best if Outlook is already running.

Code: Select all

Sub SendMailBatch()
    Const BatchSize = 200     ' change if needed
    Const Folder = "C:\PDF\"  ' PDF folder; keep \ at the end
    Const FileCol = "A"       ' column with file names
    Const MailCol = "B"       ' column with email addresses
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim r As Long
    Dim outApp As Object
    Dim outMsg As Object
    FirstRow = Application.InputBox(Prompt:="Start from which row number?", Type:=1)
    If FirstRow < 1 Then
        Beep
        Exit Sub
    End If
    LastRow = Range(MailCol & Rows.Count).End(xlUp).Row
    Set outApp = CreateObject(Class:="Outlook.Application")
    For r = FirstRow To Application.Min(FirstRow + BatchSize - 1, LastRow)
        Set outMsg = outApp.CreateItem(0) ' 0 = olMailItem
        outMsg.Subject = "Test Email"
        outMsg.Body = "Please see the attached PDF file"
        outMsg.Recipients.Add Range(MailCol & r).Value
        outMsg.Attachments.Add Folder & Range(FileCol & r).Value
        outMsg.Send
    Next r
End Sub

[shreeram.maroo]

Thanks Hans, will try this