Solved

Filter colum 'F" and send the user a mail with the details

Posted on 2008-10-24
5
264 Views
Last Modified: 2012-08-13
Hi,

Need to filter as per managers and mail them with the A,B,C,D,E data
With headers in a border box

Filter colum "F" find a manager mail him his subordinate details .
Related to such output
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23844631.html?cid=239#a22797712
Regards
Sharath
Full-Time-Internet-Users.xls
0
Comment
Question by:bsharath
  • 3
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 22799650
How this

Cheers

Dave
Option Explicit
 
 

Sub ManagerMail()

    Dim rng1 As Range, rng2 As Range, rng3 As Range

    Dim cel As Range

    Dim MyDic As Object

    Dim doit, FirstAddress As String

    Set MyDic = CreateObject("Scripting.Dictionary")
 

    Set rng1 = Sheets(1).Range([f2], Cells(ActiveSheet.Rows.Count, "f").End(xlUp)).SpecialCells(xlConstants)

    For Each cel In rng1

        Set rng3 = Nothing

        If cel.Value <> vbNullString Then

            If Not MyDic.exists(cel.Value) Then

                Set rng2 = rng1.Find(cel.Value, rng1.Cells(1), xlValues, xlWhole, xlByRows, xlPrevious)

                If Not rng2 Is Nothing Then

                    Set rng3 = Range(Cells(rng2.Row, "A"), Cells(rng2.Row, "E"))

                    FirstAddress = rng2.Address

                    Do

                        Set rng2 = rng1.FindNext(rng2)

                        Set rng3 = Union(rng3, Range(Cells(rng2.Row, "A"), Cells(rng2.Row, "E")))

                    Loop While FirstAddress <> rng2.Address

                End If

                MyDic.Add cel.Value, cel.Row

                Set doit = Mailem(cel.Value, rng3)

            Else

            End If

        End If
 

    Next

End Sub
 
 
 

Function Mailem(Recip As String, ByVal rng3 As Range)

    Dim outApp, outMail

    Dim tempStrStart As String, tempStrMid As String, tempStrFinish As String, tempStrCel As String, tempStr As String

    Dim r As Range, cel As Range

    Set outApp = CreateObject("Outlook.Application")
 

    tempStrStart = "<table border=1><tr><th>Groups</th><th>Full Name</th><th>Title</th><th>Description</th><th>Department</th></tr>"

    tempStrFinish = "</table>"
 

    For Each r In rng3.Rows

        tempStrCel = vbNullString

        For Each cel In r.Cells

            tempStrCel = tempStrCel & "<td>" & cel.Value & "</td>"

        Next

        tempStrMid = tempStrMid & tempStrCel & "</tr>"

    Next
 

    tempStr = tempStrStart & tempStrMid & tempStrFinish

    outApp.Session.Logon

    Set outMail = outApp.CreateItem(0)

    With outMail

        .To = Recip

        .Subject = "Groups"

        .htmlBody = "Hi " & Recip & ",<br>Below is the data.<br><br>" & tempStr & "<br><br>Regards<br>Sharath"

        .Recipients.ResolveAll

        .Display

    End With

    Set outMail = Nothing

    Set outApp = Nothing

End Function

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 22800058
Thanks Dave. The first mail is displayed the the first 2 rows then get an Run time error 13 error
When debug goes here
  Set doit = Mailem(cel.Value, rng3)
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22800196
try
doit = Mailem(cel.Value, rng3)

Cheers
0
 
LVL 11

Author Comment

by:bsharath
ID: 22800356
Thank U Dave worked perfect....
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22800447
thx for the grade
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
This article will show, step by step, how to integrate R code into a R Sweave document
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now