Attaching Specific Files To Specific Email Addresses (via MS Outlook or Excel)

[indi visual]

I have a folder with approx 300 small file sized PDF's.


They are all specifically named to a unique email recipient.


I also have a list of these specific email address (that match the file names exactly).


Lastly, I have an Outlook email template I will use to attach & send each file to each email recipient accordingly.


My question is...


Would it be possible to use visual basic with Outlook (and or Excel) to attach and send each specific file to each specific recipient?


All the template says is here is your invoice with today's date.


What has to be the exact is of course the correct invoice, and the correct email address for that particular recipient.


Is any of this possible?


The whole goal I was hoping for is to avoid manually sending 300 emails (and attaching each file for each person).


I was hoping to batch send them out (with a code that could attach the right file based on the name) some how.

 

[Luke M]

Ron's the guy I trun to when dealing with email & excel. Some of his links look helpful:

http://www.rondebruin.nl/sendmail.htm

 

[indi visual]

Thanks Luke this code worked beautifully.


Only thing I'm trying to figure out now is how to specify a diff file path for each customer accordingly -because right now only the same file path-attachment is being uploaded attached for every email address (I was hoping to use Column D to specify each unique file path).


Here is the key line extracted from the code below:

".Attachments.Add ("CREVIOUS SCANS 06.21.2011test.attachment.pdf")"


If I can set this next to each recipient (so that each recipient gets it's designated attachment that would be awesome).


Just not sure how to tweak this line from here, any ideas from anyone are greatly appreciated.


Sub TestFile()

Dim OutApp As Object

Dim OutMail As Object

Dim cell As Range


Application.ScreenUpdating = False

Set OutApp = CreateObject("Outlook.Application")


On Error GoTo cleanup

For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)

If cell.Value Like "?*@?*.?*" And _

LCase(Cells(cell.Row, "C").Value) = "yes" Then


Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = cell.Value

.Subject = "Please ignore this message (this is Terrence)"

.Body = "Hello! : )(1 of 3) - " & Cells(cell.Row, "A").Value _

& vbNewLine & vbNewLine & _

"Please ignore this message I was just experimenting " & _

"All is well Tony just checking something out (this first test is to see if this excel script I wrote will actually kick out what I tell it to based on the excel file : )"

'You can add files also like this

.Attachments.Add ("CREVIOUS SCANS 06.21.2011test.attachment.pdf")

.Send 'Or use Display

End With

On Error GoTo 0

Set OutMail = Nothing

End If

Next cell


cleanup:

Set OutApp = Nothing

Application.ScreenUpdating = True

End Sub

 

[Luke M]

This line:

.Attachments.Add ("CREVIOUS SCANS 06.21.2011test.attachment.pdf")


becomes this:

.Attachments.Add ("CREVIOUS SCANS 06.21.2011" & cells(cell.row, "D"))


This assumes that col D contains a string like "Invoice For Bob.pdf"

If not, you just need to concatenate the correct string together.

 

[indi visual]

It worked beautiful.


Thanks Luke as always

 

[Luke M]

You're always welcome. =)