Send emails using VBA, recipient needs a list of task items from a column in body of email

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

Christian Smithson

Posted 2019-07-16T21:37:16.173

Reputation: 1

No answers