Como enviar e-mails de uma planilha do Excel usando scripts VBA
Nosso modelo de código ajudará você a configurar e-mails automatizados no Excel usando Collaboration Data Objects (CDO) e scripts VBA.
Enviar e-mails do Microsoft Excel requer apenas alguns scripts simples. Adicione essa funcionalidade às suas planilhas e você poderá realizar muito mais no Excel.
As macros do Excel podem fazer muitas das mesmas coisas que os scripts VBA podem, sem a necessidade de tanto conhecimento de programação. O VBA permite implementar rotinas mais avançadas, como criar um relatório em planilha com todas as informações do seu PC.
Prefere assistir a este tutorial em vídeo? Nós ajudamos você!
Por que enviar e-mail do Excel?
Existem vários motivos pelos quais você pode querer enviar um e-mail de dentro do Microsoft Excel.
Talvez você tenha funcionários que atualizam documentos ou planilhas toda semana e gostaria de receber uma notificação por e-mail quando isso acontecer. Ou você pode ter uma planilha de contatos e desejar enviar um e-mail para todos eles de uma vez.
Você provavelmente está pensando que criar scripts para uma transmissão de e-mail do Excel será complicado. Esse não é o caso. A técnica neste artigo utiliza um recurso que está disponível no Excel VBA há muito tempo, Collaboration Data Objects (CDO).
CDO é um componente de mensagens usado no Windows desde as primeiras gerações do sistema operacional. Costumava ser chamado de CDONTS e, com o advento do Windows 2000 e XP, foi substituído por "CDO para Windows 2000". Este componente já está incluído na instalação do VBA no Microsoft Word ou Excel e está pronto para uso.
O uso do componente torna o envio de e-mails de produtos Windows com VBA extremamente fácil. Neste exemplo, você usará o componente CDO no Excel para enviar um email que entregará os resultados de uma célula específica do Excel.
Etapa 1: prepare sua conta do Gmail
Para enviar e-mail do Microsoft Excel, usaremos o Gmail, embora você possa personalizar a macro abaixo para funcionar com outros clientes de e-mail. Observe que o Gmail não permite mais acesso a aplicativos de terceiros, o que significa que primeiro você terá que ativar a autenticação em duas etapas do Gmail.
Na página Segurança da sua conta do Google, em Como fazer login no Google, clique em Senhas de aplicativos. Na tela Senhas de aplicativos, encontre o menu suspenso Selecionar aplicativo e selecione Mail. Em Selecionar dispositivo, selecione Computador Windows. Em seguida, clique em GERAR.
Anote a senha do aplicativo de 16 caracteres; você precisará dele ao configurar a macro.
Se você não vir a opção de senhas de aplicativos, embora tenha configurado a verificação em duas etapas para sua conta, provavelmente é porque um terceiro, como sua escola ou trabalho, gerencia sua conta ou você se inscreveu no programa Proteção Avançada do Google .
Etapa 2: crie uma macro VBA
Dica: Antes de começar, salve o arquivo Excel com o qual trabalhará como uma pasta de trabalho habilitada para macro, ou seja, no formato XLSM.
Primeiro, precisaremos da guia Excel Developer. Se você não o vir, veja como ativá-lo:
- Vá para Arquivo > Opções.
- Em Personalizar a faixa de opções > Guias principais, marque a opção Desenvolvedor.
- Clique em OK para salvar suas alterações.
Na guia Desenvolvedor do Excel, clique em Inserir na caixa Controles e selecione um botão de comando.
Desenhe-o na planilha e crie uma nova macro para ele clicando em Macros na faixa do desenvolvedor.
Ao clicar no botão Criar, o editor VBA será aberto.
Adicione a referência à biblioteca CDO navegando até Ferramentas > Referências no editor.
Role a lista até encontrar Microsoft CDO para Windows 2000 Library. Marque a caixa de seleção e clique em OK.
Ao clicar em OK, anote o nome da função onde você está colando o script. Você precisará disso mais tarde.
Etapa 3: configure sua macro
Agora você está pronto para criar os objetos de email e configurar todos os campos necessários para enviar um email. Tenha em mente que embora muitos campos sejam opcionais, os campos De e Para são obrigatórios. Cole todos os trechos de código abaixo na janela Módulo1 (Código).
Esta é a aparência do código completo:
Sub Send_Emails()
Dim NewMail As CDO.Message
Dim mailConfig As CDO.Configuration
Dim fields As Variant
Dim msConfigURL As String
On Error GoTo Err:
'early binding
Set NewMail = New CDO.Message
Set mailConfig = New CDO.Configuration
'load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
'Set All Email Properties
With NewMail
.From = "username@gmail.com"
.To = "username@gmail.com"
.CC = ""
.BCC = ""
.Subject = "Send Email From an Excel Spreadsheet"
.TextBody = "This is the body of your email. And here is some added data:" & Str(Sheet1.Cells(2, 1))
.Addattachment "c:\data\email.xlsx" 'Optional file attachment; remove if not needed.
.Addattachment "c:\data\email.pdf" 'Duplicate the line for a second attachment.
End With
msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
With fields
.Item(msConfigURL & "/smtpusessl") = True 'Enable SSL Authentication
.Item(msConfigURL & "/smtpauthenticate") = 1 'SMTP authentication Enabled
.Item(msConfigURL & "/smtpserver") = "smtp.gmail.com" 'Set the SMTP server details
.Item(msConfigURL & "/smtpserverport") = 465 'Set the SMTP port Details
.Item(msConfigURL & "/sendusing") = 2 'Send using default setting
.Item(msConfigURL & "/sendusername") = "username@gmail.com" 'Your gmail address
.Item(msConfigURL & "/sendpassword") = "password" 'Your password or App Password
.Update 'Update the configuration fields
End With
NewMail.Configuration = mailConfig
NewMail.Send
MsgBox "Your email has been sent", vbInformation
Exit_Err:
'Release object memory
Set NewMail = Nothing
Set mailConfig = Nothing
End
Err:
Select Case Err.Number
Case -2147220973 'Could be because of Internet Connection
MsgBox "Check your internet connection." & vbNewLine & Err.Number & ": " & Err.Description
Case -2147220975 'Incorrect credentials User ID or password
MsgBox "Check your login credentials and try again." & vbNewLine & Err.Number & ": " & Err.Description
Case Else 'Report other errors
MsgBox "Error encountered while sending email." & vbNewLine & Err.Number & ": " & Err.Description
End Select
Resume Exit_Err
End SubE estas são as seções e campos que você precisa personalizar:
- Com NewMail: Esta seção contém todos os parâmetros para envio do seu e-mail, incluindo o corpo do seu e-mail. O campo .From precisa conter seu endereço do Gmail, mas você pode definir os outros campos como desejar. Para o corpo, você pode reunir os componentes da mensagem usando a string & para inserir dados de qualquer planilha do Microsoft Excel diretamente na mensagem de e-mail, como mostrado acima. Você também pode anexar um ou mais arquivos.
- Com campos: é aqui que você define as configurações de SMTP da sua conta do Gmail. Deixe os campos smtpserver e smtpserverport como estão ao copiar o código. Digite seu nome de usuário do Gmail e a senha do aplicativo de 16 dígitos nos respectivos campos.
Etapa 4: teste sua macro
No editor VBA, vá em Executar > Executar formulário de sub/usuário ou pressione F5 para testar a macro. Se o seu e-mail não for enviado, você verá uma mensagem de erro. Caso contrário, você verá uma confirmação de que seu e-mail foi enviado com sucesso.
Se você receber um erro que diz O transporte falhou ao se conectar ao servidor, certifique-se de ter inserido o nome de usuário, a senha, o servidor SMTP e o número da porta corretos nas linhas de código listadas abaixo de Com campos.
Etapa 5: conecte o botão de comando ao seu script
Para conectar seu botão de comando a este script, vá até o editor de código e clique duas vezes em Planilha1 para visualizar o código VBA dessa planilha. Selecione seu botão, por ex. CommandButton1, no menu suspenso à esquerda e defina a ação à direita; Clique funciona. Em seguida digite o nome da função onde você colou o script acima; em nosso exemplo é Send_Emails.
Ao voltar para sua planilha agora, clique no botão para enviar o e-mail.
Aqui está um exemplo de como a mensagem deve ficar na sua caixa de entrada:
Vá além e automatize todo o processo
É muito bom poder enviar e-mails do Excel com o toque de um botão. No entanto, você pode querer usar essa funcionalidade regularmente; nesse caso, faz sentido automatizar o processo. Para fazer isso, você precisará fazer uma alteração na macro. Vá para o Editor do Visual Basic e copie e cole todo o código que você montou até agora.
Em seguida, clique duas vezes em ThisWorkbook na hierarquia VBAProject.
Nos dois campos suspensos na parte superior da janela de código, selecione Workbook e selecione Abrir no menu suspenso Métodos.
Cole o script de e-mail acima em Sub Workbook_Open().
Isso executará a macro sempre que você abrir o arquivo Excel.
Em seguida, abra o Agendador de Tarefas. Você usará esta ferramenta para solicitar ao Windows que abra a planilha automaticamente em intervalos regulares, momento em que sua macro será executada, enviando o e-mail.
No menu Ação, selecione Criar tarefa básica... e prossiga pelo assistente até chegar à tela Ação.
Selecione Iniciar um programa e clique em Avançar. Use o botão Procurar para encontrar a localização do Microsoft Excel em seu computador ou copie e cole o caminho no campo Programa/script. Em seguida, insira o caminho do seu documento Microsoft Excel no campo Adicionar argumentos. Conclua o assistente e seu agendamento estará em vigor.
Vale a pena fazer um teste agendando a ação para alguns minutos no futuro e, em seguida, alterar a tarefa assim que confirmar que está funcionando.
Observação: talvez seja necessário ajustar as configurações da Central de confiabilidade para garantir que a macro seja executada corretamente.
Para fazer isso, abra a planilha e navegue até Arquivo > Opções > Central de confiabilidade. A partir daqui, clique em Configurações da Central de Confiabilidade... e, na próxima tela, ajuste o botão do rádio para Nunca mostrar informações sobre conteúdo bloqueado.
Faça o Microsoft Excel trabalhar para você
O Microsoft Excel é uma ferramenta incrivelmente poderosa, mas aprender como aproveitá-la ao máximo pode ser um pouco intimidante. Se você deseja realmente dominar o software, precisará estar confortável com o VBA, e isso não é uma tarefa fácil.
No entanto, os resultados falam por si. Com um pouco de experiência em VBA, em breve você poderá fazer com que o Microsoft Excel execute tarefas básicas automaticamente, dando-lhe mais tempo para se concentrar em assuntos mais urgentes.