No announcement yet.

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

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 .
    Filter1 Filter2 Project DM Date Currency Field 1 Field2 Field3 Costing Margin% mailinglist
    ABCD A XYZ 11/2/2019 AN 0 9 9 7 5 [email protected]

  • #2
    can you attach a small example workbook, replace personal details with dummy ones
    Hope that Helps


    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.


    • #3
      Hi ,

      Please find the attached sample.
      Attached Files


      • #4
        Which Range do you want to copy?
        Do you want to paste into the body of the email?
        Are you using OutLook?

        I've looked at the example but I'm not clear what you mean by AutoFilter
        Hope that Helps


        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.


        • #5
          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 ,


          • #6
            I have mentioned the code
            Last edited by trying_still; July 16th, 2019, 18:05.


            • #7

              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))

              FieldNum = 1
              FieldNum1 = 2

              If ws1.FilterMode Then
              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
              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

              Application.CutCopyMode = False
              On Error Resume Next
              .DrawingObjects.Visible = True
              On Error GoTo 0
              End With
              '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, _
              .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
              RangetoHTML = Replace(RangetoHTML, "align=center xublishsource=", _
              "align=left xublishsource=")

              '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, _
              On Error GoTo 0
              End Function