Mail Exceldata sample file

1 Access to Outlook

1. Exceldata in emailbody
1.1 File
1.2 Sheet values
1.3 Sheet formulae
1.4 Range values
1.5 Range formulae

2 Exceldata in attachment

2.1 SendMail
2.1.1 File, 1 recipient
2.1.2 File, more
2.1.3 File, address in sheet
2.1.4 File integral
2.1.5 Sheet values
2.1.6 File no VBA
2.1.7 Integral Range
2.1.8 Range values

2.2 Routingslip
2.2.1 File, 1 recipient
2.2.2 File more recipients
2.2.3 File address in sheet
2.2.4 File no VBA
2.2.5 File complete
2.2.6 Sheet no VBA
2.2.7 Integral Range

2.3 Outlook attachment
2.3.1 File
2.3.2 Worksheet
2.3.3 Sheet values
2.3.4 File no VBA
2.3 5 Several sheets
2.3.6 Integral Range
2.3.7 Range values

Mail Exceldata (workbook, worksheet, range)

You can mail Exceldata using several methods:
- sendmail
- routingslip
- Outlook
You can mail the data in the mail or as attachment.
Your choice is dependent of the version of Excel you are working with: the method 'routingslip' isn't available anymore since Excel 2007; Excel 2000 en 2003 contain that method.

In many cases the simple methode 'Sendmail' suffices.
The routingslip offers unique options (sequentially forwarding, monitoring the forwarding, automatically returning changed/amended attachments).
Outlook offers BCC, CC etc.

If you use Outlook you need to know how to refer to Outlook in VBA.
The examples always use the method CreateObject("Outlook.Application")

The examples show how to mail
- an entire workbook
- a worksheet integrally
- only the values in a worksheet
- a certain Range in a worksheet
- only the values in a certain range

Access to Outlook

VBA has 3 methods to Access Outlook from 'outside'; in this case from Excel

I. Outlook is not loaded

the method CreateObject

with CreateObject("Outlook.Application")
x=.GetNamespace("MAPI").GetDefaultFolder(6).Items.count
End With

Restriction:
the method 'CreateObject' doesn't recognize Outlook typenames, only Outlookconstants.
For instance: the typename of folder 'PostIN' in VBA is olFolderInbox; the Outlookcontant of this folder 6.

