I'm trying to create a VBA macro that will look into the A column, find all unique email addresses, create a new outlook email for each and populate the body of that email with the rows where that email is present (also including the header).
Example data:
+----------------+---------------------+---------+
| Email | Application | Version |
+----------------+---------------------+---------+
| [email protected] | Microsoft_Office_13 | v2.0 |
| [email protected] | Putty | v3.0 |
| [email protected] | Notepad | v5.6 |
| [email protected] | Microsoft_Office_13 | v2.0 |
| [email protected] | Putty | v3.0 |
| [email protected] | Adobe_Reader | v6.4 |
| [email protected] | Microsoft_Office_13 | v3.6 |
| [email protected] | Paint | v6.4 |
| [email protected] | Adobe_Reader | v6.4 |
+----------------+---------------------+---------+
This is what I was able to find in my research, but it will create an email for every time the address is listed. It also doesn't really have any code which shows how to pull a range of cells into the body.
Sub Test1() Dim OutApp As Object Dim OutMail As Object Dim cell As Range Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") On Error GoTo cleanup For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants) If cell.Value Like "?*@?*.?*" And _ LCase(Cells(cell.Row, "C").Value) = "yes" Then Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = cell.Value .Subject = "Reminder" .Body = "Hi, please find your account permissions below:" .Display End With On Error GoTo 0 Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing Application.ScreenUpdating = True End Sub
The desired email output would be something like:
Hi, please find your account permissions below:
+----------------+---------------------+---------+
| Email | Application | Version |
+----------------+---------------------+---------+
| [email protected] | Microsoft_Office_13 | v2.0 |
| [email protected] | Putty | v3.0 |
| [email protected] | Adobe_Reader | v6.4 |
+----------------+---------------------+---------+