Results 1 to 2 of 2

Thread: Unable To Attach File To Outlook Email with VBA

Threaded View

Previous Post Previous Post   Next Post Next Post
  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

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
  •