Attaching a Spreadsheet

[seasmith]

I have code (see below) to send an email automatically if a button is pressed at it has been 30 days since the last time is was pressed. I am trying to attach the spreadsheet to the email but I am having no luck.

This is the code I'm using:

Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
x = Date

If x - Cells(3, 4) > 30 Then

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "A relief valve needs to be tested. Please look at the attached spreadsheet to see which pressure relief it is."
On Error Resume Next
With OutMail
.To = email@email.com
.CC = ""
.BCC = ""
.Subject = "Pressure Relief Valve"
.Body = strbody
.Send 'Sends Email"
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

Cells(3, 4) = x

End If
End Sub

I've tried using .Attachments.Add but no luck

 

[daverunt]

Hi,

You don't show the attachment code you tried.

If it's not attaching using Attachments.Add it's probably because you haven't specified the path.

.FullName includes the path to the file.

.Attachments.Add ThisWorkbook.FullName
 
or 
 
.Attachments.Add "C\junk\junk.xls"
 
or
 
AttachFileName = Application.GetOpenFilename("Files (*.**)," & _
"*.**", 1, "Select File", "Open", False)
.Attachments.Add (AttachFileName)

 

[seasmith]

Thanks so much the .Attachments.Add ThisWorkbook.FullName worked great!
I just couldn't figure out what to put after the .Attachments.Add in the code. Thanks again.

 

[VBAX]

I have tried all of these three options, but none of them work for me.

 

[VishMishra]

Hi,
Try the below code. .Attachment.Add does not work. You need to use the syntax as .AddAttachment "File Name"

With OutMail
.Subject = "Test Mail from LearnExcelMacro.com"
.From = "vishwamitra01@yahoo.com"
.To = "vishwamitra02@gmail.com;info@learnexcelmacro.com"
.CC = "vishwamitra01@gmail.com"
.BCC = ""
.HTMLBody = "Write your complete HTML Page"

' For multiple Attachment you can add below lines as many times

.AddAttachment "C:\ExcelMacro-help.xls"
.AddAttachment "C:\ExcelMacro-help2.xls"
End With


---

To know more in detail.. you can visit this link:

http://www.learnexcelmacro.com/2011/12/how-to-send-an-email-using-excel-macro-from-gmail-or-yahoo


http://www.learnexcelmacro.com/2011/12/how-to-send-email-by-excel-macro-from-outlook/