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 with a MsgBox 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:
Hi Robert,
You can replace the line
.HTMLBody = RangetoHTML(Rng)
with:
If MsgBox("Send as attachment?", vbYesNo, "Sending Data") = vbYes Then
.Attachments.Add RangeToNewBook(Rng)
Else
.HTMLBody = RangetoHTML(Rng)
End If
And here is the function that creates the new book:
Function RangeToNewBook(ByVal Rng As Range) As String
Dim Wb As Workbook
Set Wb = Workbooks.Add
Rng.Copy Destination:=Wb.Worksheets(1).Cells(1)
Wb.SaveAs ThisWorkbook.Path & Application.PathSeparator & "Temp " & Format(Now(), "yyyymmddhhmmss") & ".xlsx"
RangeToNewBook = Wb.FullName
Wb.Close True
End Function
