Results 1 to 2 of 2

Thread: Email

  1. #1
    Join Date
    May 2012
    Thanked 0 Times in 0 Posts



    I have set of workbooks that are created using excel vba once the workbooks are created I would like for them to be saved and then sent to someone via email.

    The email address will be grab from the active workbook cell..... this is the email code I have how do I tell it to send the email to the email address in the cell B3of the active workbook

    Sub Send_Files()
    'Working in 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim cell As Range, FileCell As Range, rng As Range

    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    Set sh = Sheets("EmailList")

    Set OutApp = CreateObject("Outlook.Application")

    For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConst ants)

    'Enter the 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
    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

    .Display 'Or use Send
    End With

    Set OutMail = Nothing
    End If
    Next cell

    Set OutApp = Nothing
    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    Does it work if you change the line to:
    .To = sh.Range("B3").Value


Posting Permissions

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