Spreadsheet doesn't attach to e-mail when sent

[Godot60]

Hi all,

I have a button on my spreadsheet that is supposed to allow the user to send it as an attachment to a specified address once the user has filled out some required fields.

Tee problem is that the e-mail message is generated and sent successfully, but the spreadsheet is not attached. See VBA below. Any thoughts on the source of the problem? Thanks in advance.



Sub Mail_small_Text_Outlook()


If Range("A4").Value = "" Or Range("PosSum").Value = "" Or Range("b6").Value = "" Or Range("a13").Value = "" Or Range("a19").Value = "" Or Range("a25").Value = "" Or Range("a31").Value = "" Or Range("a37").Value = "" Or Range("a45").Value = "" Or Range("a51").Value = "" Or Range("a57").Value = "" Or Range("b146").Value = "" Or Range("d146").Value = "" Or Range("d145").Value = "" Or Range("b145").Value = "" Then
MsgBox "Please complete all required fields"
Exit Sub
End If


Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Title: " & Sheet1.Range("A4") & vbNewLine & _
"Department: " & Sheet1.Range("C4") & vbNewLine & _
"SBU: " & Sheet1.Range("B5") & vbNewLine & _
"Level: " & Sheet1.Range("B6") & vbNewLine & _
"Manager: " & Sheet1.Range("B144")


On Error Resume Next
With OutMail
.to = "john.smith@any.edu"
.cc = "Douglas.Jones@any.edu"
.BCC = ""
.Subject = "Successful Submission: " & Sheet1.Range("a4")
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[bugmonsta]

Hello,
The source of your problem is that you are NOT attaching a spreadsheet

You are only copying cell data into the header and subject with the code you have pasted.

As a simple test - change .Send to .Display and it will show you the email with no attachments.

Have a look at Rons pages for sending email (its the only one you will need to read) - I'm assuming you are Windows and Outlook as default - Mail from Excel with Outlook (VBA)