+ Reply to Thread
Results 1 to 6 of 6

How to prevent VBA from attaching a particular file when it generates email

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    648

    How to prevent VBA from attaching a particular file when it generates email

    I have macro that builds and attaches files from a folder that is designated. The problem is an excel file gets added to that folder that I don't need when my email gets generated. can you instruct the code to ignore attaching that file. that way I don't have manually delete it every time the email is generated. this is what I have that send the email.

    EDir = Sheet24.Range("av2").Value
       
     For Each Cell In ActiveWorkbook.Sheets("Noralis Email Gen (3)").Range("ai4:ai41")
            If Cell.Value Like "?*@?*.?*" Then
                strto = strto & Cell.Value & ";"
            End If
       Next Cell
        If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
       
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        
        Set rng = Nothing
        Set rng = Range("u4:ab7")
        
        Set fso = CreateObject("scripting.filesystemobject")
        
    '   This will create an email in Outlook
        Set olApp = CreateObject("Outlook.Application")
        Set olEmail = olApp.CreateItem(0)
    
    '   Setup retrieval of attachments folder
        Set fso2 = CreateObject("Scripting.FileSystemObject")
        Set fsFolder = fso.GetFolder(EDir)
    
    
    '   Display and create the properties of the email
        With olEmail
    
            .Display
            .To = strto
            .CC = ""
            .BCC = ""
            .Subject = Range("v10")
            .HTMLBody = RangetoHTML(rng) & .HTMLBody
    
            For Each fsFile In fsFolder.Files
                .Attachments.Add EDir & fsFile.Name
            Next
        End With
    
    On Error GoTo 0
    The file is Named "rawsurveys.xls"

    thanks
    Last edited by AliGW; 06-17-2018 at 02:27 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,006

    Re: How to prevent VBA from attaching a particular file when it generates email

    Thank you for attempting to add code tags. Please note that the closing tag requires a forward slash / rather than a backslash \. I have amended this for you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,438

    Re: How to prevent VBA from attaching a particular file when it generates email

    .
    Delete or Comment Out these lines :

    For Each fsFile In fsFolder.Files
                .Attachments.Add EDir & fsFile.Name
    Next

  4. #4
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Houston, TX
    MS-Off Ver
    office 365
    Posts
    648

    Re: How to prevent VBA from attaching a particular file when it generates email

    I want the other files in the folder just not the one file outlined above....

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    91,006

    Re: How to prevent VBA from attaching a particular file when it generates email

    You have marked this thread as solved - is it?

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,344

    Re: How to prevent VBA from attaching a particular file when it generates email

    In case it isn't.
    For Each fsFile In fsFolder.Files
        If fsFile.Name <> "rawsurveys.xls" Then
            .Attachments.Add EDir & fsFile.Name
        End If
    Next
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

+ 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. VBA producing unexpected results attaching file to email - PART II
    By trandle in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-22-2017, 01:42 PM
  2. Attaching an embedded PDF file in my worksheet to a new email
    By AnthonyGFS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2017, 04:24 AM
  3. [SOLVED] VBA code exporting to PDF but not attaching the file to the email.
    By michelle 1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-24-2016, 03:11 AM
  4. Email (outlook) recipients attaching specific file name.
    By monkeypants in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 05-13-2015, 07:18 PM
  5. Email (outlook) recipients attaching specific Excel file name.
    By monkeypants in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 05-13-2015, 07:14 PM
  6. Attaching One Worksheet of a File to Email
    By MLCall in forum Excel General
    Replies: 0
    Last Post: 01-17-2012, 12:51 PM
  7. Macro. compiling email, attaching 1x sheet + 1x powerpoint file.
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-28-2011, 02:24 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