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

Asked

Viewed 2,720 times

1

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.

1 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
  • 1

    Thank you so much for your help, it worked!!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.