Hi,

Ron de Bruin gave me the following code for emailing files.

It works great! My only problem is that when I run the code, I get an
alert asking me whether I want to send the file. I try using
application.displayalerts = false, but then I don't get an alert but the
mail also doesn't send.

Is there a work around for this.

Thanks in advance for any help.

Anita



Sub TestFile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim monthAndYear
monthAndYear = InputBox("What is the month and year",
"MonthAndYear", "CY05Q01")

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")



On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value < "" Then
If cell.Value Like "?*@?*.?*" And Dir(cell.Offset(0,
1).Value) < "" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value
.CC = cell.Offset(0, 3)
.Subject = "Variance Reports for " & monthAndYear
.Body = "Hi " & cell.Offset(0, -1).Value & "," &
Chr(10) & Chr(10) & "Attached are your files for " & monthAndYear & ". " _
& "Please let me know if you have any questions." &
Chr(10) & Chr(10) & "Take care," & Chr(10) & "Anita"
.Attachments.Add cell.Offset(0, 1).Value
.Attachments.Add cell.Offset(0, 2).Value
.Application.DisplayAlerts = False
' .Display
.Send 'Or use Display
End With

Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Hi

See
http://www.rondebruin.nl/mail/prevent.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"a" wrote in message ink.net...
Hi,

Ron de Bruin gave me the following code for emailing files.

It works great! My only problem is that when I run the code, I get an alert asking me whether I want to send the file. I try
using application.displayalerts = false, but then I don't get an alert but the mail also doesn't send.

Is there a work around for this.

Thanks in advance for any help.

Anita



Sub TestFile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim monthAndYear
monthAndYear = InputBox("What is the month and year", "MonthAndYear", "CY05Q01")

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")



On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants)
If cell.Offset(0, 1).Value < "" Then
If cell.Value Like "?*@?*.?*" And Dir(cell.Offset(0, 1).Value) < "" Then
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value
.CC = cell.Offset(0, 3)
.Subject = "Variance Reports for " & monthAndYear
.Body = "Hi " & cell.Offset(0, -1).Value & "," & Chr(10) & Chr(10) & "Attached are your files for " &
monthAndYear & ". " _
& "Please let me know if you have any questions." & Chr(10) & Chr(10) & "Take care," & Chr(10) & "Anita"
.Attachments.Add cell.Offset(0, 1).Value
.Attachments.Add cell.Offset(0, 2).Value
.Application.DisplayAlerts = False
' .Display
.Send 'Or use Display
End With

Set OutMail = Nothing
End If
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub