Automating Emails with VB, multiple recipients with individual attachments

[m4dh4v]

Hi,

I was wondering and hoping it was possible to automate sending emails with VB to multiple recipients, each of whome have their own attahment.

I've seen previous threads which ask how its possible to email multiple recipients but not seen anything on also sending individual attachments.

Any help with this would be much appreciated.

 

[daverunt]

Hi,

That depends on where the information for the recipient and attachment are and you haven't revealed either.


If the recipient name and attachment path are for example in Column A and B respectively it's straightforward.
Otherwise nobody will know until you give the information....?

Here's an example of searching through a directory and adding an attachment to a mail. It looks to match the PDF name comprised of the recipients name and a number to the recipients name, listed in a column.

http://www.mrexcel.com/forum/showthread.php?t=569216

 

[m4dh4v]

Hi,

Thank you for your prompt reply. Yes, this information sits within an excel worksheet;

List of approx 60 client - Column A
Recipient email address - Columns B-D (Up to 3 email addresses per client)
Pathway of where file(s) are stored - Columns E-F (Up to 2 files to be sent per client).

Thank you for the attached link. I guess to take the script to the next level i would need to insert a loop to ensure all files are attached to the email for each client?

My aplogies, im relatively new to VB, far from calling myself an expert or guru yet so yout help is very much appreciated

 

[daverunt]

Hi,

here's some basic code.

It assumes column B is all email addresses and I have only used the address from B.
It does no kind of validity check on the address.
You can find code that will check for an @ in the cell, for example.

Column A is the name - added to the body

Attachments.
As there are only 2 I have just stuck to checking if either cell has content. If not no attachments. If the file doesn't exist or the path is a dud it will fail. So you might want to add some error handling there.

Sub Test_Mail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
 
    Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
 
    For Each cell In Rng
    RowNum = cell.Row
        If cell.Value <> "" Then
    SendTo = cell.Value
 
 
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)
 
            With OutMail
                .To = SendTo
                .Subject = "Reminder"
                .Body = "Dear" & Range("A" & RowNum).Value
 
                'Attachments
                        If Range("E" & RowNum).Value <> "" Then
                        attFile1 = Range("E" & RowNum).Value
                 .Attachments.Add attFile1
                        End If
                        If Range("F" & RowNum).Value <> "" Then
                        attFile2 = Range("F" & RowNum).Value
                .Attachments.Add attFile2
               End If
 
                .Display  'Change to .send            
 End With
            Set OutMail = Nothing
End If
    Next cell
 
End Sub