Results 1 to 2 of 2

Thread: Unable To Attach File To Outlook Email with VBA

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    Unable To Attach File To Outlook Email with VBA

    I am trying to attach a XLS to each email - I have attached the file I am working with. Each file has the same name column "A". The Email is displaying fine but the file itself is not attaching I i'm not sure what I am doing wrong. The Files are stored in the folder in column "E".

    Any help?

    Below is the code I am using.


    Code:
    Sub Send_Files()
    
        Dim OutApp As Object
        Dim OutMail As Object
        Dim sh As Worksheet
        Dim cell As Range
        Dim FileCell As Range
        Dim rng As Range
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set sh = Sheets("Techs")
    
        Set OutApp = CreateObject("Outlook.Application")
    
        For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    
         
            Set rng = sh.Cells(cell.Row, 1).Range("e2:Z2")
    
            If cell.Value Like "?*@?*.?*" And _
               Application.WorksheetFunction.CountA(rng) > 0 Then
                Set OutMail = OutApp.CreateItem(0)
    
                With OutMail
                    .to = cell.Value
                    .Subject = ThisWorkbook.Sheets("Techs").Range("C2").Value
                    .Body = ThisWorkbook.Sheets("Techs").Range("d2").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
    
                    .Display
                End With
    
                Set OutMail = Nothing
            End If
        Next cell
    
        Set OutApp = Nothing
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    Attached Files Attached Files
    Last edited by Admin; 05-22-2013 at 01:01 PM. Reason: code tag added

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    This should help.. it's almost the same layout as your file, so you shouldn't find it difficult to edit.

    http://www.excelfox.com/forum/f18/se...email-ids-304/
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 17
    Last Post: 07-15-2013, 09:56 PM
  2. Replies: 6
    Last Post: 05-25-2013, 07:36 PM
  3. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  4. How To Send Outlook Email Using VBA
    By mfaisalrazzak in forum Excel Help
    Replies: 7
    Last Post: 03-03-2013, 03:09 AM
  5. Replies: 3
    Last Post: 02-20-2012, 12:54 AM

Tags for this Thread

Posting Permissions

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