Hi all,

So I'm trying to send e-mails to multiple people. The details of each recipient are held on a single row - the e-mail address, their name and the range of cells that I want to paste. I've built a formula which should send an e-mail for all the rows on the spreadsheet.

My problem is that it only pastes blank cells into the body of the e-mail.

The msgbox flags I've put in imply that the variables are changing correctly via the loop as well. Does anybody have any idea why this might be happening or what I could do to fix it?


Sub Send_Report()

Dim MTo As String
Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim rngaddress As String
Dim count As Integer
Dim x As Integer

count = 2
x = 0

'count = Sheets("Merge").Range("d1").Value


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

MsgBox (count)

MTo = Sheets("Merge").Range("b6")

rngaddress = Worksheets("Merge").Range("C6").Value

MsgBox (rngaddress)

Set rng = Worksheets("Raw").Range(rngaddress)

On Error Resume Next
With OutMail

        .To = MTo
        .CC = ""
        .BCC = ""
        .Subject = "Lies"
        .HTMLBody = RangetoHTML(rng)
        
        
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Display
               
End With

While x < count

    x = x + 1

    MTo = Sheets("merge").Range("b2").Offset(x)

    rngaddress = Worksheets("Merge").Range("C2").Offset(x).Value
    
    rng = Worksheets("Raw").Range(rngaddress)
    
    MsgBox (MTo & x & rngaddress)
          
    With OutMail

        .To = MTo
        .CC = ""
        .BCC = ""
        .Subject = "Lies"
        .HTMLBody = RangetoHTML(rng)             
        .Display
        
    End With

Wend

Set OutMail = Nothing
Set OutApp = Nothing

End Sub