problem with attaching files to email VBA excel

[ben_sorensen]

Hello

I am trying to send an email via excel vba. Everything works fine but the attachments won't attach for some reason.

I am sending htis email out everyday so the file will have a name that varies according to the date so I am referencing cells within the sheet that the macro looks up but the files won't attach. I have attached my code:

.To = "XXX@XXX.com;"
.CC = ""
.Subject = ws("Single").Range("a4").Value
.Body = "Hello" & vbNewLine & vbNewLine & _
""
.Attachments.Add Sheets("Single").Cells(2, 3).Value
.Attachments.Add Sheets("Sinlge").Cells(2, 4).Value
.Attachments.Add Sheets("Single").Cells(2, 5).Value
.Attachments.Add Sheets("Single").Cells(2, 6).Value
.Attachments.Add Sheets("Single").Cells(2, 7).Value
.Attachments.Add Sheets("Single").Cells(2, 8).Value
.Attachments.Add Sheets("Sinlge").Cells(2, 9).Value
.Attachments.Add Sheets("Single").Cells(2, 10).Value
.Attachments.Add Sheets("Single").Cells(2, 11).Value
.Attachments.Add Sheets("Single").Cells(2, 12).Value
.Attachments.Add Sheets("Single").Cells(2, 13).Value
.Attachments.Add Sheets("Sinlge").Cells(2, 14).Value
.Attachments.Add Sheets("Single").Cells(2, 15).Value
.Attachments.Add Sheets("Single").Cells(2, 16).Value
.Attachments.Add Sheets("Single").Cells(2, 17).Value
.Attachments.Add Sheets("Single").Cells(2, 18).Value
.Attachments.Add Sheets("Single").Cells(2, 19).Value
.Attachments.Add Sheets("Sinlge").Cells(2, 20).Value
.Attachments.Add Sheets("Single").Cells(2, 21).Value
.Attachments.Add Sheets("Single").Cells(2, 22).Value
.Attachments.Add Sheets("Single").Cells(2, 23).Value



.Display
End With


End Sub

Any help would be appreciated.

Best Regards

Ben

 

[Mister H]

Hi. I think you need to use a string for the body. Something like this...

Sub Email_With_Sheet_Data_In_Body()
 Dim OutApp As Object
 Dim OutMail As Object
 Dim strbody As String
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)
 On Error Resume Next
 
 
 strbody = "This is a Test" & vbNewLine & Sheets("Single").Cells(2, 3).Value & vbNewLine & Sheets("Single").Cells(2, 4).Value & vbNewLine & Sheets("Single").Cells(2, 5).Value & vbNewLine & Sheets("Single").Cells(2, 6).Value
 
 
 
 With OutMail
 .To = "[EMAIL="Test@test.com"]Test@test.com[/EMAIL]"
 .cc = ""
 .BCC = ""
 .Subject = "NEW TEST"
 
 
 .Body = strbody
 
 
 
.Display
' .Send
 End With
 On Error GoTo 0
 Set OutMail = Nothing
 Set OutApp = Nothing
 End Sub

Hopefully that will get you going in the right direction

Good Luck,
Mark