I am attempting to send emails to a distribution list based on a trigger column (column E). Each row that contains the word "Captured" should send an email to the address in column A. The email body for these outgoing emails should contain content in column D. Ideally I would like the Name (B) and Client (C) to dynamically appear in the Subject of the email.
The code below, fires to the correct recipients but sends the same message body (last one within the list to all email addresses).
I am in need of this for a large project so any help would be amazing!
Dim outlookapp As Object
Dim Outlookmailitem As Object
Dim Icounter As Integer
Dim maildest As String
Dim mess As String
Set outlookapp = CreateObject("outlook.application")
Set Outlookmailitem = outlookapp.CreateItem(0)
With Outlookmailitem
maildest = ""
For Icounter = 1 To WorksheetFunction.CountA(Columns(1))
If maildest = "" And Cells(Icounter, 1).Offset(0, 4) = "Captured" Then
mess = Cells(Icounter, 4).Value
maildest = Cells(Icounter, 1).Value
ElseIf maildest <> "" And Cells(Icounter, 1).Offset(0, 4) = "Captured" Then
mess = Cells(Icounter, 4).Value
maildest = maildest & ";" & Cells(Icounter, 1).Value
End If
Next Icounter
.CC = maildest
.Subject = "name and Client"
.Body = mess
.Send
End With
Set Outlookmailitem = Nothing
Set outlookapp = Nothing
End Sub
Bookmarks