Loop in Email with VBA

Question

Hello Good evening/day/afternoon, I have a question regarding writing an email using a macro.

Doubt this macro Test_logico() is done to read a column X and if different from "NO" it copies and pastes the values of column Y and Z of the same row...

EX: If the value of cell "A1" is different from no (equal to yes ) it takes in the same row the value of cell C1 and F1.

... I would like to paste this loop (since the check is done in more than one cell of the "X" column always with different sizes) within another macro that creates an Outlook email (in case this copy and paste would be within the body of the email. Body)

---

Sub Teste_logico()

Dim line As Integer

For line = 2 To Cells(Rows.Count, 11). End(xlUp). Row

If Cells(linha, 11) <> "NÃO" Then

    Cells(linha, 5).Copy

    Sheets("Planilha1").Select

End If

If Cells(linha, 11) <> "NÃO" Then

   Cells(linha, 2).Copy

End If

Next line

End Sub

---

Answer 1

This is the structure I use to create email with Excel:

Sub EnviarEmail()

Application.ScreenUpdating = False

    Dim finalRow As Integer
    Dim Email_Subject, Email_Send_From, Email_Send_To, _
    Email_Cc, Email_Bcc, Email_Body As String
    Dim Mail_Object, Mail_Single As Variant
    Dim Anexo, Distribuidor As String
Dim I As Integer

 'Selecionando Lista
Sheets("emails").Select

finalRow = Sheets("lista").Cells(Rows.Count, 1).End(xlUp).Row

'Call ListaArquivos

For I = 2 To finalRow

    Anexo = Sheets("lista").Cells(I, 1).Value
    Distribuidor = Sheets("lista").Cells(I, 3).Value

    Email_Subject = "Informativo Diário - " & Distribuidor
    Email_Send_To = Sheets("lista").Cells(I, 4).Value ' loop de emails
    Email_Cc = Sheets("lista").Cells(I, 7).Value

Email_Body = "Prezado Cliente," & vbCrLf & vbCrLf & _
"Ao abrir o arquivo, escolha a opção ""ATIVAR MACROS"" para que ele seja executado corretamente." & vbCrLf & _
"Em caso de dúvidas ou necessidade de maiores informações, pedimos  entrar em contato com o seu atendimento." & vbCrLf & vbCrLf & _
"Atenciosamente," & vbCrLf & vbCrLf & _
"Seu nome"

    On Error Resume Next
    Set Mail_Object = CreateObject("Outlook.Application")
    Set Mail_Single = Mail_Object.CreateItem(0)
    With Mail_Single
    .Subject = Email_Subject
    .To = Email_Send_To
    .Body = Email_Body
    .Attachments.Add Anexo
    .Display
    '.Send
    End With

'Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%e"
'Application.Wait (Now + TimeValue("0:00:01"))
SendKeys "%{e}", True 'send the email without prompts

Next

Sheets("Consolidado").Select

MsgBox "E-mails enviados com sucesso!"

End Sub

With it is quite easy to insert your loop and put in the outlook field, you can create the ifs to define your sets.