I'm using Ron de Bruin's VBA code to send emails with attachments found here:
http://www.rondebruin.nl/win/s1/outlook/amail6.htm
How do I modify the code to do not send email if no attachments are found for one or more email addresses?
I'm using Ron de Bruin's VBA code to send emails with attachments found here:
http://www.rondebruin.nl/win/s1/outlook/amail6.htm
How do I modify the code to do not send email if no attachments are found for one or more email addresses?
Quick and dirty hack of Ron de Bruin's original code (untested)
Sub Send_Files() 'Working in Excel 2000-2016 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm Dim OutApp As Object Dim OutMail As Object Dim sh As Worksheet Dim cell As Range Dim FileCell As Range Dim rng As Range Dim bFound As Boolean 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(xlCellTypeConstants) 'Enter the path/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 For Each FileCell In rng.SpecialCells(xlCellTypeConstants) If Trim(FileCell) <> "" Then If Dir(FileCell.Value) <> "" Then bFound = True End If End If Next FileCell If bFound 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 Else MsgBox "No Attachments found" End If Set OutMail = Nothing End If Next cell Set OutApp = Nothing With Application .EnableEvents = True .ScreenUpdating = True End With End Sub
Last edited by Neil_; 08-30-2016 at 01:29 PM.
Frob first, tweak later
Hello Neil_,
Thanks, it works just as I needed.
I was attempting to make code adjustments below line "Set OutMail = OutApp.CreateItem(0)" and I could not get it to work.
Thanks again
no probs. Needs an edit though
End With bFound = False ' must reset flag Else
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks