VBA - Excel >> Outlook copy multiple ranges to email body

[smet1234]

Hi All,

I'm not good with VBA, but have made a macrothat will autosend an email from Excel via Outlook when a certain condition is met (i.e. cell x value = cell y value). The email body contains information from a range of cells using the "Select.Range()...." functionality. I would like the email body to contain information more than one range, located at different locations on the sheet. So, for instance, if I could have the first row of the email body contain Range("A1:F1"), and a second row of the email body contain Range("A7:D7"), that would be perfect.

Any help is greatly appreciated. Thank you!


-S

 

[Andrew Poulsom]

Welcome to MrExcel.

Please post your macro.

 

[smet1234]

Thanks for your reply. Bit scrappy but here it is:

Sub EmailInfo()

ActiveWorkbook.EnvelopeVisible = True


If Sheets("Volume").Range("C2").Value = Sheets("Volume").Range("E2").Value Then
ActiveSheet.Range("A2:F2").Select


With ActiveSheet.MailEnvelope
.Introduction = "Hi"
.Item.To = "XXXXX"
.Item.Subject = "Test"
.Item.Send
End With


Else: Exit Sub


End If


End Sub

Basically, the

ActiveSheet.Range("A2:F2").Select

is what I'm having trouble with. Somehow, I want to copy this and put it into a .Item.Body = ....... or something like that, so I can combine multiple ranges into different rows of the email body.

 

[Andrew Poulsom]

You could copy each range to a temporary worksheet so that they are contiguous and send that.

 

[excelwest]

You could copy each range to a temporary worksheet so that they are contiguous and send that.

Hi Andrew,

What code would I use to include multipe tab ranges into the email body?