VBA to create email using values from different columns

[mpatino]

Hi Gurus,

I need a way to create emails based on cell value, for example in Column T, I will have "Yes" / "No" on each row, so, if the row has "Yes", then, that row should generate an email with values from the same row, but different columns, for example:

To: Data from column B
Subject: Data from column D & A
Body:
Salutation + Data from Column M + signature

This should generate as many emails as "Yes's" are in the worksheet Column T

Appreciate any assistance provided.

 

[JazzSP8]

Try this;

Sub EmailStuff()
Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String
Dim strTo As String
Dim strSub As String
Dim LastRow As Integer

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

LastRow = Range("T" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

    If Cells(i, "T").Value = "Yes" Then
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)

        strTo = Cells(i, "B").Value
        
        strSub = Cells(i, "D").Value & " " & Cells(i, "A").Value
        
        strBody = "Good Morning" & vbNewLine & vbNewLine
        strBody = strBody & Cells(i, "M").Value & vbNewLine & vbNewLine
        strBody = strBody & "Regards" & vbNewLine & vbNewLine & "JazzSP8"
        
        On Error Resume Next
        With OutMail
            .to = strTo
            .Subject = strSub
            .Body = strBody
            .Display   ' Change to .Send to automatically send
        End With
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing

    End If

Next i

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[mpatino]

Hi JazzSP8,

Thank you so very much, this works like charm, I really appreciate you taking the time to help me and this community.