Mail to two or more recipients based on the autofilter of 1st and 2nd column

  • Hi ,please help in developing a code to send Mail to two or more recipients(mailing list) based on the autofilter of first and second column after copying range to mail .
    My data looks like this .
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 215"]Filter1[/TD]
    [TD="width: 98"]Filter2 [/TD]
    [TD="width: 188"]Project DM[/TD]
    [TD="width: 248"]Date[/TD]
    [TD="width: 64"]Currency[/TD]
    [TD="width: 64"]Field 1[/TD]
    [TD="width: 64"]Field2[/TD]
    [TD="width: 64"]Field3[/TD]
    [TD="width: 64"]Costing[/TD]
    [TD="width: 64"]Margin%[/TD]
    [TD="width: 227"]mailinglist[/TD]

    [/tr]


    [tr]


    [td]

    ABCD

    [/td]


    [td]

    A

    [/td]


    [td]

    XYZ

    [/td]


    [TD="align: right"]11/2/2019[/TD]

    [td]

    AN

    [/td]


    [TD="align: right"]0[/TD]
    [TD="align: right"]9[/TD]
    [TD="align: right"]9[/TD]
    [TD="align: right"]7[/TD]
    [TD="align: right"]5[/TD]

    [td]

    "[email protected]"

    [/td]


    [/tr]


    [/TABLE]

  • Hi Roy,


    I want to copy the columns in each row from the first to last one .The criteria one is selecting the entries in the first column then filtering the second column.
    These have to be copied to the mail body and then sent to all the mail addresses mentioned in the mail id(mentioned in "to" ,not in cc ) column in the same mail .


    Thanks ,

  • Sub Mail_Selection_Range_Outlook_Body()

    Dim rng As Range
    Dim OutApp As Object 'Dim OutApp As Outlook.Application
    Dim OutMail As Object 'Dim OutMail As Outlook.MailItem
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim body1 As String, body2 As String, mail_Message As String, mail_Subject As String, mail_from As String, mail_on_behfalfof As String
    Dim last_row, last_row2 As Long
    Dim last_col, last_col2 As Integer
    Dim I As Integer, J As Integer


    I = 1
    J = 1
    Set rng = Nothing

    mail_Message = "ABCD."
    mail_Message_end = "ABCD "
    mail_Subject = "ABCD"
    mail_from = "ABCD"
    mail_on_behalfof = "ABCD"

    Set ws1 = ThisWorkbook.Worksheets("Mail")
    Set ws2 = ThisWorkbook.Worksheets("do")

    Set My_Range = Range("A1:Z" & LastRow(ws1))
    My_Range.Parent.Select


    FieldNum = 1
    FieldNum1 = 2


    If ws1.FilterMode Then
    ActiveSheet.ShowAllData
    End If

    last_row = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    last_row1 = ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    last_row2 = ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row

    last_col = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
    last_col2 = ws2.Cells(1, ws1.Columns.Count).End(xlToLeft).Column


    With ws2
    'first we copy the Unique data from the filter field to ws2
    My_Range.Columns(FieldNum).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=.Range("A1"), Unique:=True

    My_Range.Columns(FieldNum1).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=.Range("B1"), Unique:=True

    End With


    ws1.Range(Cells(1, 1), Cells(last_row, last_col)).AutoFilter


    For I = 1 To last_row1 - 1

    body1 = "<P STYLE='font-family:Calibri (Body);font-size:14.5'>" & "Hi " & "," & "<br>" & "<br>" & mail_Message & "<br>" & "</p>"
    body2 = "<P STYLE='font-family:Calibri (Body);font-size:14.5'>" & "<br>" & mail_Message_end & "<br>" & "Regards," & "<br>" & mail_from & "</p>"

    ws1.AutoFilterMode = False
    ws1.Range(Cells(1, 1), Cells(1, last_col)).AutoFilter Field:=1, Criteria1:=ws2.Range("A1").Offset(I, 0).Value

    For J = 1 To last_row2 - 1


    ws1.Range(Cells(1, 1), Cells(1, last_col)).AutoFilter Field:=2, Criteria1:=ws2.Range("B1").Offset(J, 0).Value

    Set rng = ws1.Range(Cells(1, 1), Cells(last_row, last_col)).SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If (ws1.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1) Then


    ' If rng Is Nothing Then
    ' MsgBox "The selection is not a range or the sheet is protected" & vbNewLine & "please correct and try again.", vbOKOnly
    ' Exit Sub
    ' End If


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


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    'Set OutMail = OutApp.CreateItem(olMailItem)
    Dim Ldate As Date
    On Error Resume Next
    With OutMail
    .SentOnBehalfOfName = mail_on_behfalfof
    .To = ws1.Range("A1").Offset((ActiveCell.Row), (ActiveCell.Column) + 10).Value
    .CC = ""
    .BCC = ""
    .Subject = mail_Subject
    .HTMLBody = body1 & RangetoHTML(rng) & body2
    .SendUsingAccount = OutApp.Session.Accounts.Item(2)
    .Display 'use .Send or .Display for testing
    End With
    On Error GoTo 0


    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With


    Set OutMail = Nothing
    Set OutApp = Nothing

    End If
    Next J
    Next I
    End Sub



    Function RangetoHTML(rng As Range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook


    TempFile = Environ$("temp") & "" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    .Cells(1).EntireRow.AutoFit
    .Cells(1).EntireColumn.AutoFit

    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
    End With
    TempWB.Sheets(1).UsedRange.Columns.AutoFit
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=TempFile, _
    Sheet:=TempWB.Sheets(1).Name, _
    Source:=TempWB.Sheets(1).UsedRange.Address, _
    HtmlType:=xlHtmlStatic)
    .Publish (True)
    End With


    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
    "align=left x:publishsource=")


    'Close TempWB
    TempWB.Close savechanges:=False


    'Delete the htm file we used in this function
    Kill TempFile


    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    End Function


    Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
    After:=sh.Range("A1"), _
    Lookat:=xlPart, _
    LookIn:=xlValues, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    On Error GoTo 0
    End Function