Copy All Visible(Formatted Text) from Excel to Outlook using VBA?

1
votes

Copy All Visible(Formatted Text) from Excel to Outlook using VBA?

Please find below code to send email through outlook. However, I want to send the email with a selection that I have copied below using code. I do not wish to create a Table as HTML but instead just copy all visible?

Sub EmailRep()


Dim Mailbody As Range

Application.DisplayAlerts = False

Dim Outlook As Outlook.Application
Set Outlook = CreateObject("Outlook.Application")
Dim outmail As MailItem
Set outmail = Outlook.CreateItem(0)

Set Mailbody = ActiveWorkbook.Worksheets("Dashboard").Range("A1:F30")
Mailbody.Copy

With outmail

.To = "[email protected]"
.Subject = "All Open"
.Body = "This is Test Email"
.Display
.Send
End With


Set Outlook = Nothing
Set outmail = Nothing
Set Mailbody = Nothing


End Sub
1
excelvbaoutlook

1 Answers

0
votes

If I understand correct change your line of :

Set Mailbody = ActiveWorkbook.Worksheets("Dashboard").Range("A1:F30")

To 

Set Mailbody = ActiveWorkbook.Worksheets("Dashboard").Range("A1:F30").SpecialCells(xlCellTypeVisible)

Although in your code you are not putting the range into the body of the email. At first thought you pasted the range by hand but then I noticed you have .Send in code which would send the email before you got a chance to paste.

Either way the above will copy only the visible range.

If you are interested in a quick way to send your range in an email without the need to copy the below is pretty short and sweet: 

Sub EmailRep()
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

Range("A1:F30").SpecialCells(xlCellTypeVisible).Select

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
    .Introduction = "This is Test Email"
    .Item.To = "[email protected]"
    .Item.Subject = "All Open"
    .Item.Send
End With

ActiveWorkbook.EnvelopeVisible = False

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub