Excel Macro For Sending Email, Question About Email Body

[scottcutler]

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!

A B C D E F G H I J K L M N O P Q # Name Submittal Type Subcontractor Contact Email Notify Days Submit Days Approve Days Onsite Hold Submitted Needed 031000 Concrete Formwork Formwork Release Agent action Core Mike Muhlena mikem@corestructuralservices.com 3/22/2016 42 5/3/2016 42 5/31/2016 14 6/14/2016 X YES 031000 Concrete Formwork Product Data action Core Mike Muhlena mikem@corestructuralservices.com 3/22/2016 42 5/3/2016 42 5/31/2016 14 6/14/2016 X NO 031000 Concrete Formwork Testing For Formwork Removal action Core Mike Muhlena mikem@corestructuralservices.com 3/22/2016 42 5/3/2016 42 5/31/2016 14 6/14/2016 X NO 031000 Concrete Formwork Shop Drawings action Core Mike Muhlena mikem@corestructuralservices.com 3/22/2016 42 5/3/2016 42 5/31/2016 14 6/14/2016 X NO <tbody> </tbody> <strike></strike>

<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

 

[Momentman]

I have had to do something like this in the past

Maybe you could , in the macro,do a filter in the sheet and then copy the filtered data to a new sheet which gets attached.

 

[scottcutler]

I have had to do something like this in the past

Maybe you could , in the macro,do a filter in the sheet and then copy the filtered data to a new sheet which gets attached.

Thank you very much for your response. This could be the best solution, but if possible, I would like to use a logical test to filter the data, and include the filtered data in the body of the email. The email would be very simple with text, and then a list of the filtered data below. However, I continue to get error messages in the macro when I try to put a logical test within the email body. Do you know if what I'm trying to do is even possible? And if so, do you have any recommendations for how it should be done to avoid error messages? If this is not possible, perhaps I will consider an attached sheet as you recommend. Best,

 

[Mohammad Basem]

Hi scottcutler,

You may use the For Each…Next loop to build the body of the e-mail in a string variable and send the e-mail after the loop.

 

[scottcutler]

Hi scottcutler,

You may use the For Each…Next loop to build the body of the e-mail in a string variable and send the e-mail after the loop.

Thank you for your response. I think this is getting closer! I have inserted the following code into the body of the email:

For Each cl In ActiveSheet.Range("A1:Q190")
If InStr(cl.Value, "q") = "yes" Then
Cells(cell.Row, "J").Value
End If
Next cl

My goal is for a row in the given range, if the value in column "q" is "yes", then to insert the value in column "j" into the body of the email. However, when I do a test run, I don't get the value from column "j", when the value in column "q" is "yes". The macro runs without bugs, it just doesn't return the value as desired. Any ideas what is wrong? Do I need to dim cl? Any thoughts would be much appreciated. Thanks a bunch!

 

[Mohammad Basem]

Hi cottcutler,

Do I need to dim cl?

This would be a very good practice.

The macro runs without bugs…

This line returns an error,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Cells(cell.Row, "J").Value[/COLOR][/SIZE][/FONT]

but, because you have used error traps

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]On Error GoTo cleanup

On Error Resume Next[/COLOR][/SIZE][/FONT]

The error was trapped by one of these depending on where the new code is located. That's why you are not getting the error.

My goal is for a row in the given range, if the value in column "q" is "yes", then to insert the value in column "j" into the body of the email…

Do you mean you want to insert the same multiple values from (J1:J190) into the body of each e-mail?

If the answer is yes, then what would be the separator between the different values?

 

[scottcutler]

Hi cottcutler,



This would be a very good practice.



This line returns an error,

[FONT=Consolas][SIZE=2][COLOR=navy]Cells(cell.Row, "J").Value[/COLOR][/SIZE][/FONT]

but, because you have used error traps

[FONT=Consolas][SIZE=2][COLOR=navy]On Error GoTo cleanup

On Error Resume Next[/COLOR][/SIZE][/FONT]

The error was trapped by one of these depending on where the new code is located. That's why you are not getting the error.



Do you mean you want to insert the same multiple values from (J1:J190) into the body of each e-mail?

If the answer is yes, then what would be the separator between the different values?

Thank you. My goal is for each row in the excel spreadsheet where the value of cell "q" = "yes", to insert the value in cell "j" from that same row of the spreadsheet into the body of the email. It would be ideal if there was a new line in the body of the email for each value returned (i.e. the separator would be a new line). Thank you very much for any assistance. Please feel free to ask any addition questions for me to clarify.

 

[scottcutler]

At this point, the code looks like this:

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 following submittal packages are due" _
& " for submission. Specific information for these submittal" _
& " packages can be found in the Project Specification. Please feel free" _
& " to contact me with any questions." & vbNewLine & vbNewLine & "Thank you,"

For Each cl In ActiveSheet.Range("A1:Q190")
If InStr(cl.Value, "q") = "yes" Then
Cells(cell.Row, "J").Value
End If
Next cl

