Results 1 to 2 of 2

Thread: Email

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

    Email

    Hi

    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
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does it work if you change the line to:
    .To = sh.Range("B3").Value

    Steve

Posting Permissions

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