VBA - How to send specific cells of a spreadsheet in the body of an email

Question

Hello, I’m trying to make an application for my work in VBA, however I’m having difficulty sending only some cells in the body of an email directly via outlook.

Private Sub CommandButton1_Click()
Dim v1 As String
Dim data As Date
Dim v2 As String
Dim outmail As Object
Dim texto As String
Dim outapp As Object
Dim intervalo As Range



v1 = TextBox1.Value
data = TextBox2.Value
v2 = TextBox3.Value

Sheets("Demandas").Select

Range("A2") = data
Range("B2") = v2
Range("C2") = v1

Sheets("Demandas").Select
Set intervalo = Range("A2:C2").Select


Set outapp = CreateObject("Outlook.Application")
Set outmail = outapp.createitem(0)


With outmail
    .display
    .to = "[email protected]"
    .cc = ""
    .bcc = ""
    .Subject = "Solicitação de nova demanda"
    .body = intervalo

End With


MsgBox ("Sua solicitação foi concluida")

UserForm1.Hide


End Sub

On the line:Set intervalo = Range("A2:C2").Select the application always gives an error saying that the object is mandatory. I tried to do as a string also with the text variable to attach the cells I want but gave error too.

I wonder if someone could clarify this for me, because I am in great need of this application.

Thank you.

Answer 1

Hello Ecodata I hope to help you, I made small adjustments and tested and worked!

Follow the code snippet you need to change.

Sheets("Demandas").Select
Set intervalo = Range("A2:C2").SpecialCells(xlCellTypeVisible)

Dim textoDaSelecao As String

For Each valor In intervalo
    textoDaSelecao = textoDaSelecao & " " & valor
Next

Set outapp = CreateObject("Outlook.Application")
Set outmail = outapp.createitem(0)

With outmail
    .display
    .to = "[email protected]"
    .cc = ""
    .bcc = ""
    .Subject = "Solicitação de nova demanda"
    .body = Mid(textoDaSelecao, 2)
End With