VBA mailing from Excel with outlook

[Rasmussen]

Hello,

I found this VBA online, but I have some troubles with it.

When mailing worksheet, there is no problems, but the received file is empty, like there is no data in the sheets. Can someone help me with this matter?

Sub Mail()

    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next

    With OutMail
        .To = "mymail@domain.com"
        .CC = ""
        .BCC = ""
        .Subject = Range("J16").Value & " TOLDBEREGNING"
        .body = ""
        .Attachments.Add ActiveWorkbook.FullName
        .send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[Domenic]

Your code seems fine. Note, though, it's the active workbook that is being attached to the email. If the target workbook is not the active workbook, you'll need to explicitly state the name of the workbook you want to attach, for example...

.Attachments.Add Workbooks("sample.xlsx").FullName

 

[Rasmussen]

Hello,

Even with this code replacing activesheet, the data just disappear.

The file I receive on mail is called sheet.xls, but when opening the workbook, it's named sheet (3).xls.

Can this have anything to do with the fact that the workbook is empty?

 

[Domenic]

Is this the complete and exact code that you're using? If not, can you post the complete and exact code?

 

[Rasmussen]

Hello,

Yes, this is the complete exact code that I am using.

 

[Rasmussen]

So I just noticed that it doesn't attach a file, if the workbook hasn't been saved on my PC.

Do you know a workaround for this?

 

[Domenic]

If you're not saving the workbook because it's only a temporary workbook, then you can simply temporarily save it, mail it, and then delete it. Have a look at the following link...

Mail one sheet

Also, here are other links dealing with the same sort of thing...

Mail from Excel with Outlook (VBA)

 

[Rasmussen]

Thanks for your input.

Do you know if it's possible to attach more than just the ActiveWorkbook? Like 2 sheets instead?

 

[Domenic]

Simply repeat .Attachments.Add as many times as you need. For example, to attach two workbooks...

    With OutMail
        .To = "mymail@domain.com"
        .CC = ""
        .BCC = ""
        .Subject = Range("J16").Value & " TOLDBEREGNING"
        .body = ""
        .Attachments.Add ActiveWorkbook.FullName
        .Attachments.Add Workbooks("Book2.xlsx").FullName
        .send
    End With

Alternatively, you can use With/End With to manipulate the Attachments object...

    With OutMail
        .To = "mymail@domain.com"
        .CC = ""
        .BCC = ""
        .Subject = Range("J16").Value & " TOLDBEREGNING"
        .body = ""
        With .Attachments
            .Add ActiveWorkbook.FullName
            .Add Workbooks("Book2.xlsx").FullName
        End With
        .send
    End With

Hope this helps!

 

[Rasmussen]

Hello,

Thanks. What if I use the new Mail one sheet, how do I add more sheet attachments for that?