Hi deepu,
Thanks you so much for your reply
I have tried to paste the code in module but it not work properly request
you to kindly provide me the excel sheet with your macro code but before
you send me the file required small changes do not use K & M column for
Chennel name & email address that is given just for information my actual
database is from A to G request you to kindly change the macro code
accordingly & send it back for the other checks also if you want u can
create the summary sheet with the help of macro & paste it on sheet 2 &
also for name u can use Column H for the proper output.1 more thing i want
to tell you that a have to send mail with our official signature so if u
can apply macro logic for the same also it would be very great ful to u.
Request you to kindly make the changes & resend it for the final check
Thanks in Advance & thanks for your time & support.
On Mon, Nov 14, 2011 at 4:10 PM, deepu <deepaktheind...@gmail.com> wrote:
> Hi Maulik,
>
> Below is the code that you're looking for...
> Insert a module in your workbook and paste the following code and run
> it...
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Dim MyArray() As Variant, ListArray() As Variant
> Dim R As Long, C As Integer, LastRow As Long, i As Long
> Dim OutObj As Object, EmailObj As Object, EmailBody As String, RecAdd
> As String
> Sub PrepareData()
> 'Assigning values to MyArray
> LastRow = Sheet1.Range("A60000").End(xlUp).Row
> MyArray = Sheet1.Range("A7:G" & LastRow).Value
>
> 'Assigning values to ListArray
> LastRow = Sheet1.Range("K60000").End(xlUp).Row
> ListArray = Sheet1.Range("K7:M" & LastRow).Value
>
> For i = LBound(ListArray) To UBound(ListArray)
> RecAdd = ListArray(i, 3)
> EmailBody = "Hi " &
> Application.WorksheetFunction.Proper(ListArray(i, 2)) & ",<BR><P
> Align=""""center"""">FYI...<BR><BR>" & _
> "<TABLE Border=""1"", Cellspacing=""0"", Bgcolor=#EEECE1><TR>" & _
> "<TD Bgcolor=""#14498A"", Align=""Center""><Font
> Color=#FFFFFF>Customer Name</Font></TD>" & _
> "<TD Bgcolor=""#14498A"", Align=""Center""><Font
> Color=#FFFFFF>Location</Font></TD>" & _
> "<TD Bgcolor=""#14498A"", Align=""Center""><Font
> Color=#FFFFFF>Date</Font></TD>" & _
> "<TD Bgcolor=""#14498A"", Align=""Center""><Font
> Color=#FFFFFF>Site</Font></TD>" & _
> "<TD Bgcolor=""#14498A"", Align=""Center""><Font
> Color=#FFFFFF>Status</Font></TD>" & _
> "</TR>"
>
> For R = LBound(MyArray) To UBound(MyArray)
> If ListArray(i, 1) = MyArray(R, 6) Then
> For C = 1 To 5
> EmailBody = EmailBody & "<TR>" & _
> "<TD>" & MyArray(R, 1) & "</TD>" & _
> "<TD>" & MyArray(R, 2) & "</TD>" & _
> "<TD>" & MyArray(R, 3) & "</TD>" & _
> "<TD>" & MyArray(R, 4) & "</TD>" & _
> "<TD>" & MyArray(R, 5) & "</TD>" & _
> "</TR>"
> Next C
> End If
> Next R
> Call SendEmail(RecAdd, EmailBody)
> Next i
> End Sub
>
> Sub SendEmail(ReceiptAdd As String, EBody As String)
> Set OutObj = CreateObject("Outlook.Application.12")
> Set EmailObj = OutObj.CreateItem(0)
>
> EmailBody = EmailBody & "</Table><BR><BR>" & _
> "Regards<BR>XYZ"
>
> With EmailObj
> .To = ReceiptAdd
> .Subject = "Your Channels Data"
> .HTMLBody = EmailBody
> .Send
> End With
>
> End Sub
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Hope you'd like it.. Kindly contact to me if you face any problem...
>
> Regards
> Deepak
>
> On Nov 13, 10:00 pm, maulik desai <mauliksde...@gmail.com> wrote:
> > Hi Team,
> >
> > I am having 1 excel sheet in that i have to prepare Chennel wise detail
> > report on a every week & i have to send that information to respective
> > chennel heads wia email ,
> > Manually i need to do filter the data base on chennal name & then copy
> data
> > from sheet & need to paste on mail body then i have to send the data to
> > that chennel head based on chennel name.
> >
> > My problem is i have to do same excerside many times as chennel name are
> > approxly 80
> >
> > Request you to kindly provide me the macro code witch can directly send
> the
> > email based on chennel name (mail body format given in attched file
> ,sheet
> > 2)
> > Thanks in advance.
> > --
> > Thanks & Regards,
> > Maulik Desai
> > 9967363926
> >
> > Customer Info.xls
> > 33KViewDownload
>
> --
> FORUM RULES (934+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
Thanks & Regards,
Maulik Desai
9967363926
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
