Email Groups of Data from Rows

[oxbcat]

I would like to request help with a macro I am writing. The macro is going to take data and filter it to a list and format like below. Once in this format I need the macro to email the groups their open tasks. I assume I would use the auto filter to filter by column d for open status Items. But I cannot figure out how to send 1 email to HR containing ID's 1 and 6 only, for example.

The emails will all follow the format of "[group]<group>.admin". This name will be resolved in exchange. So HR.admin, IT.admin etc.

Any Ideas on how to loop through this list? Keep in mind this list will change daily and once the macro runs It will have the data in this standardized format. I am writing this in Excel 2003 also.

</group>

 

[Trevor G]

Take a look at the Datapig website there is a tutorial on sending Excel data via Outlook, so this will deal with the loop you need.

Here is the link.

http://www.datapigtechnologies.com/ExcelMain.htm

Look down the list until you find Using Excel Macros

http://www.datapigtechnologies.com/flashfiles/excelsendemail.html

 

[oxbcat]

Trevor,

While I appreciate the reply. Your demo does not solve my problem. Most emailing tutorials are all dealing with a 1:1 relationship. Where 1 email to be kicked off per row of email addresses.

I need to group all similar items into one email. That is the challenge I am trying to tackle.

 

[Trevor G]

Try looking to adapt this then

Sub EmailRange()
'*******************************************************************************
'This code requires you to Set the References. GoTo Tools > Reference >Search
'For Microsoft Outlook (A number) .Object Library and tick the box
'The following sample will look to email a filtered list of data with todays date
'Created by Trevor Gl
'May 2011
'*******************************************************************************
Dim OutlookApp As Outlook.Application
Dim MailSelection As Object
Dim cell As Range
Dim Subject As String
Dim EmailAddress As String
ThisWorkbook.Sheets("Karl To Elsie").Select
Range("A3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$j$35").AutoFilter Field:=4, Criteria1:= _
xlFilterToday, Operator:=xlFilterDynamic 'Adjust the range of cells
Range("A3").Select
ActiveCell.CurrentRegion.Copy
Set OutlookApp = CreateObject("Outlook.Application")
Set MailSelection = OutlookApp.CreateItem(0)
With MailSelection
.To = "trevor_g.com" 'Change to email address
.Subject = "Invoice"
.Display 'Change to send once checked.
SendKeys "^({v})", True 'This is the same as using Paste
End With
' End If
'Next
End Sub