Code
Sub Envia_Email()
'Seleciona o intervalo de células na planilha ativa.
Application.DisplayAlerts = False 'desabilite o alerta
Sheets("Base filtrada").Select
Dim email_envio As Variant
email_envio = Range("AP2") 'e-mail para qual será enviado
descricao = Range("AQ2")
Set rngInicial = Range("R1")
rngInicial.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
'https://stackoverflow.com/a/48496434/7690982
Dim rng As Range, cell As Range, HtmlContent As String, i As Long, j As Long
Set rng = Selection
'Debug.Print rng.Address 'Verifica os endereços da Seleção
HtmlContent = "<table>"
For i = rngInicial.Row To rngInicial.Row + rng.Rows.Count - 1
HtmlContent = HtmlContent & "<tr>"
For j = rngInicial.Column To rngInicial.Column + rng.Columns.Count - 1
HtmlContent = HtmlContent & "<td>" & Cells(i, j).Value & "</td>"
Next
HtmlContent = HtmlContent & "</tr>"
Next
HtmlContent = HtmlContent & "</table>"
'https://stackoverflow.com/a/15161351/7690982
Dim OApp As Object, OMail As Object
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
With OMail
.Display
End With
signature = OMail.HTMLbody
With OMail
Introducao = "Prezado, bom dia!." & vbCr & "Seguem os extratos atualizados nas campanhas:" 'Texto Intro Corpo do e-mail
.To = email_envio
.Cc = "[email protected]" 'Quem será copiado
.Subject = "Extrato " & descricao 'Assunto do e-mail
'.Attachments.Add
.HTMLbody = Introducao & vbNewLine & HtmlContent & vbNewLine & signature
.Send
End With
Set OMail = Nothing
Set OApp = Nothing
Application.DisplayAlerts = True 'habilite o alerta
End Sub
Note: It is not recommended to use .Select
and Selection
, there are other ways to do this. See: How to avoid using Select in Excel VBA
Explanation
Outlook
Instead of:
' Mostrar o envelope na ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
An Outlook object with Late Binding is created:
'https://stackoverflow.com/a/15161351/7690982
Dim OApp As Object, OMail As Object
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
With OMail
.Display
End With
Signature
Then the default HTML Subscription is used: signature = OMail.HTMLbody
Create the email and send
Then the email is created and sent:
With OMail
Introducao = "Prezado, bom dia!." & vbCr & "Seguem os extratos atualizados nas campanhas:" 'Texto Intro Corpo do e-mail
.To = email_envio
.Cc = "[email protected]" 'Quem será copiado
.Subject = "Extrato " & descricao 'Assunto do e-mail
'.Attachments.Add 'Para inserir Anexos
.HTMLbody = Introducao & vbNewLine & HtmlContent & vbNewLine & signature
.Send
End With
Create table
The table with content is added with the following code:
'https://stackoverflow.com/a/48496434/7690982
Dim rng As Range, cell As Range, HtmlContent As String, i As Long, j As Long
Set rng = Selection
'Debug.Print rng.Address 'Verifica os endereços da Seleção
HtmlContent = "<table>"
For i = rngInicial.Row To rngInicial.Row + rng.Rows.Count - 1
HtmlContent = HtmlContent & "<tr>"
For j = rngInicial.Column To rngInicial.Column + rng.Columns.Count - 1
HtmlContent = HtmlContent & "<td>" & Cells(i, j).Value & "</td>"
Next
HtmlContent = HtmlContent & "</tr>"
Next
HtmlContent = HtmlContent & "</table>"
In which tab tags are added for each item in the Excel range that contains the table data.
EDIT to keep formatting:
To maintain formatting, the Bruin Ron's RangetoHTML can be used: / p>
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With
'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")
'Close TempWB
TempWB.Close savechanges:=False
'Delete the htm file we used in this function
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
Code
So instead of creating the table, the function can be called with:
HtmlContent = RangetoHTML(Selection)
And the HTML body can be built with:
.HTMLbody = Introducao & vbNewLine & HtmlContent & vbNewLine & signature