'.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

 

[Mohammad Basem]

Hi cottcutler,

Try this; the body text is built before the main loop

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Sub Test1()

   Dim sBodyText As String
   Dim OutApp As Object
   Dim OutMail As Object
   Dim rCell As Range

   Application.ScreenUpdating = False
   Set OutApp = CreateObject("Outlook.Application")
   [COLOR="Green"]'[/COLOR]
   [COLOR="Green"]' Build body text[/COLOR]
   sBodyText = "This email is a reminder that the following submittal packages are due" _
      & " for submission. Specific information for these submittal" _
      & " packages can be found in the Project Specification. Please feel free" _
      & " to contact me with any questions." & vbNewLine & vbNewLine & "Thank you," _
      & vbNewLine & vbNewLine
   For Each rCell In ActiveSheet.Range("A1:Q190").Rows
      If LCase(rCl.Cells(, "Q")) = "yes" Then
         sBodyText = sBodyText & rCl.Cells(, "J").Value & vbNewLine
      End If
   Next rCell
   [COLOR="Green"]'[/COLOR]
   [COLOR="Green"]' Send e-mails[/COLOR]
   On Error GoTo CleanUp
   For Each rCell In Columns("G").Cells.SpecialCells(xlCellTypeConstants)
      If rCell.Value Like "?*@?*.?*" And _
         LCase(Cells(rCell.Row, "Q").Value) = "yes" Then

         Set OutMail = OutApp.CreateItem(0)
         On Error Resume Next
         With OutMail
            .To = rCell.Value
            .Subject = "DMPS Submittal Reminder"
            .Body = sBodyText
            [COLOR="Green"]'.Attachments.Add ("C:\test.txt")[/COLOR]
            .Send
         End With
         On Error GoTo 0
         Set OutMail = Nothing
      End If
   Next rCell

CleanUp:
   Set rCell = Nothing
   Set OutApp = Nothing
   Set OutMail = Nothing
   Application.ScreenUpdating = True

End Sub[/COLOR][/SIZE][/FONT]

I am a bit curious about the way you set-up the error traps. The error trap before the main loop stops the macro in case of error, while the one inside the loop keeps the macro running. Could you please explain this?

 

[scottcutler]

Hi cottcutler,

Try this; the body text is built before the main loop

[FONT=Consolas][SIZE=2][COLOR=navy]Sub Test1()

   Dim sBodyText As String
   Dim OutApp As Object
   Dim OutMail As Object
   Dim rCell As Range

   Application.ScreenUpdating = False
   Set OutApp = CreateObject("Outlook.Application")
   [COLOR=green]'[/COLOR]
   [COLOR=green]' Build body text[/COLOR]
   sBodyText = "This email is a reminder that the following submittal packages are due" _
      & " for submission. Specific information for these submittal" _
      & " packages can be found in the Project Specification. Please feel free" _
      & " to contact me with any questions." & vbNewLine & vbNewLine & "Thank you," _
      & vbNewLine & vbNewLine
   For Each rCell In ActiveSheet.Range("A1:Q190").Rows
      If LCase(rCl.Cells(, "Q")) = "yes" Then
         sBodyText = sBodyText & rCl.Cells(, "J").Value & vbNewLine
      End If
   Next rCell
   [COLOR=green]'[/COLOR]
   [COLOR=green]' Send e-mails[/COLOR]
   On Error GoTo CleanUp
   For Each rCell In Columns("G").Cells.SpecialCells(xlCellTypeConstants)
      If rCell.Value Like "?*@?*.?*" And _
         LCase(Cells(rCell.Row, "Q").Value) = "yes" Then

         Set OutMail = OutApp.CreateItem(0)
         On Error Resume Next
         With OutMail
            .To = rCell.Value
            .Subject = "DMPS Submittal Reminder"
            .Body = sBodyText
            [COLOR=green]'.Attachments.Add ("C:\test.txt")[/COLOR]
            .Send
         End With
         On Error GoTo 0
         Set OutMail = Nothing
      End If
   Next rCell

CleanUp:
   Set rCell = Nothing
   Set OutApp = Nothing
   Set OutMail = Nothing
   Application.ScreenUpdating = True

End Sub[/COLOR][/SIZE][/FONT]

I am a bit curious about the way you set-up the error traps. The error trap before the main loop stops the macro in case of error, while the one inside the loop keeps the macro running. Could you please explain this?

Thank you very much for your help. This code looks really good! I do have one issue, when I run the code I get an error message, "Run-time error '424': Object Required". When I hit debug, the line that has "If LCase(rCl.Cells(, "Q")) - "yes" Then", is highlighted. When I researched this error message I got, "Check that references to an object property or method have valid object qualifier. Specify an object qualifier if you didn't provide one." Do you by chance know what the issue is?

With regard to the error traps, the example that I used set them up this way. I wish I had more information to share. Would you recommend a different error trap setup? Thank you!