vietnam123
Thành viên mới
- Tham gia
- 4/9/07
- Bài viết
- 12
- Được thích
- 9
Mình tìm được đoạn mã này khá hay, đã test thành công với Outlook và Gmail. Rất tiện cho việc gửi data khi xử lý xong.
Mail a different file(s) to each person in a range Index (Only working when you use Excel-Outlook 2000 -2007)
Ron de Bruin ( Last update 28 Oct 2006)
Make a list in Sheets("Sheet1") withIn
Xem thêm ở đây http://www.rondebruin.nl/sendmail.htm
Mail a different file(s) to each person in a range Index (Only working when you use Excel-Outlook 2000 -2007)
Ron de Bruin ( Last update 28 Oct 2006)
Make a list in Sheets("Sheet1") withIn
column A : Names of the peopleIn
column B : E-mail addressesIn
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 and file names that exist in that row it will create a mail with this information and send it.column B : E-mail addressesIn
column C:Z : Filenames like this C:\Data\Book2.xls (don't have to be Excel files)
Mã:
Sub Send_Files()
'Working in 2000-2007
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")
OutApp.Session.Logon
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
'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
Chỉnh sửa lần cuối bởi điều hành viên:
