Creating emails from an Excel loop

Question

I have this sample:

My code is currently passing and creating emails based on the name in column H. Therefore, Approver1 receives one email for all of its people. I received it to cancel the repetition of the names of my employees. Example: Approver 1 receives an email saying “Please approve the time for all your employees below:” and then a list of names ... Sample1, Sample2 and Sample3. The sheet will often duplicate employees for each approver, as shown in my sheet above.

The code is well suited for the first set of cover names (there can be up to 10 of the same approvers in a line, all receiving one email address), and then works fine through any singles.

, , ; , . , , approver1 , approver2 , approver3 .

, , , , ?

Sub DivisionApprovals()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell, lookrng As Range
    Dim strdir As String
    Dim strFilename As String
    Dim sigString As String
    Dim strBody As String
    Dim strName As Variant
    Dim strName1 As Variant
    Dim strDept As Variant
    Dim strName2 As String
    Dim strbody2 As String
    Dim strName3 As Variant

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    Set rng = ActiveSheet.UsedRange

    r = 2

    Do While r <= rng.rows.count
        Set OutMail = OutApp.CreateItem(0)

        Set strName = rng.Cells(r, 1)
        Set strName3 = rng.Cells(r, 3)
        strName2 = Trim(Split(strName, ",")(1))

        strBody = "<Font Face=calibri>Dear " & strName2 & ", <br><br> Please approve the following divisions:<br><br>"

        With OutMail
            .To = rng.Cells(r, 2).Value
            .Subject = "Please Approve Divisions"
            List = strName3 & "<br>"


            Do While rng.Cells(r, 1).Value = rng.Cells(r + 1, 1)
                r = r + 1
                Set strDept = rng.Cells(r, 3)
                .Subject = "Approvals Needed!"
                List = .HTMLBody & strDept & "<br>"
                r = r + 1
                .HTMLBody = List
            Loop
            .HTMLBody = strBody & "<B>" & List & "</B>" & "<br>" & Signature
            .Display

        End With

        Set OutMail = Nothing
        r = r + 1
    Loop
    Set OutApp = Nothing
End Sub

Answer 1

, , . , OP .

: Do While, , . , , , , .

@clearningthisstuff , , , . , , , , , ( ?), , .

, . , AND , , .

, , , .

Sub Email_Macro()
'
' Email_Macro Macro
'
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell, lookrng As Range
    Dim strdir As String
    Dim strFilename As String
    Dim sigString As String
    Dim strBody As String
    Dim strName As Variant
    Dim strName1 As Variant
    Dim strDept As Variant
    Dim strName2 As String
    Dim strbody2 As String
    Dim strName3 As Variant
    Dim emailWS As Worksheet
    Dim nameCol As Double
    Dim deptCol As Double
    Dim lastRow As Double
    Dim startRow As Double
    Dim r As Double

    Dim depList As String
    deptList = ""


    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")


    Set emailWS = ThisWorkbook.ActiveSheet
    startRow = 2 ' starting row
    nameCol = 1 'col of name
    deptCol = 3 'col of dept

    'find the last row with a name in it from the name column
    lastRow = emailWS.Cells(emailWS.Rows.Count, nameCol).End(xlUp).Row

    'set variable to the starting row #
    r = startRow 'this is where the counting begins

    'sort the data first before going through the email process
    'assumes these are the only columns 1 (nameCol) thru 3 (deptCol) to sort
    'assumes you are sorting based on col 1 (nameCol)
    emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, deptCol)).Sort key1:=emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, nameCol))

    Do While r <= lastRow
        Set OutMail = OutApp.CreateItem(0)

        Set strName = emailWS.Cells(r, nameCol)
        Set strName3 = emailWS.Cells(r, deptCol)
        'careful the line below assumes there is always a comma separator in the name
        strName2 = Trim(Split(strName, ",")(1))

        strBody = "<Font Face=calibri>Dear " & strName2 & ", <br><br> Please approve the following divisions:<br><br>"

        With OutMail
            .To = emailWS.Cells(r, 2).Value
            .Subject = "Please Approve Divisions"
            deptList = strName3 & "<br>"


            Do While emailWS.Cells(r, 1).Value = emailWS.Cells(r + 1, 1)
                r = r + 1
                Set strDept = emailWS.Cells(r, 3)
                .Subject = "Approvals Needed!"
                deptList = deptList & strDept & "<br>"
            Loop
            .HTMLBody = strBody & "<B>" & deptList & "</B>" & "<br>" & Signature
            .Display

        End With

        Set OutMail = Nothing

        'conditionally increment the row based on the name difference
        If emailWS.Cells(r, 1).Value <> emailWS.Cells(r + 1, 1) Then
            r = r + 1 'increment if there is a new name or no name
            deptList = "" 'reset the department list
        Else 'Do nothing
        End If
    Loop
    Set OutApp = Nothing


