Hi
I have a tool in which the user clicks a macro button and it send out the range of the sheet in the body of an email in Outlook.
We are having an issue that sometimes the range can be over 1000 rows long. The receiver will have to copy paste it in a workbook anyways, so I wanted to give the sender the option to select if they want the range sent as an attachment or in the body of the Outlook email
below is the code that I have:
I have a tool in which the user clicks a macro button and it send out the range of the sheet in the body of an email in Outlook.
We are having an issue that sometimes the range can be over 1000 rows long. The receiver will have to copy paste it in a workbook anyways, so I wanted to give the sender the option to select if they want the range sent as an attachment or in the body of the Outlook email
below is the code that I have:
VBA Code:
Sub Main()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim lastRow As Long
Set rng = Nothing
On Error Resume Next
lastRow = Sheets("Sheet1").Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row
Set rng = Sheets("Sheet1").Range("A1:F" & lastRow).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng 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
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Sheets("Contacts").Range("F4").Value
.CC = "isaac.santos@expeditors.com,sju-ivm@expeditors.com"
.BCC = ""
.Subject = "Expeditors Statement"
.HTMLBody = RangetoHTML(rng)
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub