1

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
Rohit Gupta
  • 3,499
tammy
  • 13

1 Answers1

0

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.

spikey_richie
  • 8,951
  • perfect. It works. Thanks a lot Rohit for your valuable time & quick help. You're SUPER GENIUS !! – tammy Mar 31 '23 at 16:42
  • Thank you so much spikey_richie for your valuable time & quick help. You're SUPER GENIUS !! Thanks millions – tammy Mar 31 '23 at 16:44
  • Please tick the green tick under this answer, to mark it as the correct answer. – spikey_richie Mar 31 '23 at 16:48
  • I am new. are we allowed to give the super user who helps with the problems scores/rating? if so, how? thanks a lot – tammy Mar 31 '23 at 17:16
  • Sort of. Users get reputation for providing an answer marked as correct by the person asking the question, and we also get reputation for having answers up-voted. – spikey_richie Mar 31 '23 at 19:02