+ Reply to Thread
Results 1 to 5 of 5

Do not sent email if attachment is not found

Hybrid View

  1. #1
    Registered User
    Join Date
    03-14-2011
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Do not sent email if attachment is not found

    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?

  2. #2
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: Do not sent email if attachment is not found

    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

  3. #3
    Registered User
    Join Date
    03-14-2011
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Do not sent email if attachment is not found

    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

  4. #4
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: Do not sent email if attachment is not found

    no probs. Needs an edit though

    
                End With
                bFound = False ' must reset flag
                Else

  5. #5
    Registered User
    Join Date
    03-14-2011
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: Do not sent email if attachment is not found

    Quote Originally Posted by Neil_ View Post
    no probs. Needs an edit though

    
                End With
                bFound = False ' must reset flag
                Else
    Thanks
    I added the update and it's working well.
    I liked the feature of the message to know that a email was not sent.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to email pdf attachment to different email addressees
    By Ricky Wilko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2015, 07:43 PM
  2. Copy email contents to new email whilst saving attachment.
    By X82 in forum Outlook Programming / VBA / Macros
    Replies: 25
    Last Post: 08-18-2015, 03:16 PM
  3. olMail - Email Attachment Variable. Sending Email through Excel
    By ShakJames in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-23-2014, 07:55 AM
  4. olMail - Email Attachment Variable. Sending Email through Excel
    By ShakJames in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-23-2014, 07:41 AM
  5. Macro to send email with attachment for each unique email Ids
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-06-2014, 07:12 AM
  6. email excel file or worksheet as an attachment to multiple email addresses
    By jgeagle5 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-16-2009, 03:40 PM
  7. Email Open Workbook as an Attachment to Email
    By BazzaBoy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2009, 06:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1