When a pass a range when calling a SubRoutine, the range get lost on the process.
I've got two modules:
- Final
On the Email Module i've got this sub:
Sub Send_Email(Type, MSG, operation, Optional RangeB As Range, Optional txtpath)
The parameter "Type" is either 1 or 2.
If i put 1 the sub will send an e-mail with a file.txt as an attachment. And if i put 2 the sub will put the range in the email body. That's why both RangeB and txtpath are optional parameters.
The attachment one works fine.
But when i put a range as parameter, RangeB cant be selected.
"RangeB.Select" -> ERROR (select method of range class failed)
I've tryed calling the sub by two different ways:
Call Send_Email(2, var_MSG, var_Op, Range("RangeBalko"))
Call Send_Email(2, var_MSG, var_Op, Templates.Range("RangeBalko"))
Templates -> ThisWorkbook.Sheets("Templates")
Someone knows how to resolve this issue ?
Best Regards!!
CodePudding user response:
Does this help? The single-cell range gets "MsgBox"ed and the multi-cell ranges get selected.
Sub test()
Cells(1, 1) = "Hello"
ActiveWorkbook.Names.Add "rng1", "=$C$3:$D$4"
Call SendEmail(Range("a1"), Range("a1:b2"), Range("rng1"))
End Sub
Sub SendEmail(Optional rangeA As Range, Optional rangeB As Range, Optional rangeC As Range)
MsgBox rangeA.Value
rangeB.Select ' this can be done without "selecting": rangeB.delete
Selection.Delete
rangeC.Select ' if you must select
End Sub