End Sub

:

, , /? . , . , , .

Sub Email_Macro()
'
' Email_Macro Macro
'
    Dim OutApp As Object 'email application
    Dim OutMail As Object 'email object
    Dim strBody As String 'first line of email body
    Dim strName As String 'name in the cell we are processing
    Dim strDept As String 'dept of the name we are processing
    Dim previousName As String 'previous name processed
    Dim nextName As String 'next name to process

    Dim emailWS As Worksheet 'the worksheet selected wehn running macro
    Dim nameCol As Double 'column # of names
    Dim deptCol As Double 'column # of depts
    Dim lastRow As Double 'last row of data in column
    Dim startRow As Double 'row we wish to start processing on
    Dim r As Double 'loop variable for row
    'This will be the list of departments, we will build it as we go
    Dim depList As String
    Dim strSig As String 'email signature
    strSig = "Respectfully, <br> Wookie"

    deptList = "" 'empty intitialization
    previousName = "" 'empty intialization
    nextName = "" 'empty intialization

    'Turn off screen updating
    'Application.ScreenUpdating = False
    'choose email application
    Set OutApp = CreateObject("Outlook.Application")
    'set worksheet to work on as active (selected sheet)
    Set emailWS = ThisWorkbook.ActiveSheet
    startRow = 2 ' starting row
    nameCol = 1 'col of names, can also do nameCol = emailWS.Range("A1").Column
    deptCol = 3 'col of depts, can also do deptCol = emailWS.Range("A3").Column
    '** Advantage of the optional way is if you have many columns and you don't want to count them

    'find the last row with a name in it from the name column
    lastRow = emailWS.Cells(emailWS.Rows.Count, nameCol).End(xlUp).Row

    'sort the data first before going through the email process using Range sort and a key
    'assumes these are the only columns 1 (nameCol) thru 3 (deptCol) to sort
    'assumes you are sorting based on col 1 (nameCol)
    emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, deptCol)).Sort key1:=emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, nameCol))

    'Set up our loop, it will go through every cell in the column we select in the loop
    For r = startRow To lastRow
        'Get the name and dept
        'For the name we will split around the comma and take the second part of array (right of comma)
        strName = Trim(Split(emailWS.Cells(r, nameCol), ",")(1))
        strDept = emailWS.Cells(r, deptCol)

        'if the next name is not blank (EOF)
        If emailWS.Cells(r + 1, nameCol) <> "" Then
           'assign the next name
           nextName = Trim(Split(emailWS.Cells(r + 1, nameCol), ",")(1))
        Else
           'this is your EOF exit so assume a name
           nextName = "Exit"
        End If 'Else do noting on this If

        If strName <> previousName Then
            'Set our "new" name to previousName for looping
            'process the "new" name
            previousName = strName
            'create the email object
            Set OutMail = OutApp.CreateItem(0)
            'Process as new email
            With OutMail
                .To = strName 'address email to the name
                .Subject = "Please Approve Divisions" 'appropriate subject
                deptList = strDept & "<br>" 'add the dept to dept list
                'Build the first line of email body in HTML format
                strBody = "<Font Face=calibri>Dear " & strName & ", <br><br> Please approve the following divisions:<br><br>"
            End With
        Else
            'The name is the same as the email we opened
            'Process Dept only by adding it to string with a line break
            deptList = deptList & strDept & "<br>"
        End If

        'Do we send the email and get ready for another?
        If strName <> nextName Then
            'the next name is not the same as the one we are processing and we sorted first
            'so it is time to send the email
            OutMail.HTMLBody = strBody & "<B>" & deptList & "</B>" & "<br><br>" & strSig
            OutMail.Display

        Else 'Do Nohing
        End If

Next r 'move to the next row

'nullify email reference
Set OutMail = Nothing
Set OutApp = Nothing


End Sub

, , , , :

    End With
Else
    'The name is the same as the email we opened
    'Process Dept only by adding it to string with a line break
    If InStr(deptList, strDept) = 0 Then
        'Dept is not in the list so Add the department
        deptList = deptList & strDept & "<br>"
    Else
        'Do nothing, the dept is already there
    End If
