Compile Error

[CarlStephens]

Hello All,

I am getting a compile error on the .Rows. with "Invalid or Unqualified Reference" and I am not too sure what he fix is, as it looks ok to me but clearly it is not. The xMailBody code should go to cell C11 and anything typed in this cell and any other cells after C11 will be copied and pasted into the body of the email. Any help will be greatly appreciated. Thank you.

Sub Send_Single_Email()

Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = Join(Application.Transpose(.Range("C11", .Cells(.Rows.Count, "C").End(xlUp)).Value), vbCrLf)
                  On Error Resume Next
   
    With xOutMail
        .To = .Range("C2").Value
        .CC = .Range("C3").Value
        .BCC = .Range("C4").Value
        .Subject = .Range("C5").Value
        .Attachments.Add = .Range("C7").Value
        .Display
       
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

 

[RoryA]

.Range, .Cells and .Rows all require a worksheet object, which you have not provided. It looks like that section should be inside a With... End With block. You'll also need to rewrite the With xOutMail section, since the .Range calls in there are currently referring back to the xOutMail object, which doesn't make sense.

 

[RoryA]

Basically you need a worksheet object, so try something like this (adjust the sheet to whichever one it should be):

Sub Send_Single_Email()

Dim xOutApp As Object
Dim xOutMail As Object
    Dim xMailBody As String
Dim ws as worksheet
set ws = activesheet
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = Join(Application.Transpose(ws.Range("C11", ws.Cells(ws.Rows.Count, "C").End(xlUp)).Value), vbCrLf)
On Error Resume Next

With xOutMail
.To = ws.Range("C2").Value
.CC = ws.Range("C3").Value
.BCC = ws.Range("C4").Value
.Subject = ws.Range("C5").Value
.Attachments.Add = ws.Range("C7").Value
.Display

End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[CarlStephens]

Basically you need a worksheet object, so try something like this (adjust the sheet to whichever one it should be):

Sub Send_Single_Email()

Dim xOutApp As Object
Dim xOutMail As Object
    Dim xMailBody As String
Dim ws as worksheet
set ws = activesheet
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = Join(Application.Transpose(ws.Range("C11", ws.Cells(ws.Rows.Count, "C").End(xlUp)).Value), vbCrLf)
On Error Resume Next

With xOutMail
.To = ws.Range("C2").Value
.CC = ws.Range("C3").Value
.BCC = ws.Range("C4").Value
.Subject = ws.Range("C5").Value
.Attachments.Add = ws.Range("C7").Value
.Display

End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[CarlStephens]

Thanks Rory....thats a great help to a VBA newbie. The sheet is called "3. Email New Joiners", what would I need to rename?

 

[CarlStephens]

Hello Rory,

If I wanted to add the attachments to the bottom of the email body.....do you know what the code would be? Also, does the above code that you gave allow for a signature in the email body? Thank you for your time.

 

[RoryA]

Thanks Rory....thats a great help to a VBA newbie. The sheet is called "3. Email New Joiners", what would I need to rename?

Use:

set ws = sheets("3. Email New Joiners")

 

[RoryA]

If you want to use a signature, it's much simpler to use a template email with the signature already in place.

Attachment location is dependent on the email format and program, as far as I recall. Mine always seem to appear at the top of the email regardless in Outlook.