Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
send email to each customer email in excel sheet. | Excel Discussion (Misc queries) | |||
Email addresses in Excel need to format for mass email | Excel Worksheet Functions | |||
Email editor closes when forwarding Excel-embedded email | Setting up and Configuration of Excel | |||
working on excel document in email saved changes in email not in . | Excel Discussion (Misc queries) | |||
body of email disappears when I send an email from Excel | Excel Discussion (Misc queries) |