End If

. , , ( ).

- WWC

Answer 2

, , A C. . , .

Option Explicit

Private Sub CommandButton1_Click()
 Dim thisWS As Worksheet
 Dim firstRow As Double
 Dim lastRow As Double
 Dim workCol As Double
 Dim dataRange As Range
 Dim uniqueLast As Double
 Dim uniqueCol As Double
 Dim i As Double
 Dim y As Double
 Dim Temp As String
 Dim found_Bool As Boolean

 Set thisWS = ThisWorkbook.Worksheets("Sheet2")
 workCol = thisWS.Range("A1").Column
 firstRow = 1
 uniqueLast = 1
 uniqueCol = thisWS.Range("C1").Column
 lastRow = thisWS.Cells(thisWS.Rows.Count, workCol).End(xlUp).Row

 For i = firstRow To lastRow
    Temp = Trim(UCase(thisWS.Range(Cells(i, workCol), Cells(i, workCol))))
    Temp = Replace(Temp, "#", "")
    found_Bool = False
    For y = 1 To uniqueLast
        If Temp = thisWS.Range(Cells(y, uniqueCol), Cells(y, uniqueCol)) Then
          found_Bool = True
        Else ' Do nothing
        End If


    Next y

    If found_Bool = False Then
          thisWS.Range(Cells(uniqueLast + 1, uniqueCol), Cells(uniqueLast + 1, uniqueCol)) = Temp
          uniqueLast = uniqueLast + 1
    Else
    End If

 Next i
End Sub

non unique .

