scottcutler
New Member
- Joined
- Dec 23, 2015
- Messages
- 10
Hello,
I wrote the macro below (with some help from an online posting) to send an email to the address in column "G", given the value in column "Q" is "yes". A snapshot of the excel spreadsheet is also included below. However, the problem is that the person with this email address has multiple items (rows) in the spreadsheet with value "yes" in column "Q". So every time I run the macro, they receive many emails.
I would like to amend the code so that every row with column "Q" being "yes" is sent in one email to the address given in column "G". The one email though, needs to contain data from all rows that have a value of "yes" in column "Q". Is it possible to embed the logical test within the body of the email to make this possible? Any help would be very much appreciated. Thanks a bunch!
<tbody>
</tbody>
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("G").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "Q").Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "DMPS Submittal Reminder"
.Body = "This email is a reminder that the " & Cells(cell.Row, "A").Value _
& ": " & Cells(cell.Row, "B").Value & ", " & Cells(cell.Row, "C").Value _
& " submittal package is due for submission on " & Cells(cell.Row, "J").Value & "." _
& " Specific information for this submittal" _
& " package can be found in the Project Specification. Please feel free" _
& " to contact me with any questions." _
& vbNewLine & vbNewLine & "Thank you,"
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
I wrote the macro below (with some help from an online posting) to send an email to the address in column "G", given the value in column "Q" is "yes". A snapshot of the excel spreadsheet is also included below. However, the problem is that the person with this email address has multiple items (rows) in the spreadsheet with value "yes" in column "Q". So every time I run the macro, they receive many emails.
I would like to amend the code so that every row with column "Q" being "yes" is sent in one email to the address given in column "G". The one email though, needs to contain data from all rows that have a value of "yes" in column "Q". Is it possible to embed the logical test within the body of the email to make this possible? Any help would be very much appreciated. Thanks a bunch!
<tbody> </tbody> |
<tbody>
</tbody>
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("G").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "Q").Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "DMPS Submittal Reminder"
.Body = "This email is a reminder that the " & Cells(cell.Row, "A").Value _
& ": " & Cells(cell.Row, "B").Value & ", " & Cells(cell.Row, "C").Value _
& " submittal package is due for submission on " & Cells(cell.Row, "J").Value & "." _
& " Specific information for this submittal" _
& " package can be found in the Project Specification. Please feel free" _
& " to contact me with any questions." _
& vbNewLine & vbNewLine & "Thank you,"
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
Last edited: