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]

Actually, the example shows you how. In any case, try...

        With OutMail
            .to = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hi there"
            .attachments.Add ActiveWorkbook.FullName
            .attachments.Add Workbooks("Book2.xlsx").FullName
            'You can add other files also like this
            '.attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With

Notice that if you want to attach a workbook that is closed, you'll need to specify the path and filename like this...

.attachments.Add ("C:\test.txt")

 

[Rasmussen]

Thank you. But this only will attach the empty workbook. The code without an extra add.attachment, does save the workbook with data inputs, but only the activesheet, can this be changed, so it's all sheets attached in 1 workbook?

 

[Domenic]

So you have more than one workbook? And you'd like to combine those workbooks into a single workbook and then attach it to the email? If so, some questions...

1) How many workbooks do you want to attached?

2) What are the names of those workbooks?

3) Do any of the workbooks contain more than one worksheet? If so, do you want to include all worksheets from a workbook into the single workbook?

4) Are these workbooks closed or are they already opened?

 

[Rasmussen]

Sorry, I’m so bad at explaining.
I have this 1 workbook with 2 sheets. There are some calculations and formulas on both sheets, so therefore I would like both sheets to be included when sending the 1 workbook. I have a textfield and a button with the mail macro, to easily write the mail subject number and send to my mail by pushing the button.

 

[Domenic]

In that case, you've already been shown how to attach your workbook.

If you want to attach the active workbook (note workbook must be saved to get the full path and filename)...

.attachments.Add ActiveWorkbook.FullName

If you want to attach an already opened workbook, which is not the active workbook (change the name of the workbook accordingly)...

 .attachments.Add Workbooks("Book2.xlsx").FullName

If you want to attach a closed workbook (change the path and filename accordingly)...

.attchments.add "c:\users\domenic\documents\book2.xlsx"