Email attachment code with email?

[KainAussie]

Hi,

i have a Excel document that i have created and i am trying to add a button so that after pressing the button asks for a email address and it sends the email with the subject "test email" using outlook could some one please help me out with this as i am not sure how to do it and my boss would like this done for his business so its easier to send out the quotes

 

[KainAussie]

Aargh !!! This code just isn't coming together....I've rewritten it, and hopefully it's a bit simpler to run AND follow.....sorry

Sub EmailWithOutlook()
Dim oApp As Object, oMail As Object, WB As Workbook, fileName As String
Application.ScreenUpdating = False
ActiveSheet.Copy
Set WB = ActiveWorkbook
fileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & fileName
On Error GoTo 0
WB.SaveAs fileName:="C:\" & fileName
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
        With oMail
             .To = "someone@somedomain.com" ' change to suit
             .CC = "change to suit"
             .BCC = "" ' change to suit
             .Subject = "Look at my workbook!" ' change to suit
             .Body = "" 'Insert body text if required
             .Attachments.Add WB.FullName
            .Display 'change to .Send if you don't want to chack the E-Mail first
        End With
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

do i just use a commandbutton1? because it starts with Sub EmailWithOutlook() if not what do i use

 

[Michael M]

If you have a command button, right click on it and select view code.
Paste this between the first and last line in the code window

Dim oApp As Object, oMail As Object, WB As Workbook, fileName As String
Application.ScreenUpdating = False
ActiveSheet.Copy
Set WB = ActiveWorkbook
fileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & fileName
On Error GoTo 0
WB.SaveAs fileName:="C:\" & fileName
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
        With oMail
             .To = "someone@somedomain.com" ' change to suit
             .CC = "change to suit"
             .BCC = "" ' change to suit
             .Subject = "Look at my workbook!" ' change to suit
             .Body = "" 'Insert body text if required
             .Attachments.Add WB.FullName
            .Display 'change to .Send if you don't want to chack the E-Mail first
        End With
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing

 

[KainAussie]

If you have a command button, right click on it and select view code.
Paste this between the first and last line in the code window

Dim oApp As Object, oMail As Object, WB As Workbook, fileName As String
Application.ScreenUpdating = False
ActiveSheet.Copy
Set WB = ActiveWorkbook
fileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & fileName
On Error GoTo 0
WB.SaveAs fileName:="C:\" & fileName
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
        With oMail
             .To = "someone@somedomain.com" ' change to suit
             .CC = "change to suit"
             .BCC = "" ' change to suit
             .Subject = "Look at my workbook!" ' change to suit
             .Body = "" 'Insert body text if required
             .Attachments.Add WB.FullName
            .Display 'change to .Send if you don't want to chack the E-Mail first
        End With
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing

im getting a error saying

the following feature cannot be saved in marco free workbooks
vb projects

to save a file with these features click no and choose a macro enabled file type in the file type list the continue saving a marco free workbook click yet

even though the document is a macro enabled document

 

[Michael M]

What version of Excel are you using ?
You may have to change this line

fileName = "Temp.xls"

TO

fileName = "Temp.xlsm"

 

[KainAussie]

What version of Excel are you using ?
You may have to change this line

fileName = "Temp.xls"

TO

fileName = "Temp.xlsm"

no i am still getting a error telling me it cant be saved marco free document

surely there is a simpler code i can use? as this one is not working

 

[Michael M]

AS requested in the previous post....what version are you using ?
Also, are you trying to run this from an xlsx template. ?

AND finally, please keep to this thread.
Private E-Mails are just that...private !

 

[KainAussie]

AS requested in the previous post....what version are you using ?
Also, are you trying to run this from an xlsx template. ?

AND finally, please keep to this thread.
Private E-Mails are just that...private !

office profesional plus 2010
and trying to run it from a .xlsm

 

[Michael M]

Sorry, it works fine for me....and no, I don't have another computer.....they are all govt workstations !

 

[KainAussie]

Sorry, it works fine for me....and no, I don't have another computer.....they are all govt workstations !

well its not working for me...
can u send me ur excel file if i pm u my email.
with it working in it because i cant get it to work

 

[Michael M]

Yep, can if you like ....but I only have 2007 / 03