I have a compile error message byref argument type mismatch pointing to rg1 where the HTML is.
Could you please help?
Sub email_multi_ranges()
Dim OutApp As Object
Dim OutMail As Object
Dim rgl As Range, rg2 As Range, rg3 As Range, rg4 As Range
Dim str1 As String, str2 As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set rg1 = locate(1, 1, "Countries")
Set rg2 = locate(11, 1, "Countries")
Set rg3 = locate(3, 1, "Sheet2")
Set rg4 = locate(15, 2, "Sheet2")
str1 = "<BODY style=font-size:12ptsfont-family:calibri>" & _
"Hello Team, <br><br> Please see the figures below.<br>"
str2 = "<br>Best regards,<br>Tam"
On Error Resume Next
With OutMail
.to = "test.com"
.cc = "test1.com"
.BCC = ""
.Subject = "Country Info"
.Display
.HTMLBody = str1 & RangetoHTML(**rg1**) & RangetoHTML(rg2) & _
RangetoHTML(rg3) & RangetoHTML(rg4) & str2 & .HTMLBody
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Function locate(y1 As Long, x1 As Long, sh As String) As Range
Dim y2 As Long, x2 As Long
ThisWorkbook.Sheets(sh).Activate
y2 = WorksheetFunction.CountA(Range(Cells(y1, x1), Cells(y1, x1).End(xlDown))) + y1 - 1
x2 = WorksheetFunction.CountA(Range(Cells(y1, x1), Cells(y1, x1).End(xlToRight))) + x1 - 1
Set locate = Sheets(sh).Range(Cells(y1, x1), Cells(y2, x2))
End Function
As others have said, you declared the variables as Dim rgl As Range, rg2 As Range, rg3 As Range, rg4 As Range but then you reference Set rg1 = locate(1, 1, "Countries")
If you add Option Explicit to the top of the sub, it'll throw an error because you've not declared rg1.
Just change rgl to rg1, and it should work.
Best practice going forward may be to always use Option Explicit to force variable declaration.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments