Automatizar o envio de emails das suas listas com o Excel

Olá pessoas Excelentes!
Tenho recebido muitos comentários, pedidos e dúvidas acerca do tema “enviar emails pelo Excel“, por isso, há tempos desejo escrever um tutorial bacana sobre um modo mais avançado e aprimorado de realizar esta tarefa diretamente de suas planilhas.
Já ensinei, aqui no site, esta outra maneira de enviar emails pelo Excel, contudo, apesar de bem funcional, este método é bem limitado e dependente de um processador de emails instalado na máquina. Resolve em muitos casos mais simples e que não exijam muitas alternativas, como por exemplo, escrever uma mensagem personalizada no corpo do email.
Na dica de hoje, vamos utilizar um processo de envio de emails baseado em configurações de SMTP, as mesmas que você utiliza em qualquer aplicativo de envio de emails, como o Outlook, por exemplo. Desse modo, não será necessário nenhum software externo para auxiliar na execução desta tarefa. Tudo será gerenciado pela própria macro do Excel.
Vamos começar, pois temos bastante coisa para fazer.

Iniciando o projeto

O processo de envio de email que iremos criar com nossa macro VBA é totalmente independente que qualquer programa de emails instalado na sua máquina, ou seja, não requer o Outlook configurado, Live Mail ou qualquer outro. Essa liberdade para manipular o tratamento das mensagens será possível com o auxílio da biblioteca Cdonts (cdosys.dll) que já está presente em seu computador.
Vamos referenciá-la dinamicamente em nosso projeto através das linhas de comando abaixo:

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

Para que possamos ter uma melhor compreensão e aproveitamento da nossa rotina de envio de emails, vamos organizar a macro em duas partes principais:

  1. Uma classe de configuração e envio da mensagem
  2. Um módulo onde criaremos o conteúdo da mensagem e os destinatários da mensagem

Isto é bom porque não precisamos ficar configurando o objeto CDO a cada método que criarmos. Enfim, um pouco do conceito de OOP (Object-oriented programming) que tentamos aplicar aqui no VBA.
 

Criando a classe de envio de emails

Abra o painel de edição de macros (ALT+F11) e insira um módulo de classe.

 
Altere o nome da Classe1 para clsEmail.

Em seguida, digite o código abaixo.

Option Explicit
Private iConf                   As Object
Private iMsg                    As Object
Private confEmailFromNome       As String
Private confEmailFrom           As String
Private confEmailSenha          As String
Private confEmailServidor       As String
Private confEmailPorta          As String
Private confEmailSSL            As Boolean
Private emailTo                 As String
Private emailToNome             As String
Private emailTitulo             As String
Private emailConteudo           As String
Private emailAnexo              As Variant
Public Property Let setConfEmailFromNome(value As String)
    confEmailFromNome = Trim(value)
End Property
Public Property Let setConfEmailFrom(value As String)
    confEmailFrom = Trim(value)
End Property
Public Property Let setConfEmailSenha(value As String)
    confEmailSenha = Trim(value)
End Property
Public Property Let setConfEmailServidor(value As String)
    confEmailServidor = Trim(value)
End Property
Public Property Let setConfEmailPorta(value As String)
    value = Trim(value)
    If Len(value) = 0 Then value = "25"
    confEmailPorta = value
End Property
Public Property Let setConfEmailSSL(value As Boolean)
    confEmailSSL = value
End Property
Public Property Let setEmailTitulo(value As String)
    emailTitulo = Trim(value)
End Property
Public Property Let setEmailConteudo(value As String)
    emailConteudo = Trim(value)
End Property
Public Property Let setEmailAnexo(value As Variant)
    emailAnexo = value
End Property
Public Property Let setEmailTo(value As String)
    emailTo = Trim(value)
End Property
Public Property Let setEmailToNome(value As String)
    emailToNome = Trim(value)
End Property
Public Property Get getEmailTo() As String
    getEmailTo = emailTo
End Property
Public Property Get getEmailToNome() As String
    getEmailToNome = emailToNome
End Property
Public Function Configurar() As Boolean
Dim Flds As Variant
On Error GoTo Err_Class
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    iConf.Load -1
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = confEmailServidor
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = confEmailPorta
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = confEmailFrom
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = confEmailSenha
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = confEmailSSL
        .Update
    End With
    With iMsg
        Set .Configuration = iConf
    End With
    Configurar = True
Err_Exit:
    Exit Function
Err_Class:
    Configurar = False
    MsgBox "Ocorreu um erro. [" & Err.Number & "]", vbExclamation
    GoTo Err_Exit
End Function
Public Function EnviarEmail() As Boolean
Dim strbody As String
Dim i As Integer
On Error GoTo Erro
    'adiciona quebras de linha
    strbody = Replace(emailConteudo, "<br>", "<br>" & vbCrLf)
    With iMsg
        .To = emailToNome & " <" & emailTo & ">"
        .CC = ""
        .BCC = ""
        .FROM = confEmailFromNome & " <" & confEmailFrom & ">"
        .Subject = emailTitulo
        .HTMLBody = emailConteudo
        'Anexa arquivos ao email
        .attachments.DeleteAll
        If IsArray(emailAnexo) Then     'Se mais de um arquivo para anexar
            For i = 1 To UBound(emailAnexo)
                .AddAttachment emailAnexo(i)    'anexa um por um dos arquivos
            Next i
        Else        'Se apenas um arquivo para anexar
            If Len(emailAnexo) > 0 Then
                .AddAttachment emailAnexo
            End If
        End If
        .Send       'Comando para enviar o email
    End With
    EnviarEmail = True
Exit Function
Erro:
    EnviarEmail = False
    MsgBox "Falha no envio do Email." & vbCrLf & Err.Description, vbExclamation
    Exit Function
End Function
Private Sub Class_Terminate()
On Error GoTo Err_Class
    Set iMsg = Nothing
    Set iConf = Nothing
Err_Exit:
    Exit Sub
Err_Class:
    MsgBox "Ocorreu um erro. [" & Err.Number & "]", vbExclamation
    GoTo Err_Exit
End Sub

Essa classe será responsável pelas configurações do servidor SMTP,  login e senha do email do remetente. E também fará o envio da mensagem, ou seja, toda a rotina que é executada ao clicar no botão enviar, por exemplo, é processada nela, de acordo com o conteúdo e destinatário do email passado para a classe através dos parâmetros solicitados.
Vejamos a seguir como criar nossa rotina de envio de emails e fazer a chamada a classe clsEmail.
 

Criando a rotina para enviar emails

Agora que já temos a classe que fará todo o trabalho para tratar o envio de mensagens, podemos utilizá-la de acordo com nossas necessidades que podem ser as mais diversas, como enviar um relatório de vendas para cada vendedor de uma lista em sua planilha, ou para os gerentes de cada departamento com uma frequência previamente determinada. Isso vai depender, como disse, da necessidade do seu projeto. Você é quem vai determinar e desenvolver a rotina que mais se aplicará ao teu caso.
Neste tutorial, vou ensinar uma maneira básica de enviar um email utilizando nossa macro. A rotina a seguir mostra como enviar uma mensagem pré-configurada, no formato HTML, para o endereço de email informado na célula C8 da planilha Plan1.
Adicione um Módulo ao projeto e digite o código abaixo.

Sub EnviarEmail()
Dim objEmail As clsEmail
Dim sh As Worksheet
On Error GoTo Erro_Sub
    Set objEmail = New clsEmail     'Inicializa a classe clsEmail
    Set sh = Sheets("Plan1")        'Define a planilha
    With objEmail
        .setConfEmailServidor = "smtp.seuservidor.com.br"       'Servidor de saída de emails. Ex: smtp.uol.com.br
        .setConfEmailPorta = "25"                               'Porta. Padrão é a porta 25
        .setConfEmailSSL = false                                'Se necessita conexão segura SSL
        .setConfEmailFrom = "seu-email@seuservidor.com.br"      'Seu email: O remetente do email. Ex: seunome@uol.com.br
        .setConfEmailSenha = "sua-senha"                        'Sua senha: A senha que você usa para acessar seus emails
        .setConfEmailFromNome = "Seu Nome"                      'Seu nome: O nome que será exibido no campo De:
        .Configurar     'Executa a configuração
        .setEmailTo = sh.Range("C8")                            'Email do Destinatário
        .setEmailToNome = sh.Range("C6")                        'Nome do Destinatário
        .setEmailTitulo = "Aprendendo a enviar emails diretamente do Excel"     'Título da mensagem
        'Aqui, você deve digitar o conteúdo. Pode utilizar formatação HTML.
        .setEmailConteudo = "Olá, <strong>" & .getEmailToNome & "</strong>.<br><br>Estou aprendendo muito aqui no site <a href=""https://www.exceldoseujeito.com.br"">Excel do Seu Jeito</a>." _
                            & "<br><br>Acesse <a href=""https://www.exceldoseujeito.com.br"">https://www.exceldoseujeito.com.br</a> e fique expert no Excel."
        .EnviarEmail
    End With
    Set objEmail = Nothing
    Set sh = Nothing
    MsgBox "Email enviado com sucesso!", vbInformation
Exit Sub
Erro_Sub:
    MsgBox Err.Description, vbExclamation
    Exit Sub
End Sub

Após criar a rotina acima (que pode ser utilizada teclando ALT+F8), podemos definir que a sua utilização seja iniciada com um clique num botão Enviar na própria planilha. Para isso, crie uma planilha semelhante a mostrada na figura a seguir e atribua a macro EnviarEmail ao botão “Enviar“, clicando com o botão direito do mouse no retângulo inserido e selecionando a opção “Atribuir macro“.


 
Feito isso, digite o nome e o email na planilha e clique no botão enviar. Pronto! Seu email foi enviado com sucesso.
 
Bom pessoal, vou terminar essa primeira parte por aqui. Amanhã postarei a continuação, onde falarei como anexar arquivos e enviar vários emails de uma lista.
Deixem seus comentários sobre este artigo e compartilhem com seus amigos do twitter e do facebook, ok.
E, pra quem ainda não adquiriu meu ebook Aprenda as 7 Fórmulas que podem salvar a sua vida, clique aqui para ver mais detalhes e adquirir o seu ebook agora. Nele você vai encontrar exemplos e ensinamentos sobre as fórmulas mais usadas em qualquer planilha do Excel e vai poder tirar onda com seus colegas de trabalho, apresentando sempre planilhas perfeitamente profissionais, de dar inveja a quem utilizá-las.
 
Até a próxima pessoal.

13 comentários em “Automatizar o envio de emails das suas listas com o Excel”

  1. Mano, muito bom seu site!
    Gosto muito dele e acesso sempre. Ja utilizei de varias dicas suas para a contrução de planilhas que uso em casa e no trabalho. É tudo mostrado de forma simples e objetiva; ficando claro até para quem não tem um conhecimento muito apurado na area.
    Parabéns!!!! =D

    Responder
  2. Reinaldo, muito com o conteudo do site.
    Estava procurando uma maneira de enviar e-mail pelo excel via smtp e com seu artigo acho quer vou conseguir resolver o problema, porem baixei o arquivo exemplo mas não consegui fazer funcionar… quando clico em ENVIAR aparece a seguinte mensagem: “FALHA NO ENVIO DO EMAIL. THE TRANSPORT FAILED TO CONNECT TO THE SERVE.”
    Estou tentando o envio pelo hotmail com porta 25 e também com porta 587 + SSL e sempre da a mesma mensagem…. existe alguma dica adicional?

    Responder
      • Reinaldo,
        Tentei com hotmail…
        .setConfEmailServidor = “smtp.gmail.com”
        .setConfEmailPorta = “25”
        .setConfEmailFrom = “[editado pelo moderador]”
        .setConfEmailSenha = “******”
        … e com hotmail…
        .setConfEmailServidor = “smtp.live.com”
        .setConfEmailPorta = “25”
        .setConfEmailFrom = “[editado pelo moderador]”
        .setConfEmailSenha = “******”
        … e no modulo clsEmail incluir informação sobre o uso de SSL…
        With Flds
        .Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
        .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = confEmailServidor
        .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = confEmailPorta
        .Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
        .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = confEmailFrom
        .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = confEmailSenha
        ‘.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
        ‘.Item(“http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout”) = 60
        .Update
        End With
        … no mais, está tudo “original” na planilha exemplo.

        Responder
        • Mas agora você conseguiu?
          Eu testei novamente aqui e funcionou bem.
          Não sei o que pode ser. Vou arriscar mais uma tentativa: certifique-se que o proxy/firewall permite esta operação.
          Algumas empresas tem algumas restrições de proxy.
          Abç

          Responder
  3. Olá Reinaldo,
    Seria possível incrementar esta macro, sendo que, ela buscaria determinado e-mail salvo em rascunhos, no Outlook 2010, e fizesse o envio automático para todos os e-mails da planilha?
    Abs,
    Beto.

    Responder
  4. Olá Reinaldo. Tudo bem?
    Você quase salvou minha vida kkkkkkkk. Tenho uma loja de carros e vou fazer um feirão em outra cidade e minha secretaria vai ficar na sede para poder organizar as papeladas para os financiamentos. Criei uma planilha para que meus vendedores preencham com os cadastro e enviem para ela e estava procurando uma forma de automatizar isso para evitar complicações pois a maioria dos vendedores não sabe nada de informática. Então achei essa “quase solução”. Na minha pasta tenho duas planilhas uma bem boba para eles preencherem e outra que leva as informações digitadas por eles para possível envio para secretaria e consegui enviar a mensagem para o email através do teu artigo. Mas o que eu queria enviar seriam os dados que estão nessa planilha que chamei de dados para impressão. É possível enviar esses dados pelo email??
    Antecipadamente, muito obrigado.

    Responder
    • Olá Rodrigo,
      É possível sim. E de diversas maneiras…
      Uma solução poderia ser a criação de uma rotina para pegar as informações nas células específicas da planilha de dados e atribuí-las numa variável. Então seria utilizada a propriedade .setEmailConteudo para recuperar o valor da variável de conteúdo.
      Outra idéia seria mesclar a macro que ensinei neste artigo com esta outra aqui, onde ensino a salvar uma planilha apenas. Porque dessa forma você poderia enviar apenas a planilha de dados por email como anexo.
      Enfim, se ainda estiver precisando desta macro, envia sua planilha aqui pra eu dar uma verificada.
      Um abraço

      Responder
  5. Boa noite
    Estou precisando ai uma ajuda do PPL, eu tenho uma planilha onde cadastro data de vencimento de documentos de funcionarios.
    Existem 4 documentos com as respectivas datas de validade para cada funcionario.tenho uma coluna que define quais os funcionarios que teem documentos caducados, sempre que essa coluna tenha o estado caducado pretendia enviar um email para esse funcionario(o email esta numa outra coluna da mesma folha) com o cabeçalho da tabela(nome do documento) e as datas de validade de cada documento.

    Responder
    • Você precisa definir em que momento o Excel faria essa verificação: se na abertura do arquivo, se na ativação da planilha, se ao clicar num botão… Enfim, defina como ativar a macro de automatização de envio.
      A macro de automatização de envio deverá percorrer toda a lista (pode fazer isso usando o laço For…Next) e verificar se a célula correspondente a informação da validade, em cada linha, tem o status caducado. Se a condição for satisfeita, a macro chama a rotina de envio.
      Abç

      Responder

Deixe uma resposta para Authentic Louis Vuitton Handbags edI6N Cancelar resposta