CODEWITHSUNDEEP
excelvbaemailsmtp
Dayley
Dayley

Reputation: 301

How can I add a Range of cells as the body of a custom SMTP email from Excel using Visual Basic?

On the click of a button in Excel, I am sending a range of cells on a sheet as the body of an email. The email is sending correctly, but I cannot work out how to add the actual range of cells as the body. Here is the code I am currently using in Excel's Visual Basic along with the Micrsoft CDO for Windows 2000 Library:

Sub Email_Figures_Click()
Dim myMail As CDO.Message

Set myMail = New CDO.Message

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "HIDDEN"
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "HIDDEN"
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "HIDDEN"
myMail.Configuration.Fields.Update

With myMail
.Subject = "HIDDEN"
.From = "HIDDEN"
.To = "HIDDEN"

    Dim myRng As Range
    Set myRng = Nothing

    'Only the visible cells in the selection
    Set myRng = Sheets("Monthly Figures").Range("A1:B29").SpecialCells(xlCellTypeVisible)

    If myRng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub

    End If

.HTMLBody = myRng
End With
On Error Resume Next
myMail.Send
MsgBox ("Email has been sent!")
Set myMail = Nothing
End Sub

Anything that has the word HIDDEN is to protect the client.

Any help is appreciated.

Upvotes: 0

Views: 658

Answers (1)

Franz
Franz

Reputation: 360

If I understand correctly, you cannot simply assign the range to .HTMLBody. You may have to construct the HTML-String "by hand" and have something similar to

html_text = "<table>" & vbCrLf & "<tr>"
For Each Row In myrng.Rows
    For Each cell In Row.Cells
        html_text = html_text & "<td>" & cell.Text & "</td>"
    Next cell
    html_text = html_text & "</tr>" & vbCrLf
Next Row
html_text = html_text & "</table>"
.HTMLBody=html_text

replace your line

.HTMLBody = myRng

.

Upvotes: 2

Related Questions