, , VBA ( (), vba pivot, , .

'***************************************************

, , . , "". , , . , , , , , .

, , , , , , , , :

   '***************************
    'Add Sales Pivot Table
   'Last DR is the last data row, you can see it done several times, in the code below, once you do it you will always do it
   'CalcSheet is the name of the worksheet in the workbook I am working on
   'The range here is defined in Range Format, you could use a named range or use .Range(Cells(row,col),Cells(row,col)) there are several ways
   'I name the pivot table upon creation so I can manipulate it better
   'I specify the target cell, upper left with which to begin the pivot table

   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        CalcSheet.Range("K14:AY" & LastDR), Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:=CalcSheet.Range("CA37"), TableName:="SalesPVT", DefaultVersion _
        :=xlPivotTableVersion15

, , :

With CalcSheet.PivotTables("SalesPVT").PivotFields("Salesperson")
    .Orientation = xlRowField
    .Position = 1
End With

With CalcSheet.PivotTables("SalesPVT").PivotFields("Customer")
    .Orientation = xlRowField
    .Position = 2
End With

With CalcSheet.PivotTables("SalesPVT").PivotFields("DD Rev")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "$#,##0"
End With

With CalcSheet.PivotTables("SalesPVT").PivotFields("Job Days")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "#,##0"
End With

CalcSheet.PivotTables("SalesPVT").PivotFields("Salesperson").AutoSort _
    xlDescending, "Sum of DD Rev"

, , , () ? , , , :

'Find the last row of Pivot table Data

Dim LastPVTrow As Double
Dim FirstPVTrow As Double
Dim NumPVTrows As Double
Dim PivCol As Double

PivCol = CalcSheet.Range("CB37").Column

FirstPVTrow = CalcSheet.Range("CB37").Row
LastPVTrow = CalcSheet.Cells(Rows.Count, PivCol).End(xlUp).Row
NumPVTrows = LastPVTrow - FirstPVTrow

- , , : ' Avg Rev/Job Day Column

    For i = 1 To NumPVTrows ' four columns in this table
        CalcSheet.Range("CD" & (100 + i)).NumberFormat = "$#,##0"
        If CalcSheet.Range("CC" & (FirstPVTrow + i)) <> 0 Then
            CalcSheet.Range("CD" & (100 + i)) = CalcSheet.Range("CB" & (FirstPVTrow + i)) / CalcSheet.Range("CC" & (FirstPVTrow + i))
        Else
            CalcSheet.Range("CD" & (100 + i)) = 0
        End If
   Next i

' , , , ..

' , , ,

'copy pivot table to get rid of it
 CalcSheet.PivotTables("SalesPVT").TableRange1.Copy
'Paste it as values with formatting
CalcSheet.Range("CA100").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Delete Sales Pivot from the file
 CalcSheet.PivotTables("SalesPVT").TableRange1.Delete

'Clear Work Space
CalcSheet.Range("CA1:CN500").Clear

, , , , , , , . : , , - , , , .

, ,

'***************************************
'Make the Customer Pivot and table
'***************************************

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        CalcSheet.Range("K14:AY" & LastDR), Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:=CalcSheet.Range("CA37"), TableName:="CustPVT", DefaultVersion _
        :=xlPivotTableVersion15

    With CalcSheet.PivotTables("CustPVT").PivotFields("Customer")
        .Orientation = xlRowField
        .Position = 1
    End With

    With CalcSheet.PivotTables("CustPVT").PivotFields("DD Rev")
        .Orientation = xlDataField
        .Function = xlSum
        .NumberFormat = "$#,##0"
    End With

    With CalcSheet.PivotTables("CustPVT").PivotFields("Job Days")
        .Orientation = xlDataField
        .Function = xlSum
        .NumberFormat = "#,##0"
    End With

'Find the last row of Pivot table Data

FirstPVTrow = CalcSheet.Range("CA37").Row
LastPVTrow = CalcSheet.Cells(Rows.Count, PivCol).End(xlUp).Row
'LastPVTrow = CalcSheet.Range("CB37:CB500").Find((0), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
NumPVTrows = LastPVTrow - FirstPVTrow

... .. ..,

, . , , (, ) , , , , " " , , , . , " ", , , , ., , . , . , .

Answer 3

, , .

Option Explicit

Sub LoopPivot()

With Sheet1

    Dim pt As PivotTable
    Set pt = .PivotTables(1)

    Dim nameField As PivotField
    Set nameField = pt.PivotFields("Name")

    Dim nameItem As PivotItem

    For Each nameItem In nameField.PivotItems

        Dim name As String
        name = nameItem.Value

        Dim emailField As PivotField
        Set emailField = pt.PivotFields("email")

        Dim emailItem As PivotItem
        Set emailItem = emailField.PivotItems(nameItem.Position)

        Dim email As String
        email = emailItem.Value

        Dim divisionName As Range

        Dim division As String
        division = vbNullString

        For Each divisionName In nameItem.DataRange

            division = division & "," & divisionName.Value

        Next

        division = Mid(division, 2, 255)

        Debug.Print name
        Debug.Print email
        Debug.Print division


    Next

End With


End Sub

Answer 4

Excel. , ADODB-Recordset:

Function RST_Excel(strExceldatei As String, strArbeitsblatt As String, strWHERE As String, Optional strBereich As String, _
                   Optional strDatenfelder As String = "*") As ADODB.Recordset

Dim i As Integer
Dim rst As ADODB.Recordset
Dim strConnection As String
Dim strSQL As String


On Error GoTo sprFehler

strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strExceldatei

If global_con Is Nothing Then

   Set global_con = New ADODB.Connection

   With global_con
        .Open strConnection
        End With

   End If

strSQL = "SELECT " & strDatenfelder & " FROM [" & strArbeitsblatt & "$" & strBereich & "] WHERE " & strWHERE

Set rst = New ADODB.Recordset

With rst
     .Source = strSQL
     .CursorLocation = adUseClient
     .ActiveConnection = global_con
     .Open
     Set RST_Excel = rst
     End With

sprEnde:

Set rst = Nothing

Exit Function


sprFehler:
Set rst = Nothing

Set RST_Excel = Nothing

End Function

ADODB-Recordset :

Dim strWHERE As String
Dim strFields As String
Dim rst_Recipients As ADODB.Recordset

strWHERE = "Surname IS NOT NULL AND Emailadress IS NOT NULL"
strFields = "Surname, Name, Emailadress, SMIME"

Set rst_Empfänger = RST_Excel(ThisWorkbook.FullName, "Email", strWHERE, "A1:M1000", strFields)

SQL-Statement, .

, Recordset:

With rst
     .movefirst

     do until .eof
        debug.print .fields("surename").value
        .movenext
        loop

end with

Answer 5

, , , .

Make a list in sheets ("Sheet1") with:

In column A : Names of the people
In column B : E-mail addresses
In column C:Z : Filenames like this C:\Data\Book2.xls (don't have to be Excel files)

The macro will go through each row in "Sheet1", and if column B has an email address and the file name (s) in column C: Z, it will create mail with this information and send it.

Sub Send_Files()
'Working in Excel 2000-2016
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    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("Sheet1")

    Set OutApp = CreateObject("Outlook.Application")

    For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)

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

                .Send  'Or use .Display
            End With

            Set OutMail = Nothing
        End If
    Next cell

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