0
1
Basically I have a table that contains Email addresses, names, and task items. I want to send an email to each person, but I'm having trouble figuring out how I can send just one email to each recipient that contains their list of task items without the code sending an email for each single task. Column E has the email addresses, Column G has their first name, and Column F contains the tasks. Here is some dummy data similar to the way mine is structured (mine has an occasional blank row or two): sample data
Here is my code thus far:
Dim xRg As Range
Private Sub CommandButton1_Click()
Call notify
End Sub
Sub notify()
Dim Msg, Style, Title, Response
Dim rng As Range
For Each rng In Sheets("Email Logic").Range("E2:E252")
If (rng.Value <> "") Then
Call mymacro(rng.Value)
End If
Next rng
Msg = "Email notifications have been sent!"
Style = vbOKOnly
Title = "Notification Sent"
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then
'do nothing
Else
'do nothing
End If
End Sub
Private Sub mymacro(theValue As String)
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there " & Application.WorksheetFunction.Index(Sheets("Email Logic").Range("G2:G252") _
, Application.WorksheetFunction.Match(theValue, Sheets("Email Logic").Range("E2:E252"), 0)) & "!"
On Error Resume Next
With xOutMail
.To = theValue
.CC = "ethompson@jtafla.com"
.BCC = ""
.Subject = "Quarterly Report Updates"
.Body = xMailBody
.Display 'use .Display or .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
'Msg = "An email has been sent: " & theValue
'Style = vbOKOnly
'Title = "Notification Sent"
'Response = MsgBox(Msg, Style, Title)
'If Response = vbOK Then
'do nothing
'Else
'do nothing
'End If
End Sub