1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA Email sending error

Discussion in 'Business Applications' started by crphoenix, Feb 24, 2016.

Thread Status:
Not open for further replies.
  1. crphoenix

    crphoenix Thread Starter

    Joined:
    Feb 22, 2016
    Messages:
    1
    Hey All
    Im very new to VBA, two weeks in the learning. I am trying to set it up so that when a cell on row g (sheet2) states YES. An single email is sent out to an address stated on sheet(1). Problem im getting is im recieving 80 emails. And none of Row g on sheet 2 is stating yes. Also can someone tell me how to make it send only a single email if more then one cell states yes.

    Im bad at explain so please ask what every detail that may be needed.
    Regards
    Jimmy

    Code:
    Sub eMail()
    Dim lRow As Integer
    Dim i As Integer
    Dim toDate As Date
    Dim toList As String
    Dim eSubject As String
    Dim eBody As String
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    Dim wa As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "One" Or ws.Name <> "two" Then
    lRow = Cells(Rows.Count, 4).End(xlUp).Row
    For i = 2 To lRow
      If Sheets(2).Cells(i, 7) = "Yes" Then
         Set OutApp = CreateObject("Outlook.Application")
         Set OutMail = OutApp.CreateItem(0)
            toList = Sheets(1).Range("G14")
            toAlso = Sheets(1).Range("G15")
            eSubject = "Reviews/actions"
            eBody = "Dear Sir" & vbCrLf & vbCrLf & "You currently have actions/reviews required on assessments on the following."
            On Error Resume Next
            With OutMail
            .To = toList
            .CC = toAlso
            .BCC = ""
            .Subject = eSubject
            .Body = eBody
            .bodyformat = 1
            .Send
            End With
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
    Next i
    ActiveWorkbook.Save
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
    End If
    Next ws
    End Sub
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    It would be helpful to post your sample data workbook with any personal sensitive info removed. In terms of sending only one email there are several ways to do this. I recommend adding a counter variable that will be incremented by 1 each time a "YES" value is found in your existing "IF" statement and then add the email procedure to the end of the macro in a completely separate "IF" statement to evaluate whether the counter variable is greater than whatever value you specify.

    Rollin
     
    Last edited: Feb 25, 2016
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1166777

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice