Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Mail a different file(s) to each person in a range

Hello,

I needed to mail files to people in a ranges. I am using the following code.

This works perfect when the filenames are STRINGS, but if the filenames come from a formula ( in order to add the directory, extention, etc), the email is created but without the attachments.

If someone can look into this, it would be greatly appreciated!

Regards

D

this code originally found he
http://www.rondebruin.nl/mail/folder2/files.htm

-----------------------------------

Make a list in Sheets("Sheet1") with :

In column A : Names of the people
In column B : E-mail addresses
In column C:Z : Filenames like this C:\Data\Book2.xls (don't have to be Excel files)

The Macro will loop through each row in "Sheet1" and if there is a E-mail address in column B
and file name(s) in column C:Z it will create a mail with this information and send it.
Sub Send_Files()
'Working in 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set sh = Sheets("Sheet1")

Set OutApp = CreateObject("Outlook.Application")

For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConst ants)

'Enter the file names in the C:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")

If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) 0 Then
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -1).Value

For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) < "" Then
If Dir(FileCell.Value) < "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell

.Send 'Or use Display
End With

Set OutMail = Nothing
End If
Next cell

Set OutApp = Nothing

With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
-----------------------
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail a row to each person in a range (HTML) - by Ron de Bruin tcooper007 Excel Programming 7 January 6th 06 10:10 PM
Mail a different files to each person in a range [email protected] Excel Worksheet Functions 5 September 2nd 05 02:00 PM
Can't get the Person Name (Outlook e-mail recipients) smart tag wo ranwin Setting up and Configuration of Excel 0 January 11th 05 09:41 PM
Mail a row to each person in a range? Donnie Stone Excel Programming 1 August 7th 04 03:32 PM
Mail a row to each person in a range (HTML) Index? Donnie Stone Excel Programming 1 August 3rd 04 02:38 PM


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"