vba excel to Outlook

[dshafique]

Hey Guys, Im trying to create a macros for an application report. the report contains application data and the status if they're expired or not. for each application which is expired, i want to email the whole row of data to the owner and the technician. the names are also in the same row.

Here is what I have so far:

or Each cl In Range("PAD[PAD ID]")

   Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = ActiveCell.Offset(0, 2).Value
        .CC = ActiveCell.Offset(0, 7).Value
        .BCC = ""
        .Subject = "CIO Ticket Automation test"
        .HTMLBody = ThisWorkbook.Sheets("PAD").Range("A2:K2").Value
        ' In place of the following statement, you can use ".Display" to
        ' display the e-mail message.
        .Display
    End With
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   Next cl

i get the "to" value from the 3rd column of my table and my "cc:" value from the 8th column. but everytime i run this loop, it only puts the people from my first row in outlook every run. and it doesnt put anything in the body for the email.

i am fairly new to vba with outlook, any and all support would be greatly appreciated.

 

[Kenneth Hobson]

1. Move the createobject line to before the loop.
2. Replace ActiveCell with cl.
3. Use Ron de Bruin's RangeToHTML() to convert a range to well, html.

https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

If you need help building the range for RangeToHTML(), post back. Obviously, you would use cl.Row as part of that. e.g.

.htmlBody = RangeToHTML(Range("A" & cl.row, "E" & cl.Row)))

 

[dshafique]

Thank you Kenneth, this is exactly what I needed! i just changed from A to J to fit my data.