Email from Excel
 

Hello....

I ahve found the following code from Ron de Bruin's
website which works well. I was wondering if anyone has
extended this to handle any errors?

As I am emailing 100's of files and these get imported
with another macro sometimes the macro falls down due to
their not being an email address or file name in the
respective cells. What I would like is if there is
somekind of error for the report to carry on to the next
item.

Cheers

Going going gone Ronnie

Sub Email_Remit()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
For Each cell In Sheets("Sheet1").Columns
("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Offset(0, 1).Value < "" Then
If cell.Value Like "*@*" And Dir(cell.Offset
(0, 1).Value) < "" Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.Subject = "File Send"
.Body = "Please see the attached file"
.Attachments.Add cell.Offset(0,
1).Value
.Display
End With
Set olMail = Nothing
End If
End If
Next cell
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub

Email from Excel
 

Hi Ronnie

It is checking for a E-mail Addres and if tehre is a file in the column and if
it exist?
It will loop through all cells


Maybe you have set the Offset wrong?


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ronnie" wrote in message ...
Hello....

I ahve found the following code from Ron de Bruin's
website which works well. I was wondering if anyone has
extended this to handle any errors?

As I am emailing 100's of files and these get imported
with another macro sometimes the macro falls down due to
their not being an email address or file name in the
respective cells. What I would like is if there is
somekind of error for the report to carry on to the next
item.

Cheers

Going going gone Ronnie

Sub Email_Remit()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim cell As Range
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
For Each cell In Sheets("Sheet1").Columns
("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Offset(0, 1).Value < "" Then
If cell.Value Like "*@*" And Dir(cell.Offset
(0, 1).Value) < "" Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = cell.Value
.Subject = "File Send"
.Body = "Please see the attached file"
.Attachments.Add cell.Offset(0,
1).Value
.Display
End With
Set olMail = Nothing
End If
End If
Next cell
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub