Email attachment is image, not the file

[noboffinme]

Hi

I want to attach an Excel spreadsheet to an Outlook e-mail & the below code works OK, only I find it attaches an image of the Excel icon instead of the file itself.

Looking around, I've found 2 methods to attach a file, 'AddAttachment' & 'Attachment.Add - which one is correct or is there another method to get the file itself to attach properly??

-----------------------------------------------------------

Sub send_to_outlook()

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Dim wb As Workbook

'code creating the 'ORDER' workbook goes here

Worksheets("ORDER").Copy
Set destwb = ActiveWorkbook
destwb.SaveAs "C:\ORDER.xlsx"
destwb.Close

'send the new worksheet to e-mail

On Error Resume Next

With OutMail

.Display
.To = email@gmail.com
.CC = ""
.BCC = ""
.Subject = "Subject line"
.Body = "Hello World!"
'.Attachments.Add ActiveWorkbook.FullName
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\ORDER.xlsx")
.AddAttachment ("C:\ORDER.xlsx")
'.Send

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

 

[thingssocomplex]

This link will tell you all you need and you can even download examples

http://www.rondebruin.nl/sendmail.htm

 

[noboffinme]

Thanks but that's where I got this example.

It works but attaches an image & you can't click on the Excel icon & open the file.

Ideally, I'd like to see a working example & the code that supports it in Excel 2007.

 

[noboffinme]

A bit more info, if I run the examples as they appear in Ron Debruins worksheets, I also get the file attached as an image.

Can anyone try to run one to see why I'm getting this result when the attachment should appear as a file? Thanks

 

[thingssocomplex]

In references do you have Microsoft Outlook Object Library ticked?

 

[noboffinme]

Yes, vers 12 Outlook Library is checked.

With the below declarations, I'm getting the 'Object variable or With block variable not set' error from the 'Set' line;

-----------------------------------
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set OutMail = OutApp.CreateItem(olMailItem)
------------------------------------

Do I need to assign a value to 'olMailItem' to overcome this??

 

[thingssocomplex]

Are you wanting to send a workbook or just a sheet if you want to just attach a sheet from an open workbook to to a new workbook and attach to email the below code should work? If you want something different let me know

Private Sub CommandButton1_Click()

ThisWorkbook.Sheets(1).Copy

With ActiveWorkbook

.SendMail Recipients:="dump@ozgrid.com", Subject:="Try Me" & Format(Date, "dd/mm/yy")
.Close SaveChanges:=False

End With

End Sub

 

[noboffinme]

Thanks thingssocomplex !!

I removed the declarations & set statements & added your code & it worked well.

If I wanted to display the mail before I send it - is there such a thing like 'DisplayMail' ??