Excel VBA: Email List of Tasks Assigned To A User

[JonnieO]

I created a project task spreadsheet that has a GANTT chart as a part of it using formulas and conditional formatting. It works rather well for tracking tasks, their completion %, task owner, etc. There are several "sections" that list tasks which fall under that classification.

Every Tuesday I send an email to each person with an assigned task and in the body of the email I copy and paste the rows assigned to the user I'm emailing at the time, asking for updates prior to the project meeting on the upcoming Friday.

I want to have Excel grab the email address of the user assigned to the task, also copy each row that contains tasks they are assigned to, paste that that into the body of an email (along with a small blurb about providing updates), then send that email. As I said, right now I do all of that manually, which is taking too much time with the other responsibilities that I've been given.

Any help you can provide would be great. Thank you for taking time to read this.

 

[Kenneth Hobson]

If you set your data where AutoFilter gets what you need, that makes it easier. The chart is best sent as an attachment I think.

Here is an example. Set the reference as commented. Add the routine from Ron de Bruin's site as well. Change To, Subject, and such to suit.

Sub SendToOutlook1()
  'Tools > References > Microsoft Outlook xx.0 Object Library
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  Dim sig$
  
  Set olApp = New Outlook.Application
  Set olMail = olApp.CreateItem(olMailItem)
  With olMail
    .Display
    sig = .HTMLBody
    .To = "ken@gmail.com"
    .Subject = "Restaurant Summary Report"
    'http://www.rondebruin.nl/win/s1/outlook/bmail2.htm
    .HTMLBody = RangetoHTML([A1:H22]) & sig
    .Display
    '.Send
  End With
  Set olMail = Nothing
  Set olApp = Nothing
End Sub

 

[JonnieO]

Thanks Kenneth, this help to get me on the right track. I am light years closer now than I was with this.