x=CreateObject("Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items.count
Returns an error, while
x=CreateObject("Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6).Items.count
Doesn't.

II. Outlook has been loaded

the method Getobject

With Getobject(,"Outlook.Application")
x=.GetNamespace("MAPI").GetDefaultFolder(6).Items.Count
End With

The method 'GetObject' has the same restrictions regarding typenames as the method 'CreateObject'.

III. Load the Outlook-VBA-library

the methode 'references': independent of whether Outlook has been loaded or not.
manually: VBEditor/ MenuBar / Extra / References / Microsoft Outlook 11.0 Object Library /check

sub referentie()
ThisWorkbook.VBProject.References.AddFromFile "msoutl9.olb"' Outlook 2000
ThisWorkbook.VBProject.References.AddFromFile "msoutl10.olb"' Outlook 2003
ThisWorkbook.VBProject.References.AddFromFile "msoutl11.olb"' Outlook 2007
End sub

If the library is loaded you can refer to Outlook as an object in the code

with Outlook
x=.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items.Count
end with

In this method you can use both Outlook typenames and Outlookconstants.

The examples in this page use the Method 'CreateObject'.

Insert Exceldata into the email body

If you want the Exceldata in the email body you have to use Outlook.
You will need HTML code to accomplish this.
So you can convert the complete workbook, a worksheet or any range into an HTML table.
You can also convert all formatting into HTML, but here we stick to the values to keep it simple.

The complete workbook in the emailbody

Sub volledig_werkboek_in_email()
c01 ="<table border=1 bgcolor=#FFFFF0#>"

For Each sh In Sheets
sn = sh.UsedRange
For j = 1 To UBound(sn)
c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"
Next
c01 = c01 & "</table><P></P><P></P>"
Next

With CreateObject("Outlook.Application").CreateItem(0)
.To = "snb@forums.eu"
.Subject = "actual workbook"
.HTMLBody = c01
.Send
End With
End Sub

The values in 1 worksheet in the emailbody

Sub werkblad_waarden_in_email()
c01 = "<table border=1 bgcolor=#FFFFF0#>"

sn = Sheets("Sheet1").UsedRange
For j = 1 To UBound(sn)
c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"
Next
c01 = c01 & "</table><P></P><P></P>"

With CreateObject("outlook.application").CreateItem(0)
.To = "snb@forums.eu"
.Subject = "actual worksheetvalues"
.HTMLBody = c01
.Send
End With
End Sub

The formulae in 1 worksheet in the emailbody

Sub werkblad_formules_in_email()
c01 = "<table border=1 bgcolor=#FFFFF0#>"

sn = Sheets("Sheet1").UsedRange.Formula
For j = 1 To UBound(sn)
c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"
Next
c01 = c01 & "</table><P></P><P></P>"

With CreateObject("Outlook.Application").CreateItem(0)
.To = "snb@forums.eu"
.Subject = "worksheetformulae"
.HTMLBody = c01
.Send
End With
End Sub

Values in a Range in the emailbody

Sub range_waarden_in_email()
c01 = "<table border=1 bgcolor=#FFFFF0#>"

sn = Sheets("Sheet1").Range("A1:K100")
For j = 1 To UBound(sn)
c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"
Next
c01 = c01 & "</table><P></P><P></P>"

With CreateObject("Outlook.Application").CreateItem(0)
.To = "snb@forums.eu"
.Subject = "Range values"
.HTMLBody = c01
.Send
End With
End Sub

The formulae in a Range in the emailbody

Sub range_formules_in_email()
c0 = "<table border=1 bgcolor=#FFFFF0#>"

sn = Sheets("Sheet1").Range("A1:K100").Formula
For j = 1 To UBound(sn)
c01 = c01 & "<tr><td>" & Join(Application.Index(sn, j), "</td><td>") & "</td></tr>"
Next
c01 = c01 & "</table><P></P><P></P>"

With CreateObject("Outlook.Application").CreateItem(0)
.To = "snb@forums.eu"
.Subject = "Range formulae"
.HTMLBody = c01
.Send
End With
End Sub

Exceldata (workbook, worksheet, range) as attachment

- method 'Sendmail'
- method Routingslip
- using Outlook

SendMail

Workbook to 1 recipient

Sub mail_werkboek_met_sendmail()
ActiveWorkbook.SendMail "snb@forums.nl", "subject"
End Sub

Workbook to several recipients

Sub mail_werkboek_met_sendmail_addressen()
ActiveWorkbook.SendMail Array("snb@forums.eu", "snb@forums.de"), "subject"
End Sub

Workbook to several addresses in a worksheet

- You can add Emailaddresses in a worksheet directly to the argument 'recipients' of the method 'Sendmail'.
Addresses in a column (A1:A10), row (A1:K1) or range (A1:K10) will be converted automatically into a 1-dimensional array.

Sub mail_werkboek_met_sendmail_addressen()
With ActiveWorkbook
.SendMail .sheets(1).Range("A1:G10"), "subject"
End With
End Sub

Workbook integrally

Sub mail_werkblad_integraal_met_sendmail()
With ActiveWorkbook
.Sheets(1).Copy

with ActiveWorkbook
.SendMail "snb@forums.nl", "subject"
.Close False
End With
End With
End Sub

Values in a worksheet

Sub mail_werkblad_waarden_met_sendmail()
With ActiveWorkbook
With .Sheets.Add
.Cells = ActiveWorkbook.Sheets(1).Cells.Value
.Copy

with ActiveWorkbook
.SendMail "snb@forums.nl", "subject"
.Close False
End With

.Delete
End With
End With
End Sub

Workbook without VBA

Sub mail_werkblad_waarden_met_sendmail()
With ActiveWorkbook
With .Sheets.Add
ActiveWorkbook.Sheets(1).Cells.Copy .Cells(1)
.Copy

with ActiveWorkbook
.SendMail "snb@forums.nl", "subject"
.Close False
End With

.Delete
End With
End With
End Sub

Integral Range

Sub mail_gebied_integraal_met_sendmail()
With ActiveWorkbook
With .Sheets.Add
ActiveWorkbook.Sheets(1).Range("A1:K25").Copy .Cells(1)
.Copy

with ActiveWorkbook
.SendMail "snb@forums.nl", "subject"
.Close False
End With

.Delete
End With
End With
End Sub

Values in a Range

Sub mail_gebied_waarden_met_sendmail()
With ActiveWorkbook
With .Sheets.Add
.range("A1:K25") = ActiveWorkbook.Sheets(1).Range("A1:K25").Value
.Copy

with ActiveWorkbook
.SendMail "snb@forums.nl", "subject"
.Close False
End With

.Delete
End With
End With
End Sub

Routingslip

If you are lucky enough to use Excel 2000 or 2003 you can use a routingslip.
Every officedocument can be sent to 1 or several addresses in your standard email programma - not necessarily Outlook-.
Extra facilities in routingslip:
- the option to send the document to all addressees simultaneously (Delivery: xlAllAtOnce), or to send it sequentially to the next recipient in the list after opening/adapting the document (Delivery: xlOneAfterAnother).
- the option to get a reminder if the document has been forwarded to the next recipient in the list (Trackstatus=True)
- the option to return the document after opening/adapting by the recipient (ReturnWhenDone = True)

The recipient will prompted after opening/amending the document:
- to return the amended document to the sender (ReturnWhenDone)
- to forward the document to the next recipient in the list (delivery: xlOneAfterAnother).
the recipient only has to click to do as proposed.
Unfortunately MS deleted this facility in Office 2007.

Workbook to 1 recipient

Sub mail_werkboek_met_circulatielijst()
With ActiveWorkbook
.HasRoutingSlip = True

With .RoutingSlip
.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = True
.TrackStatus = False
End With
.Route
End With
End Sub

Workbook to several recipients

Sub mail_werkboek_met_circulatielijst()
With ActiveWorkbook
.HasRoutingSlip = True

With .RoutingSlip
.Recipients = Array("snb@forums.eu", "snb@forums.de")
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlOneAfterAnother
.ReturnWhenDone = True
.TrackStatus = False
End With
.Route
End With
End Sub

Workbook to recipients in a worksheet

- You can add email addresses directly to the property 'recipients'.
Addresses in a column (A1:A10), row (A1:K1) or range (A1:K10) will be converted automatically into a 1-dimensional array

Sub mail_werkboek_met_circulatielijst()
With ActiveWorkbook
.HasRoutingSlip = True

With .RoutingSlip
.Recipients = sheets(1).range("A1:K10")
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlOneAfterAnother
.ReturnWhenDone = True
.TrackStatus = False
End With
.Route
End With
End Sub

Workbook without VBA

Sub werkboek_zonder_VBA_met_circulatielijst()
With Workbooks.Add
For Each sh In ThisWorkbook.Sheets
With .Sheets.Add
.Name = "c_" & sh.Name
sh.Cells.Copy .Cells(1)
End With
Next

.HasRoutingSlip = True
With .RoutingSlip
.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False
End With
.Route

.Close False
End With
End Sub

Worksheet integral

Sub mail_werkblad_met_circulatielijst()
With ActiveWorkbook
.sheets(1).copy

With activeworkbook
.HasRoutingSlip = True
With .RoutingSlip
.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False
End With
.Route
End With
End With
End Sub

Worksheet without VBA

Sub mail_werkblad_zonder_VBA_met_circulatielijst()
With ActiveWorkbook
with .Sheets.Add
Activeworkbook.Sheets(1).Cells.Copy .Cells(1)
.Copy
With ActiveWorkbook
.HasRoutingSlip = True
With .RoutingSlip
.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False
End With
.Route
.Close False
End With
.Delete
End With
End With
End Sub

Integral Range

Sub mail_gebied_met_circulatielijst()
With ActiveWorkbook
With .Sheets.Add
Activeworkbook.Sheets(1).Cells(1).CurrentRegion.Copy .Cells(1)
.Copy
With activeworkbook
.HasRoutingSlip = True
With .RoutingSlip
.Recipients = "snb@forums.eu"
.Subject = "subject"
.Message = "this is the message"
.Delivery = xlAllAtOnce
.ReturnWhenDone = False
End With
.Route
.Close False
End With
.Delete
End With
End With
End Sub

Attachment in Outlook

You can add an attachment to every email in Outlook.
That can only be an Excel workbook; you can't add a worksheet, graph or range.
If you want to attach a worksheet, graph or range you first have to save that into a workbook.

In Excel < 2007 you didn't have to specify the extension nor the 'fileformat' explicitly. Since Excel 2007 it is required.
In the example code we assume the atttachment will get the same extension and the same fileformat as the originating one.
So us the example code only in workbooks that have been saved (with path and extension).

The integral workbook

Sub volledig_werkboek_sturen()
With CreateObject("Outlook.Application").createitem(0)
.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add ThisWorkbook.FullName
.Send
End With
End Sub

An integral worksheet

- copy the worksheet
- save the new, automatically created workbook
- add this new workbook as attachment to the email

Sub enkel_werkblad_integraal_sturen()
Application.DisplayAlerts = False

c00 = "E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat

ThisWorkbook.Sheets("Sheet1").Copy

With ActiveWorkbook
.SaveAs c00, c01
.Close False
End With

With CreateObject("Outlook.Application").createitem(0)
.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add c00
.Send
End With
End Sub

Values in a worksheet

- add a temporary worksheet
- put in this sheet the values of the sheet to send
- copy this temporary sheet so it will become a new workbook
- save this new workbook
- delete the temporary sheet
- add the new saved workbook as attachment to the email

Sub enkel_werkblad_alleen_waarden_sturen()
Application.DisplayAlerts = False

c00 = "E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat

With ThisWorkbook.Sheets.Add
.Range(ThisWorkbook.Sheets("Blad1").UsedRange.Address) = ThisWorkbook.Sheets("Blad1").UsedRange.Value
.Copy

With ActiveWorkbook
.SaveAs c00, c01
.Close False
End With

.Delete
End With

With CreateObject("Outlook.Application").createitem(0)
.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add c00
.Send
End With
End Sub

Workbook without VBA code

- add a temporary worksheet
- copy all cells of the sheet to be sent
- copy this temporary sheet so it will become a new workbook
- save this new workbook
- deletee the temporary sheet
- add the new workbook as attachment to the eamail

Sub enkel_werkblad_zonder_VBA_sturen()
Application.DisplayAlerts = False

c00 ="E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat

With ThisWorkbook.Sheets.Add
ThisWorkbook.Sheets("Sheet1").Cells.Copy .Cells(1)
.Copy

With ActiveWorkbook
.SaveAs c00, c01
.Close False
End With

.Delete
End With

With CreateObject("Outlook.Application").createitem(0)
.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add c00
.Send
End With
End Sub

Several worksheets integrally

- save the file as a copy with a separate name
- open that copy file
- store the sheetnames that shouldn't be sent in an array
- delete the sheets that should not be sent
- save the file
- add this file as attachment to the email

Sub verschillende_werkbladen_integraal_sturen()
Application.DisplayAlerts = False

c00 = "E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat
sn = Split("weg1|weg2|weg3", "|")

ThisWorkbook.SaveCopyAs c00

With GetObject(c00)
.Sheets(sn).Delete
.Close True
End With

With CreateObject("Outlook.Application").createitem(0)
.to = "snb@forums.eu"
.Subject = "example"
.attachments.Add c00
.Send
End With
End Sub

An integral Range (including formatting, formulae, etc.)

- add a temporary worksheet
- copy te range to be copied to this temporary sheet
- copy the temporary sheet so it becomes a new workbook
- save this new workbook
- delete the temporary sheet
- add the new file as attachment to the email

Sub Range_integraal()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

c00 = "E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat

With ActiveWorkbook.Sheets.Add
ActiveWorkbook.Sheets("Sheet1").Range("A1:AC152").Copy .Range("A1")
.Copy

With ActiveWorkbook
.SaveAs c00, c01
.Close
End With

.Delete
End With

With CreateObject("Outlook.Application").CreateItem(0)
.To = "snb@forums.eu"
.Subject = "example"
.Attachments.Add c00
.Send
End With
End Sub

Values in a Range

- add a temporary worksheet
- put the values of the range to be copied into the range to be sent
- copy the temporary sheet so it becomes a new workbook
- save the new workbook
- delete the temporary worksheet
- add the new file as attachment to the email

Sub Range_alleen_waarden()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

c00 = "E:\OF\bestandsnaam." & CreateObject("scripting.filesystemobject").getextensionname(ThisWorkbook.Name)
c01 = ThisWorkbook.FileFormat
c02 = "Blad1"
c03 = "A1:AC152"

With ActiveWorkbook.Sheets.Add
.Range(c03) = ActiveWorkbook.Sheets(c02).Range(c03).Value
.Copy

With ActiveWorkbook
.SaveAs c00, c01
.Close
End With

.Delete
End With

With CreateObject("Outlook.Application").createitem(0)
.To = "snb@forums.eu"
.Subject = "example"
.Attachments.Add c00
.Send
End With
End Sub